Sorting of Date and Time in Query

[JayDubya]

I have a Date/Time field that I am trying to sort by using:

Between[Enter The Date] And [Enter The Date]

I would like the user to specify a date (mm/dd/yyyy) and time range in 24hr time (HHMM). The criteria entered may be on the same day, it may overlap the next day.

I am new to Access and SQL and I cannot find the proper syntax for the Criteria nor can I find an example of the syntax to enter at the prompt for the date and time (it returns zero records). I have searched the forum and not found anything close what what I am trying to do.

Any help would be appreciated. Thanks!

 

[Galaxiom]

Much better to run the query from a form with text boxes formatted as Date/Time to input the criteria.
These boxes don't need to be reentered every time the query runs and can have default values.

Refer to the textboxes as Forms!formname!textboxname in the query.

 

[JayDubya]

Thanks, but I have a form that is used to enter data on the table. The query is for a report that needs to be run only a few times a day. The problem I have is that the query needs to be a 4 hr period sometimes and other times it could be a 7 1/2 day period.

 

[Galaxiom]

You will probably have some form with a button to run the Report. Place the unbound textboxes on that form. It is a much better way than using criteria dialogs.

You can even have separate date and time boxes which get combined in the query. You don't have to have default values for the boxes if you don't want.

 

[JayDubya]

I understand what you are saying. I am trying to do this in the query criteria to prevent having to train 200 people on a new method.

The criteria I had was working just fine for a week until someone tried to run a report across the 24hr period into the next day.

 

[raskew]

Hi -

Take a look at DCrake's time selector. Some really good stuff here which you may be able to adapt to your needs.

Bob

 

[JayDubya]

Thanks! I will check it out.