Syntax error (comma) in query expression

[PATRICKPBME]

"Syntax error (comma) in query expression"



I get the noted error message and can't figure out why. Here is what I wrote:

1st FMV Review: (IIF([FMV Expires]>[End Date],[FMV Expires]-90,[End Date]-90)) This part worked fine, the I attempted to nest as follows:

1st FMV Review: (IIf([FMV Expires]>[End Date],[FMV Expires]-90,[End Date]-90),IFF([FMV Effective]="1-1-1","NA","NA")))

Adding that new stuff causes the error message. Can anyone show me what I am missing?

[ranman256]

IFF is not a function.

and IIF has IIF( statment, TRUE part, false part)

[PATRICKPBME]

Okay, thanks. I corrected the IFF to IIF and ended up with the following:


1st FMV Review: (IIf([FMV Expires]>[End Date],[FMV Expires]-90,[End Date]-90), and IIF([FMV Effective]="1-1-1","NA","NA")))

and now the error message says:

"The expression you entered contains an invalid syntax. You may have entered an operand without an operator."

Still at a loss as to what it is telling me I have to do...

[isladogs]

It is possible to have nested IIF statements but not written like that
However, the second iif is pointless as the answer if always NA.

Perhaps you should explain what your possible outputs could be

[PATRICKPBME]

I will try... I am trying to get the query to give me either 1 of 2 dates or a response that says NA.

There are basically 3 fields I want the query to look at with and insert 1 of the answers.

Two get 1 of 2 date, I tell it if FMV Expires > End Date" the "true" result is the FMV Expires - 90, the "false" result is End Date - 90. With just that it works fine.

THEN I want the query to look at another field (FMV Effective)and if it finds "1-1-1" to enter NA because there is no FMV date applying to the records with that date as an effective date. The last part is what is making Access flash the error message.

[isladogs]

OK - so we do the 2nd criterion (part in RED) first as that is the over-riding condition
If that's false, we run the other IIf

1st FMV Review:  IIF([FMV Effective]="1-1-1","NA",IIf([FMV Expires]>[End Date],[FMV Expires]-90,[End Date]-90))

Hope that makes sense and does what you want

[PATRICKPBME]

Makes perfect sense and gets me out of the error message. Unfortunately, when I run the query I get #Error in the spreadsheet/report where I wanted NA. I thought this was maybe because the field was a date/time field. Changed it to text but it still would not insert the NA I need. Still, you have helped me make progress and I thank you for that! I 'll try to figure out what the formatting problem is.

[isladogs]

Without seeing your data i can't advise further. However this sounds odd:

if it finds "1-1-1" to enter NA because there is no FMV date applying to the records with that date as an effective date

Is that 1st January in the Year 1? If so, that is a problem as Access can't handle dates before the year 100.
The reason for that is confusion between e.g. 99 and 1999 (when in 2 digit format)