How do I Query for a date range using a form in Microsoft Access?

Question

I've got 2 unbound date fields in Micrsoft Access form where I wanted to type in the dates and it fills it in back in the query design view. I tried using BETWEEN & AND operators but didn't seem to work. Can someone help?

Answer 1

Access handles dates differently than other strings in the SQL statement, so maybe you have a syntax error.

If you get a date from a form and store it in the variable strDate your statement would resemble this:

Select * FROM tblTransactions WHERE # " & strDate & " # BETWEEN [StartDate] AND [EndDate]

The # signs tell Access this is a date to be compared

Hope that helps

Answer 2

I'm not following your question. If you have 2 unbound date fields in a MS Access form you can't have the information saved back to a query, simply because they are unbound fields ( no field source on a table).

You must create a query with a date field with the following criteria: Between [Start Date] and {End Date]. Each time you run the query (or any form based on it) you will be asked to type the beginning and the end dates, setting a range of time to display.

I'm not sure if I answered your problem, but I tried.

Makiavel

Answer 3

You can certainly use unbound fields in a form to collect criteria to be used in a query. What you didn't say is how you are running the query -- are you using a button on the form to run your query?

At any rate, in order for the query to be able to utilize the user's input into the unbound fields, the form must remain open while the query is running. Then in your query criteria, you must specify the form's name along with the field names.

If you are using Between/And, your criteria would then look something like this:

Between [Forms]![frmQueryForm]! [txtDate1] And [Forms]! [frmQueryForm]![txtDate2]

(There are no spaces between the exclamation marks and the brackets, but I had to put a couple in, otherwise Yahoo abreviates the expression.)

Of course, fill in the names with your form name and field names.

That should do it!

Answer 4

You have a table with all the information. Use a query to select events within a certain date range and certain type. After that I am lost. Why do you need a new table? You save the query and run it whenever you need the information. There is no need to duplicate information into a new table.