MS Access: Nested Iif statements

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
I have a query that includes the following field:

Status: IIf([Estimated] Is Null,"Awaiting Estimate",IIf([Workbook] Is Null,"Awaiting Workbook",IIf([Authorised] Is Null,"Awaiting Authorisation",IIf([InWork] Is Not Null And [QCPass] Is Null,"Work In Progress",IIf([V5Received] Is Null,"Awaiting V5",IIf([InStorage] Is Not Null,"Storage",IIf([QCPass] Is Not Null,"QC Passed","")))))))

All of the fields shown in the IIF statements are dates.

This is for a "Status" report, showing the status of vehicles in a repair process. The query is otherwise (currently) based on a single table. The report is grouped by this Status field, and only shows the current status, so the IIFs are arranged in the order of the work procedure.
However, I may want to show all "Awaiting" statuses on another report (i.e. a vehicle at the beginning of the process is effectively "Awaiting Estimate", "Awaiting Workbook", "Awaiting Authorisation" and "Awaiting V5".

I could write another query that lists all of the outstanding "Awaiting"s depending on where the vehicle has got to, but I figured I should be able to use this query, and simply list the vehicle in multiple groups.
How can I achieve this?

Thanks!

Gram
 

OBP

Joined
Mar 8, 2005
Messages
19,896
It really would be better to have a dedicated query to do this, they do not take up much disk space and it can be much more flexible. You should use the "Totals" to do what you want.
You can then use the new standard query for crosstab summaries if you so desire.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top