I am trying to pull a query for times greater than 5am...

**twinnyfo** · Dec 24 '14, 11:28 AM

sooli,

Try to keep in mind that Dates are integers and times are fractions of integers. So, 5:00 am is actually 0.208333.

To calculate times, the best method is to use the DateAdd() Function. So, to determine a date at 5:00 am, you would take the date (be sure to use a DATE and not a DateTime) and add 5 hours:

Code:

WHERE [DateTimeField] > #" & _
    DataAdd("h", 5, [Enter yesterdays date]) & "#"

This works, because "yesterday' s date" is, technically speaking, 12/23/2014.0000. When you add 5 hours, that new, numerical value becomes 12/23/2014.208333.

Hope this hepps!

**Rabbit** · Dec 24 '14, 04:41 PM

Also, if the parameter is always yesterday's date, there's no need to prompt for it. You can subtract one day from the current date.

By the way, just because you're not displaying the date portion doesn't mean it's not there in the data. #5:00 AM# has a hidden date portion of 12/30/1899.

And Hour(5) is getting the hour of the value #01/04/1900 00:00:00 AM# which is equal to 0.

**sooli** · Dec 27 '14, 09:25 AM

THANK YOU! This worked perfectly! You rock!

**sooli** · Dec 27 '14, 09:25 AM

Thank you for the excellent explanation! Now this makes sense!

**sooli** · Dec 29 '14, 10:58 AM

well.. something has gone wrong.. its not working... it is still pulling the whole day's data... not just from yesterday @ 5:00 am to today @ 5:00 am...

here's my code:

Code:

SELECT MonitoringCheck.Solution, MonitoringCheck.CDate, MonitoringCheck.CTime, MonitoringCheck.Technician, MonitoringCheck.MonitoringComplete, MonitoringCheck.Incident, MonitoringCheck.Server, MonitoringCheck.Comments, MonitoringCheck.LogFile, MonitoringCheck.ProductionVerification, MonitoringCheck.TestVerification, MonitoringCheck.MIPPatchingVerification, MonitoringCheck.MIPPatchingVerification, MonitoringCheck.CAECoreCopied, MonitoringCheck.MachineAddCreation, MonitoringCheck.MachineAddResolution, MonitoringCheck.MachineDesubCreation, MonitoringCheck.MachineDesubResolution, MonitoringCheck.[SEPHomeUse(NoNew)], MonitoringCheck.[SEPHomeUse(Posted)]
FROM MonitoringCheck
WHERE (((MonitoringCheck.CDate)>=DateAdd("h",0.5,[enter yesterdays date])))
ORDER BY MonitoringCheck.CDate, MonitoringCheck.CTime;

**sooli** · Dec 29 '14, 11:48 AM

I think i got it! using the .20833

Code:

SELECT MonitoringCheck.Solution, MonitoringCheck.CDate, MonitoringCheck.CTime, MonitoringCheck.Technician, MonitoringCheck.MonitoringComplete, MonitoringCheck.Incident, MonitoringCheck.Server, MonitoringCheck.Comments, MonitoringCheck.LogFile, MonitoringCheck.ProductionVerification, MonitoringCheck.TestVerification, MonitoringCheck.MIPPatchingVerification, MonitoringCheck.MIPPatchingVerification, MonitoringCheck.CAECoreCopied, MonitoringCheck.MachineAddCreation, MonitoringCheck.MachineAddResolution, MonitoringCheck.MachineDesubCreation, MonitoringCheck.MachineDesubResolution, MonitoringCheck.[SEPHomeUse(NoNew)], MonitoringCheck.[SEPHomeUse(Posted)]
FROM MonitoringCheck
WHERE (((MonitoringCheck.CDate)>Date()-2) AND ((MonitoringCheck.CTime)>0.20833))
ORDER BY MonitoringCheck.CDate, MonitoringCheck.CTime;

seems to be working

**sooli** · Dec 29 '14, 12:01 PM

nope... i'm at a loss... the last bit of code was eliminating the some of the data before 5am on the current day.. i don't get it...

**twinnyfo** · Dec 29 '14, 02:39 PM

I would still recommend going with your code in Post #6, but change line 3:

Code:

WHERE (((MonitoringCheck.CDate)>=DateAdd("h",5,[enter yesterdays date])))

Your code was looking at all times after 12:30 am.

**Rabbit** · Dec 29 '14, 06:28 PM

You didn't mention this before:

... to today @ 5:00 am...

You need an additional filter to account for this.

**sooli** · Dec 29 '14, 11:35 PM

so something like:

Code:

 Where monitoringcheck.CDate  BETWEEN(((monitoringcheck.CDate)>=DateAdd("h",5,[enter yesterdays date] AND((monitoringcheck.CDate)<=DateAdd("h",5,date()))

im not at work to try... but thought I'd run this by you all while it was on my mind...

**sooli** · Dec 30 '14, 11:32 AM

well that didn't work either... still pulling from midnight rather than 5:00 am....

on another note... if I can...

how in the world do i hide the database window? The thing takes up the whole screen, all my users need are the forms I created... used to be a check box that would let you not display the application... that feature seems to be gone in 2013 =/

**twinnyfo** · Dec 30 '14, 12:00 PM

First, are you SURE that monitoringcheck .CDate is a Date/Time?

If it is, then try this:

Code:

WHERE monitoringcheck.CDate BETWEEN DateAdd("h",5,DateAdd("d", -1, Date()) AND DateAdd("h", 5, Date())

Try that.

As far as your second question in the last post, please start a new thread, and I'd be glad to provide the solution I use (we like to avoid multiple questions in one thread).

**sooli** · Dec 30 '14, 02:57 PM

Yes, it is definatly a date/time in the table, set to short date format, and the CTime is also a date/time format in the table, set to short time format.

Dates and times are auto populated on a click event

**twinnyfo** · Dec 30 '14, 04:54 PM

When you assign the Date/Time in the OnClick event are you using Date() or Now()?