IIF Statements

[bdehning]

I have a database where all items are considered "Pending" until some entry on a form is completed. A person can check a box "Waive", they can check a box "Cancel" or they can enter a date for a field "Rescheduled". If a field "Date Written Report Sent is filled out, then it is considered "Completed". The person can either check "Cancel" or Waive" and that could end the item. Entering a date in "Rescheduled" field leaves it open still for all possibilities. Filling out the date field "Date Written Report Sent" ends the item as well.

What and how would be the best approach to be able to show the current status in a field on a report

How would a person write a multiple IIF Statement for this to be able to display the current status

 

[Graham Mandeno]

Put a calculated field in a query based on your table(s) and use the Switch
function. Use True as the last conditional expression to effectively give
you an Else value:

Status: Switch( [Waive], "Waived", [Cancelled], "Cancelled",
Not IsNull([Date Report Sent]), "Completed", True, "Pending")

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a database where all items are considered "Pending" until some

entry on a form is completed. A person can check a box "Waive", they can
check a box "Cancel" or they can enter a date for a field "Rescheduled". If
a field "Date Written Report Sent is filled out, then it is considered
"Completed". The person can either check "Cancel" or Waive" and that could
end the item. Entering a date in "Rescheduled" field leaves it open still
for all possibilities. Filling out the date field "Date Written Report
Sent" ends the item as well.

 

[bdehning]

Graham, it works good except for a person could "Reschedule" and then eventually "Complete".

If both fields are filled in it shows "Rescheduled" and not "Complete".

What change do I need for the switch to pick this up properly and show "Complete". Picking "Rescheduled" leaves the option to pick "Cancel", "Waive" or to "complete" the item by filling in "Date Written Report".

Thanks for the help so far. I know we are close.

----- bdehning wrote: -----

I have a database where all items are considered "Pending" until some entry on a form is completed. A person can check a box "Waive", they can check a box "Cancel" or they can enter a date for a field "Rescheduled". If a field "Date Written Report Sent is filled out, then it is considered "Completed". The person can either check "Cancel" or Waive" and that could end the item. Entering a date in "Rescheduled" field leaves it open still for all possibilities. Filling out the date field "Date Written Report Sent" ends the item as well.

What and how would be the best approach to be able to show the current status in a field on a report?

How would a person write a multiple IIF Statement for this to be able to display the current status?

 

[bdehning]

Graham, I think I made it work by adding [Rescheduled],"Rescheduled" to the switch.

Since you are so helpful, maybe you can tell me why I am having trouble with my Form and subforms and the tab key. First time through it tabs through all fields correctly. Second time through however it skips the first subform and goes directly to the 2nd subform.

The way it works it that I have a form "Account Information" which has a subform "Location" and the "Location" subform has a subform "Service Calls". It works such that for each Account there can be multiple Locations with each location having multiple Service calls. I use a commenad button to Add New Location and Add New Account.

I have posted and received no help on my tab issues. Have any ideas? If it goes correctly the first time, what could cause it to not the second time

----- bdehning wrote: ----

I have a database where all items are considered "Pending" until some entry on a form is completed. A person can check a box "Waive", they can check a box "Cancel" or they can enter a date for a field "Rescheduled". If a field "Date Written Report Sent is filled out, then it is considered "Completed". The person can either check "Cancel" or Waive" and that could end the item. Entering a date in "Rescheduled" field leaves it open still for all possibilities. Filling out the date field "Date Written Report Sent" ends the item as well.

What and how would be the best approach to be able to show the current status in a field on a report

How would a person write a multiple IIF Statement for this to be able to display the current status

 

[Graham Mandeno]

I should have explained that the way Switch works is that you have pairs of
conditions and results:
Switch( Condition1, Result1, Condition2, Result2,...etc)

Each condition is tested *in order* and the function returns the result
after the first condition which is True. (This is why specifying the last
condition as True works like an Else clause.) Anyway, this means the
*order* of the pairs is very important, because you might have two
conditions which are true (for example, Rescheduled and Complete). In this
case, you put the overriding condition (Complete) first in the list.

I don't know what the problem could be with your tabs, but I suggest you
carefully check (1) the tab order of each form section, (2) the tabstop
property of all the affected controls, (3) any event code which might be
executing a SetFocus to another control.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, I think I made it work by adding [Rescheduled],"Rescheduled" to the switch.

Since you are so helpful, maybe you can tell me why I am having trouble

with my Form and subforms and the tab key. First time through it tabs
through all fields correctly. Second time through however it skips the
first subform and goes directly to the 2nd subform.

The way it works it that I have a form "Account Information" which has a

subform "Location" and the "Location" subform has a subform "Service Calls".
It works such that for each Account there can be multiple Locations with
each location having multiple Service calls. I use a commenad button to Add
New Location and Add New Account.

I have posted and received no help on my tab issues. Have any ideas?

If it goes correctly the first time, what could cause it to not the second
time?

----- bdehning wrote: -----

I have a database where all items are considered "Pending" until some

entry on a form is completed. A person can check a box "Waive", they can
check a box "Cancel" or they can enter a date for a field "Rescheduled". If
a field "Date Written Report Sent is filled out, then it is considered
"Completed". The person can either check "Cancel" or Waive" and that could
end the item. Entering a date in "Rescheduled" field leaves it open still
for all possibilities. Filling out the date field "Date Written Report
Sent" ends the item as well.

What and how would be the best approach to be able to show the

current status in a field on a report?