Query Criteria Inclues Time

[jereece]

I am running a query linked to a mainframe database. The Entered Date field is formatted like this: 2/14/2007 1:14:00 AM. I want to run my query to show all data greater than an entered date at 5AM. So I tried using this for a query criteria >[ENTER START DATE]+#5:00:00 AM# , but Access says the query criteria is typed incorrectly or is too complex. Below are variations of the criteria I tried to use but none would work.

>[ENTER START DATE]+(#5:00:00 AM#)
>([ENTER START DATE])+(#5:00:00 AM#)

This criteria does work >(Date()-1)+#5:00:00 AM# , but I really want the user to be prompted to enter a start date because on Monday the report has to go back 3 days, if there's a holiday it has to go back 2 days, etc. The reports always have to begin at 5am of the starting date.

Any suggestions?

Thanks,
Jim

 

[Moniker]

Can you not just separate the mainframe's stored date/time into separate date and time fields? That makes it much easier.

DateValue("2/16/2007 5:24:54 PM") = 2/16/2007
TimeValue("2/16/2007 5:24:54 PM") = 5:24:54 PM

Then, in the Criteria for your new time value, put >TimeValue("5:00 AM").

 

[jereece]

Where would I separate the mainframe's stored date/time into separate date and time fields? I only have read access to the mainframe data. Could this be done locally / temporarily somehow? I am not sure how to do what you are suggesting.

Thanks,
Jim

 

[Moniker]

Pull the mainframe data down into Access, and from there, do the manipulation within Access. Presumably, there is some record number (I'll call it RecordID) or other indicator on the mainframe, so you can pull that down as well to link your split date/time values back to the mainframe and pull the data you need.

1) Pull the date/time combined field off the server into Access.
2) Use a simple query to split the date and time into separate fields as described earlier.
3) Get the date you want from the user.
4) Run that date in a query against your newly created table that has an ID and the date and time separated, with the criteria you set to go >5am.
5) You now have the recordset with >5am and you can return to the mainframe by using the recordIDs you got earlier.

 

[john471]

criteria is typed incorrectly

Have you tried setting the "type" of the parameter?

1) Open your query in design view
2) From the Query menu, select "Parameters..."
3) In the Parameter column, type in the name of your parameter i.e. "ENTER START DATE" (without the quotes)
4) In the Data Type column, change this to "Date/Time"
5) Click OK

Try running your query again.

HTH Regards

John.