Using AND, OR and BETWEEN (1 Viewer)

[Chumpalot]

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:

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

 

[spikepl]

USe the Nz funtion to give values way out of range when the date control on the form is blank (null). If not familiar with the function look it up in the documentation.

 

[Chumpalot]

I'm familiar with the Nz function. I'm not too sure however where this will all fit into my existing SQL.

 

[spikepl]

Don't do too much at one time. Make SQL to get recods before some date without any other critaria, then after some date, then those that are between those dates .. then add criteria one at a time. You have the query designer to play in - it really isn't difficult.

 

[namliam]

Custom solutions require custom code, require custom SQL.

When making options like this ineffitably you run into trouble trying to mix and match different options.
Best solution is to custom build your SQL with some VBa....

Dim mySQL as string
mySQL = "SELECT Cases.Case_Type, Cases.Case_Status " & _
        "FROM Cases " & _ 
        "where 1=1 "  'show all records by default

If not IsNull([Forms]![frm_casefilters]![cbo_casetype]) then
' assuming case_type is a text field use the ' as per below
    mySQL = Mysql & "and case_type =  '" & [Forms]![frm_casefilters]![cbo_casetype] & "'"
' If its a date, use #
'    mySQL = Mysql & "and case_type =  #" & [Forms]![frm_casefilters]![cbo_casetype] & "#"
' if it is a number, dont add anything
'    mySQL = Mysql & "and case_type =  " & [Forms]![frm_casefilters]![cbo_casetype] & ""
endif
etc...

 

[Chumpalot]

Thanks. I'll take a look at this.