Using AND, OR and BETWEEN

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:

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

Using AND, OR and BETWEEN