Can IF or CASE statements be used to alter the criteria of a query?

[Nick H]

I am developing quite a large database in MS Access 2003.
I need to allow the information to be filtered and displayed in a subform.

I have several combo boxes that filter this query. These compare the combo box variable with the corresponding fields in another table. These work fine.

The next step that I would like to do is allow the user to choose to show records if a certain field is blank (DatePaymentRec eived). This is because the user will want to filter the data as normal but may also choose to see the records of any outstanding payments.

I can get the query to filter using the SQL below:

WHERE ((CandidateInformation.Trust Like ([Forms]![MainForm]![cmbTrust])) 
AND 
(Qualification.DueProgrammeStartDate Between ([Forms]![MainForm]![cmbStartDate]) AND ([Forms]![MainForm]![cmbEndDate])) 
AND
 (Qualification.CompletionStatusID Like ([Forms]![MainForm]![cmbStatus]))
AND
Qualification.DatePaymentReceived IS NULL)

Where the "Qualification. DatePaymentRece ived IS NULL" I would like to only filter this if another combo box is set to "yes"
Is this possible? I have tried inserting an IF statement to the query to check IF (cmbOverdue = "yes") THEN ...perform payment filter

Are there any suggestions on how/if this can be done?

Your advice would be greatly appreciated, thanks

[TheSmileyCoder]

I believe this should work. You can find information on the IIF function in the help.

...
AND 
IIF(Forms!MainForm!cmbOverdue ,Qualification.DatePaymentReceived IS NULL,nz(Qualification.DatePaymentReceived,0) =*))

[Nick H]

Thank you. That looks like what I need!

But should the expression part be...

IIF(Forms!MainForm!cmbOverdue = "Yes",

...so it can calculate true/false?

I am also getting a syntax error (missing operator) in query with the "*" at the end.

Thank you for your help

[Nick H]

It's ok, I've sorted it!

It's working great, thank you very much for your help, much appreciated!

[Oralloy]

Efficency wise, you might want to lose the IF.

You can use a clause of the form:

((Forms!MainForm!cmbOverdue <> "Yes") OR (guarded condition))

The trick is to use "OR".

Better still is to simply guard whether you add the condition to your SQL statement. It all depends on what you're doing.

Cheers!