Date Range Query

[need_help12]

Good afternoon.



I'm working on a db that contains contracts data, and I need the ability to pull contracts that are expiring between a chosen "StartDate" and "EndDate".

I've created a form that allows end users to search for contracts expiring between a chosen start and end date, with a command button to run the report.

In the qry for this report, in the Contract STart Date field I have a listed criteria of "<[Forms]![frmContractTracking]![EndDate]" and on the Contract End Date field I have a listed criteria of ">[Forms]![frmContractTracking]![StartDate]"

Currently, this is returning all field in the table, regardless of dates I enter on the Form. I've been staring at this too long, and I'm hoping someone with fresh eyes can assist.

Any help is greatly appreciated.

Thanks!

[pbaldy]

Based on your description, you'd want this in the end date criteria:

Between [Forms]![frmContractTracking]![StartDate] And [Forms]![frmContractTracking]![EndDate]

[need_help12]

immediately fixed. Thanks again for your help, Paul!

[pbaldy]

Happy to help!

[need_help12]

Actually, a quick follow-up, Paul. Everything works perfectly. For the report header, is there a way to show the range of dates the end user input? For example, if the end user inputs on the frm "4/1/12" - "4/30/12" and reuns the report, the report head states "Contracts Expiring Between [StartDate] - [EndDate]?

[pbaldy]

Sure, you just concatenate the form references into a string. A textbox control source of:

="Contracts Expiring Between " & [Forms]![frmContractTracking]![StartDate] & " - " & [Forms]![frmContractTracking]![EndDate]

[need_help12]

I'm going to have to put you on retainer Thanks, Paul!

[pbaldy]

Happy to help. I accept both beer and nice red wines as retainer fees.