Can I use an IF statement to create a value for a field in a Query?

[David Ball]

Hi Forum,
I want to create a report to show the Status of items on a To-Do List that is in an Access table.
I will base the Report on a Query. The Query has the fields dtReported, dtStarted, dtCompleted, and dtVerified (among others) that show what date each of these date fields was "checked off".
Each item in the To-Do List will have a date automatically assigned for dtReported when it is entered into the database. It will then have a date added to dtStarted when work starts on it, a date for dtCompleted when it is completed and one for dtVerified when a manager verifies that it has been done.
On the report I want to show a Current Status, only, for each item. I do not want to show Reported, Started, Completed and Verified dates, just the "highest level" that has a date in it.
The Current Status will either be Reported, In Progress (if a date appears in the dtStarted field), Completed (if there is a date in the dtCompleted field), or Verified (if there is a date in the Verified field).
Can I use an “IF” statement to incorporate this logic into my Query?
In words it would be:
If there is a date in [dtVerified] use “Verified” as the value for Current Status. If not, if there is a date in [dtCompleted] use “Completed” for Current Status. If not, if there is a date in [dtStarted] use “In Progress” for Current Status. If there is no date in [dtStarted] use “Reported” as the value for Current Status.
I am not sure how to write the IF statement that would appear at the top of the Current Status field in the Query but I guess it would be something like below:
=IF([dtVerified]IsNotNull,“Verified”,IF([dtCompleted]IsNotNull,”Completed”, IF([dtStarted]IsNotNull,”In Progress”, IF([dtStarted]IsNull,”Reported”,” “))))

Can anybody advise on how to do this?
Thanks very much.
DFB

 

[John Big Booty]

You should be able to create an expression in your Query using;

IIF([dtVerified]IsNotNull,“Verified”,IIF([dtCompleted]IsNotNull,”Completed”, IIF([dtStarted]IsNotNull,”In Progress”, IIF([dtStarted]IsNull,”Reported”,” “))))

put this in the top row of an empty column of your query builder grid, also note the double "I" in the IIF.

 

[vbaInet]

John's code does the job but here's an idea I just tested that works (or should work) in this kind of situation without using IIF(). Just thought I'd share:

Nz(String(0, [dtVerified]) + "Verified", Nz(String(0, [dtCompleted]) + "Completed", Nz(String(0, [dtStarted]) + "In Progress", "Reported")))

It might look complicated to the untrained eye