Where to put Select Case for unbound field

[Christine Pearc]

I have a subform, set to Continuous Forms, that presents information from a query in columns:

Job Number Current Status

There is a hidden field from the qry/Table called “Status”. I would like the output for Current Status (an unbound field) to vary depending on the value of Status, using a Select Case statement. I've decided on Select Case to get the value for Current Status because the variety of options seems far too complicated to put into an IIF expression.

The Select Case statement looks something like this:

Select Case Status
Case “Assigned”
Me.ExpStatus = "Assigned on " & [DispositionDate]
Case "Complete"
Me.ExpStatus = "Completed on " & [CompleteDate]
Case "Verified"
Me.ExpStatus = "Verified on " & [VerifiedDate]
Etc........
End Select

I can’t seem to find the correct place to put the Select Case statement. Could someone advise?

Thank you,
Christine

 

[WayneRyan]

Christine,

Unbound controls on Continuous forms are trouble.

IIf is better (indented for readability):

ExpStatus: IIf(Status = "Assigned”, "Assigned on " & [DispositionDate],
               IIf(Status = "Complete", "Completed on " & [CompleteDate],
                   IIf(Status = "Verified", "Verified on " & [VerifiedDate], "Invalid Status")))

Just run them all together in the Zoom Box.

Wayne

 

[Christine Pearc]

Where to put Select Case for unbound field - thank you

Thanks, Wayne. I thought using so many multiple IIFs would be way too confusing, but your indented the example makes it much easier easier to figure out what's going on. Cheers again.

Christine

 

[WayneRyan]

Christine,

Glad to help. Unless the IIfs are indented like that, I haven't got a chance
of understanding them. It is the best way though.

See ya,
Wayne

 

[Irmgard Wesselb]

Unbound field in continuous formfield to evaluate GrandTotal

Christine,

Unbound controls on Continuous forms are trouble.

IIf is better (indented for readability):

ExpStatus: IIf(Status = "Assigned”, "Assigned on " & [DispositionDate],
               IIf(Status = "Complete", "Completed on " & [CompleteDate],
                   IIf(Status = "Verified", "Verified on " & [VerifiedDate], "Invalid Status")))

Just run them all together in the Zoom Box.

Wayne

-----

The above is close to what I am looking for, but I can't get it to work.
On my form I have a field 'GrandTotal' which shows the total amount of all orders for each customer. In an unbound textbox 'txtCategory' I want to show the category according to what the amount is in the GrandTotal field, i.e.: <50 = Category 3
<100 = Category 2
>=100 = Category 1

Can somebody please help me how to set up the IIf statement?
I assume that it would go into the Control Source property of the txtCategory textbox?

Any help would be greatly appreciated.
Regards....Irma

 

[WayneRyan]

Irmgard,

Hope this helps:

GrandTotal: IIf ([txtCategory] < 50 "Category 3"
                IIf([txtCategory] < 100, "Category 2", "Category 1"))

If worse-came-to-worse you could change [txtCategory] to CLng([txtCategory])


Wayne

 

[Irmgard Wesselb]

Thank you for quick reply

Hallo Wayne,

Thanks a million for the prompt and detailed reply. I will give it a try first thing tomorrow as it is too late here (Australia) and I am brain-dead at the moment.

Regards....Irmgard

 

[Irmgard Wesselb]

Problem solved with 'Switch' function

Hallo again,

I thought it may be of interest to some members to see how I (with lots of help) got Access to assign categories to customers according to the total amount spent.

In a query I put the following code:

Category: Switch([TotalAmount]>=100,1,[TotalAmount]<100 And [TotalAmount]>=50,2,[TotalAmount]<=50,3)

This works beautifully, i.e.
TotalAmount >100 = Category 1
TotalAmount between 50 and 100: = Category 2
TotalAmount <50 = Category 3

Regards...Irma