IIF statement returning #Error in select rows, data type mismatch in criteria expression

[suwilson008]

Hello,

I'm trying to create an expression in a new field using the IIF function, but I haven't been successful. I'm returning a #Error in some rows, and otherwise unexpected results in other rows, and I can't see what's causing the issue. Any guidance would be appreciated!

I'm trying to do the following:
Create an expression within a query titled IIR Population that populates "IIR" if the expression is true, or "N/A" if false

IIR Population: IIf(([Retail / Non-Retail]="Retail") And ([Store Type]="NEWFU") And ([Life in Months]>"84") And ([Cat Minor Category]<>"PARKING") And ([Cat Major Category]<>"INTANGIBLES"),"IIR","N/A")

In the attachment, there are two tabs...one for the Snapshot table, and the other for the results of the query

The [Retail / Non-Retail] column on the Query Results tab is a field in the database that's also built on an IIF expression in the same query I'm trying to add this new expression to...not sure if this has anything to do with my issue?

Retail / Non-Retail: IIf([Snapshot].[RC]="999999","Non-Retail","Retail")

If I try to do anything with the newly created (but totally incorrect) IIR Population field in Access, I get the following error message:


Data type mismatch in criteria expression

Please help!!

Thank you,
Susie

[June7]

Post the query you attempt. Does it join the two tables? What data type is RC field? If it is number the don't use quote marks around 999999.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be Retail_NonRetail.

[suwilson008]

Thank you! Your answer was on the right track for my issue. It was actually my [Life in Months] field that was set as a number. I removed the quotes and it now works as expected...woo hoo!!