nested iif statements in a query

  • Thread starter Thread starter Hotrodick
  • Start date Start date
H

Hotrodick

How many are allowed, it have currently 12 and seems to error out if I try
another?
My problem is I have a control on a form and many other forms, "WIP Status",
this control is set according to a date entered in other controls; WIP
Status: =iif(date Recvd] > 1 , "New Item", iif([Date tech pickup] > 1,
"Test", iif(date AWP]>1, "AWP", iif([date issue]>[date AWP]," Ready for
Retest", this goes now for many mores date controlls, 12 exact))))))))).
I was told to try nesting them in a macro but I am not sure how to get
started. Also I have several queries that have a WIP Status on other forms
and reports that if I change the date selection I have to go to every query
and make the change.
 
How many are allowed, it have currently 12 and seems to error out if I try
another?
My problem is I have a control on a form and many other forms, "WIP Status",
this control is set according to a date entered in other controls; WIP
Status: =iif(date Recvd] > 1 , "New Item", iif([Date tech pickup] > 1,
"Test", iif(date AWP]>1, "AWP", iif([date issue]>[date AWP]," Ready for
Retest", this goes now for many mores date controlls, 12 exact))))))))).
I was told to try nesting them in a macro but I am not sure how to get
started. Also I have several queries that have a WIP Status on other forms
and reports that if I change the date selection I have to go to every query
and make the change.
Click to expand...

I would REALLY suggest doing this with a simple table, rather than a complex
nested IIF or (slightly better) Switch() function call... and I'd even more
strongly suggest that you normalize your table design! If one (object,
whatever your form means) can have up to twelve different kinds of dates, then
a better design than twelve date fields would be a one-to-many relationship to
a SignificantDates table, with fields for EventType (e.g. "Received", "Tech
pickup", "AWP", etc. etc.) and a date/time field for that event.
 
Do you need to have that Status field in your query?
Are you printing or viewing that data through a form or report?
Your form or report can have an unbound text box
Assign the value of the box to a user defined function in that form or
report
Put all of your ifs in that function with if then else elseif and endif.
It will be much easier to code and debug.

your status field will be calculated whenever you open that form or
report.
 
Back
Top