Between Dates query

[niccig]

I have a form where I have a number of combo boxes and text boxes, what a user enters or selects will be the criteria for a query. I place the query criteria in an extra column with this structure

Field: [FinancialYear]=[Forms]![PrintFacultyReports]![cboFinancialYear] Or [Forms]![PrintFacultyReports]![cboFinancialYear] Is Null
Show: unchecked
Criteria: True

This allows the combo box or text box to be included in the query or left empty. I got this code from an old post on this forum (Thanks!)

My problem is that I want to combine the above structure with the Between operator using what is entered in 2 text boxes, txtfromdate and txtuntildate.

I've tried this:
[PayPeriodEnd] = Between Forms![PrintFacultyReports]![txtfromdate] And Forms![PrintFacultyReports]![txtuntildate] Or (Forms![PrintFacultyReports]![txtfromdate] And Forms![PrintFacultyReports]![txtuntildate] Is Null)

I get invalid syntax with the Between operator, and I don't think the last half from "Or...." is correct either.

Unfortunately I only have one date field to use PayPeriodEnd

Does anyone have any suggestions?!?!

 

[Jon K]

You can use:-

[PayPeriodEnd] Between [Forms]![PrintFacultyReports]![txtfromdate] And [Forms]![PrintFacultyReports]![txtuntildate] Or [Forms]![PrintFacultyReports]![txtfromdate] Is Null


When txtfromdate is left blank, every record will be returned. (You can use some code on the form to ensure that nothing can be entered in txtuntildate if txtfromdate is blank.)
.

 

[niccig]

Thanks

Jon, Thanks for your help - I knew I had something wrong but I couldn't figure it out.
Once again someone on this forum has solved my problem. I really have to thank everyone for being generous with their knowledge of Access - this is a great forum.