IIF Returning #error Message

[Tlo]

I'm new to Access and trying to get an IIF function to work in a query. I understand the concept as I've used them for years in Excel, but can't seem to get the syntax right because I keep getting a #error message in the field I'm trying to create. I need to evaluate what service was done on a vehicle and add the appropriate amount of time for Next Service Due field (field with the iif function). I know how to use DateAdd, so I've reduced the formula to this for testing purposes, and am still getting a #error message.



Next Service Due: IIF([Service Category]= "650 Mile Service","OK","NOT OK")

If the field Service Category is empty, NOT OK is returned. If it is populated with anything OK is returned.

What am I missing?

Thanks in advance for the help!

[JoeM]

I do not get any error messages. I get OK or NOT OK, even when Service Category is an empty field.

What is the value of Service Category that causes you an error?
What is the exact error message you are seeing?

[Tlo]

Actually I just figured it out - Service Category field is a lookup field from another table, so Service Category was a number not a text When I did this it worked:

Next Service Due: IIf([Service Category]=1,"No Further Service Required",IIf([Service Category]=2,DateAdd("m",6,[Service Date]),IIf([Service Category]=3,DateAdd("m",12,[Service Date]),IIf([Service Category]=4,DateAdd("m",24,[Service Date]),IIf([Service Category]=5,DateAdd("m",42,[Service Date]),IIf([Service Category]=6,DateAdd("m",72,[Service Date])))))))

Thanks for quick reply though! I had been wrestling that one for a few hours now.

[CJ_London]

I bet you have set your Service Category field to be a lookup field in your table design. If so, think how much time you would have saved if you hadn't done that.

Another tip to save time in the long run is to not have spaces in your field names - a) it is an extra 3+ characters to type each time, b) you forget and get errors and spend time trying to resolve, c) even with the square brackets, access will for no rational reason find the word 'date' (or other reserved words) upsetting and generate an ambiguous error when you least expect it

Also, nested iifs can become difficult to manage so recommend you add the 'choose' and 'switch' functions to your repertoire

your code would then be simplified to

Next Service Due: Choose([Service Category],"No Further Service Required",DateAdd("m",6,[Service Date]),DateAdd("m",12,[Service Date]), DateAdd("m",24,[Service Date]), DateAdd("m",42,[Service Date]),DateAdd("m",72,[Service Date]))

[Tlo]

Wonderful - thanks for the advice Ajax.

[ssanfu]

Why lookup fields (in tables) are evil: http://access.mvps.org/access/lookupfields.htm

[Tlo]

Thanks for the link. I'm taking a course in Access beginning next week and am sure I will get a much better foundation for this. But how else would I set up a field in that table to only allow a certain list of values to be chosen?

[Tlo]

Would it mean storing the results (and building the entry form) based off a query? Again I know this is a basic fundamental question, so forgive me for my ignorance. And thanks in advance for the help.

[CJ_London]

But how else would I set up a field in that table to only allow a certain list of values to be chosen?

you do it in the form, not the table - the control in your form would have the same properties (rowsource, bound column, columncount and column widths) as you have currently set in the table

Point is - when developing, you know you are working with a number and not text. and labels in forms can be anything you want