Writing between criteria in a form

[Suzied]

Good Day;

I have written a query which uses the between criteria to select data within a date range.



I would like users to access query from a form, with the option to specify the date range which is in the criteria.

[Gerry]

I'm with you so far - what is your hold-up? What are you trying to do that you're not able to accomplish?

[Suzied]

I don't know how to get the range which is in the criteria on the form.

[Gerry]

Okay, if I understand correctly, your problem is that you don't know how to take the entry from the form and use it for criteria in the query. This isn't too hard, actually. I'll skip some of the details - if you need more, let me know.

Put a button on your form to run the query (or to update the query). Also, add two date fields (in my example, dtStart and dtEnd). I'm assuming you've set up the query already, and tested it with hard-coded dates as criteria, to make sure it works properly. Assuming the form is frmLookup, you'd just replace the hard-coded dates with the form field names, so you'd have something like this in your criteria for the appropriate date field in your query:

>= [frmLookup]![dtStart] AND <= [frmLookup]![dtEnd]

Now, when they click the "search" button (or whatever you label it) to call the query results, the query will base the results on the entries in these fields.

There are other ways, but this should do what you want.

[Suzied]

Hi Gerry;

This works, but it asking me for the Parameter values before the form is even open. Then it ask me for the value again when the form is and the command button is submitted.

[pbaldy]

I would use Between (though the other way works), and the correct syntax includes "Forms!" before the form name:

Between Forms!FormName.ControlName And Forms!FormName.OtherControlName

The form must be open when the query runs. Typically we open the criteria form, let the user enter the dates, and then open the form or report that uses the query from that form. You can open the criteria form in dialog mode from the open event of the report as well, but it's probably less common.

[Suzied]

Now this worked. Thanks

[pbaldy]

Happy to help.