variable criteria in a query

Hi,

What is the criteria you need to change?

The simpilest way is to put something like:

[Enter Name] in the name field, this will open a promt each time is is run.
However a better way in to control it from a Form.

Regards,

That is a great start. If the table contains numbers or dates how do I use "<" or ">" to find a range of values?

You can use >=[Enter Start Date] and <=[Enter End Date]

I would consider using a Form for this.

Your Starter for 10:

Create a simple
Add 2 Textboxes, rename them txt_StartDate and txtEndDate

Save the form, say frm_TestDates

Open your Query.

Right-click on the criteria and choose Build...
Select Forms>All Forms>frm_TestDates

in the top box type >=
You should then be able to select the txt_StartDate from the bottom middle box.

Your syntax should look like this:

>=[Forms]![frm_TestDates]![txt_StartDate]

Do the same for EndDate.

<=[Forms]![frm_TestDates]![txt_EndDate]

Save the Query.

Goto the design view of your Form

Add a button,
In the new dialogue box select Miscellaneous>Run Query
Choose your query
Run through the rest of the wizard as you wish.

now you can load the form and enter a Start Date and End Date.
Hit the button, and Bingo.

Hope that gets you started

Regards,

Thanks I will give it a try.

Do I need to put something between >=[Forms]![frm_TestDates]![txt_StartDate] and <=[Forms]![frm_TestDates]![txt_EndDate]

j3andc said:

Do I need to put something between >=[Forms]![frm_TestDates]![txt_StartDate] and <=[Forms]![frm_TestDates]![txt_EndDate]

Click to expand...

Hi,

that should be the Exact systax you need:

>=[Forms]![frm_TestDates]![txt_StartDate] And <=[Forms]![frm_TestDates]![txt_EndDate]

This goes in your Date field.
Once you leave the criteria box the and should change to And, capitalised.
This return the records within the dates you enter in the text boxes.

Regards,

You could use Between

eg

Between [Forms]![frm_TestDates]![txt_StartDate] And [Forms]![frm_TestDates]![txt_EndDate]

You could also do it in SQL view by using :

between Forms.frm_TestDates.txt_StartDate & " " and Forms.frm_TestDates.txt_EndDate & " "

I got it to work. Is there a way to write the formula so I can leave one or both of the fields blank and it would still work.