Parameter Box Date Range

[buckwheat]

My table has one date of service field. In my query, I have a criteria to query a range, e.g., Between 03/01/2013 and 05/31/2013. Is there a way to put this in a parameter box so when users click the query they will get a parameter box to enter date range? I know how this works for a single date, not sure if it can be done with a range.

[rzw0wr]

Yes you can.
Enter 2 parameters in the parameter box.

Type Between [par1] AND [par2]

Dale

[buckwheat]

Hi Dale,

I did try this entry Between 3/1/2013 and 3/31/2013 before and got a message that the entry was too complex.

[rzw0wr]

You have to use the Parameter names.
If you are going to use the dates enclosed the with #.
Between #3/1/2013# AND #3/31/2013#

Dale

[buckwheat]

I tried that as well. No worries, I'll convert the date range to months in a parameter box. Thanks anyway.

[buckwheat]

Sorry I have to revisit this issue again for another db. I can successfully add Between 01/01/2013 and 07/31/2013 in the criteria and get the results.
When I enter [Enter Date Range] in as the criteria and I'm prompted to enter the date range . . . I am getting the error "This expression is typed incorrectly, or is too complex to be evaluated" I have tried it as Between #01/01/2013# AND #07/31/2013# and without the #, is there any other way to do this?

[orange]

Have you tried 2 parameters eg Date1 and Date2

Between Date1 AND Date2

[buckwheat]

Are you saying to add two fields and call them 1 and 2? and a third for the criteria?

[orange]

No I was suggesting QUERY along this structure.

PARAMETERS [Bdate] DateTime, [Edate] DateTime;
SELECT Orders.OrderID, Orders.OrderDate, Orders.ShipVia, Orders.ShipName
FROM Orders
WHERE (((Orders.OrderDate) Between [Bdate] And [EDate]));

[buckwheat]

Oh, sorry. I'll give that a try. Thanks for the advice.

[buckwheat]

I'm just now getting back to this project. Your suggestion worked perfectly. Thanks so much!

[orange]

Happy to help. Good luck with your project.

[buckwheat]

Another element to this question please: is it possible to modify the above code for a parameter box to show, e.g., <= 08/31/2013?

[orange]

How does <= 08/31/2013 fit with BDate and EDate?

You can change the SQL
eg

PARAMETERS [MyDate] DateTime;
 SELECT Orders.OrderID, Orders.OrderDate, Orders.ShipVia, Orders.ShipName FROM Orders 
WHERE ((Orders.OrderDate) <= MyDate);

[buckwheat]

thanks for the advice