If someone could please help me with this, I'd really appreciate it. Per my Access query, I'm trying to construct the if statement to pull only specified values when true, and only non-specified values (including nulls) when false. The field in question is called FOP (short for "form of payment"). My If statement for the FOP field critieria looks like this: IIf(Date()-[DueDate]<=5,"CH",[FOP]). If I leave it like this, the false portion of the if statement only gives me all values, except for nulls. However, if I rewrite it to IIf(Date()-[DueDate]<=5,"CH",[FOP]) or is Null, it includes CH and null values if the if statement is true.


I want it to have only CH(which stands for cash) values (no nulls) if the statement is true, and all other values, including nulls, if it is false. This seems like it would be simple, but I can't figure it out without getting error messages. If today's date is within 5 days of the due date, I only want to see cash records (no nulls); if today's date exceeds the due date by 6 days or more, I want to see all records, including nulls.
.
I've tried variations of IIf(Date()-[DueDate]<=5,"CH",[FOP] and Is Null), and I kept getting a error saying "data mismatch type expression". Not sure why i'm getting that. FOP is a text field.


I'd really appreciate any assistance I can get. Thank you,

Sean

Have you tried

You can't put operators such as IS NULL in parameters - only actual matching values.

Try doing without the IIF() function altogether:

WHERE ((Date() - [DueDate] <= 5 AND [FOP] = 'CH') OR (Date() - [DueDate] > 5))

in SQL view. I don't know how that will come out in the grid so do it from the SQL window - just edit the WHERE clause.

The suggestion below doesn't work. I want it to only pull "CH" (no null values) if the statement is true, and if it's false, I want it to pull all values (including ch and null values). THe suggestion below only pulls CH values. I don't think it recognizes the false statement. I've uploaded a spreadsheet with test data; I only included the relevant columns.

Thanks, John. That worked. I really appreciate it.

Whose suggestion? Darnellk's or mine? Mine should work...

Sorry, I meant darnell's suggestion didn't work. I started typing my reply before I saw your initial reply. Thanks again.