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 (DatePaymentReceived). 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:
Expand|Select|Wrap|Line Numbers
- 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.DatePaymentReceived 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