Hi all, first post here so please bear with me
I have built a query which you can see below. It basically looks at various fields in a form (frm_casefilters) and pulls the relevant information from my table.
Everything works fine. I'd like to make one addition though.
I'd like to add the ability to filter by 'Claim_Issue_Date' too. I've added two text boxes (txt_datefrom and txt_dateto) to the form. These are short date formatted and users will be able to enter dates here to filter the results.
A few things I've thought of:
- if the TO and FROM boxes are left blank, return all results
- Using less than or equal to... if a user selects for example 21/12/2014 I'd like the records issues on that date to be included in the results.
- A custom error if the 'txt_dateto' if BEFORE 'txt_datefrom'
Now, I know how to create a query to do just this. However, how and where would I add it to the code below? I have no idea :banghead:
Thank you very much in advance for any help. It is greatly appreciated.
Cheers
Dave
I have built a query which you can see below. It basically looks at various fields in a form (frm_casefilters) and pulls the relevant information from my table.
Everything works fine. I'd like to make one addition though.
I'd like to add the ability to filter by 'Claim_Issue_Date' too. I've added two text boxes (txt_datefrom and txt_dateto) to the form. These are short date formatted and users will be able to enter dates here to filter the results.
A few things I've thought of:
- if the TO and FROM boxes are left blank, return all results
- Using less than or equal to... if a user selects for example 21/12/2014 I'd like the records issues on that date to be included in the results.
- A custom error if the 'txt_dateto' if BEFORE 'txt_datefrom'
Now, I know how to create a query to do just this. However, how and where would I add it to the code below? I have no idea :banghead:
Code:
SELECT Cases.Case_Type, Cases.Case_Status
FROM Cases
WHERE (((Case_Type)=[Forms]![frm_casefilters]![cbo_casetype] Or IsNull([Forms]![frm_casefilters]![cbo_casetype]))
AND ((Case_Status)=[Forms]![frm_casefilters]![cbo_casestatus] Or IsNull([Forms]![frm_casefilters]![cbo_casestatus]))
AND ((Funding)=[Forms]![frm_casefilters]![cbo_legalaid] Or IsNull([Forms]![frm_casefilters]![cbo_legalaid]))
AND ((Litigation_RAG_risk_rating)=[Forms]![frm_casefilters]![ cbo_risk] Or IsNull([Forms]![frm_casefilters]![cbo_risk]))
AND ((CMP)=[Forms]![frm_casefilters]![cbo_cmp] Or IsNull([Forms]![frm_casefilters]![cbo_cmp]))
AND ((lawyer)=[Forms]![frm_casefilters]![cbo_lawyers] Or (IsNull([Forms]![frm_casefilters]![cbo_lawyers]))
));
Thank you very much in advance for any help. It is greatly appreciated.
Cheers
Dave