Not Pulling all the records for last 24 hours

[ruenells]

Here is my issue I am having, With the records we are pulling it is not pulling everything from the previous 24 hours. It is using the "D" to pull the information, however it is also using the default system time, so if i have a record that occured last night at 8:30 pm it is not showing up.

How do i get all of the records from the last 24 hours regardless of system time?

 

[Brianwarnock]

How are you rquesting the date?
Via hrad code or is it parameter driven if so pop up or from form.

Brian

 

[ruenells]

Between (DateAdd("d",0,Date())) And (DateAdd("d",-2,Date()))

 

[Brianwarnock]

I don't know if the system objects to the dates being the wrong way round, put the -2 first, wont that make it the last 2 days?
From 23 Jan at 00 hors to 25th Jan at 00 hours.

Brian

 

[ruenells]

Well I switched it around still didnt pull anything past the time on my pc?

 

[Brianwarnock]

Well I switched it around still didnt pull anything past the time on my pc?

The time on your PC will be now how can there be records in the future plus that is not the last 24 hours. If you want all todays records then use Date()+1. ie Between (DateAdd("d",0,Date())) And (DateAdd("d",1,Date()))

Brian

 

[ruenells]

Sorry I guess I was not clear I am looking for all records for the last 24 hours. or the last day from midnight to midnight

 

[Brianwarnock]

The thing to remember when doing this type of selection is that if only the date is quoted as in Date(), the time will default to 0, but the system clock has both date and time and therefore you must adjust for this , either by adding a day to the end time or more correctly 23:59:59.

so Between Date()-1 And Date() ran today would get all records for 24th Jan.

Brian

 

[ruenells]

So how do I do that then?

 

[Brianwarnock]

Did our posts cross?

 

[ruenells]

Ok I tried what you suggested and it pulled 2 records leaving off the third that was done at 8:30pm last night.

That is why I am wondering how to get all the records?

 

[Brianwarnock]

What were the dates and times in all 3 records?
Brian

 

[ruenells]

Planned-Start Planned-Finish
1/24/2008 9:00:00 AM 1/24/2008 1:00:00 PM
1/24/2008 9:00:00 AM 1/25/2008 10:00:00 AM
1/24/2008 8:30:00 PM 1/24/2008 11:00:00 PM

 

[Brianwarnock]

I take it you are selecting on the planned start. I can think of no reason as to why the third record was not selected. Any chance of you attaching the DB? I am assuming that there was no other criteria involved.

Brian

 

[ruenells]

nope it is very clean. It just is not pulling it so that is why I am asking if there is a way to pull it based on the time since the other criteria is not working

I think it should work as well but I am stuck!

 

[Jon K]

We need more detail on two points:

1) In post #7 you wrote: "I am looking for all records for the last 24 hours. or the last day from midnight to midnight"

By "the last day from midnight to midnight", do you mean if the query is run anytime today 28 January, you want it to pull all records that occurred on 27 January from 0:0:0 to 23:59:59?


2) There are two date/time fields [Planned-Start] and [Planned-Finish] in your data. So each record represents a period of time rather than a point of time.

Are you selecting on the period from [Planned-Start] to [Planned-Finish] or selecting only on the [Planned-Start] time?
.

 

[ruenells]

I am looking for the second 00:00:00 to 23:59 and i am only pulling from the Planned Start field

 

[Jon K]

Try this criteira for the Planned Start field:
between Date()-1 and DateAdd("s",-1,Date())

When the query is run on 28 January, it should pull all records with
Planned Start between 27/1/2008 0:0:0 and 27/1/2008 23:59:59
.

 

[jdraw]

nope it is very clean. It just is not pulling it so that is why I am asking if there is a way to pull it based on the time since the other criteria is not working

I think it should work as well but I am stuck!

What is the SQL for your query?
I'm still not clear on what you mean by the last 24 hours -- I take it you mean yesterday (anytime yesterday as Jon K said 0:0:0 to 23:59:59).

Have you resolved the issue?

 

[ruenells]

I have not resolved this, in fact it has gotten worse. I am basically trying to pull any information that would be for the past 2 days...so it doesnt matter if i have to put it in in hours or days just the last 2 days 00:00 to 11:59 pm

Here is the SQL
SELECT [48_Hour_Pull].Expr1, [48_Hour_Pull].[2-Change-Category], [48_Hour_Pull].[1a-Request-Name], [48_Hour_Pull].[Severity-of-Change], [48_Hour_Pull].[Planned-Start], [48_Hour_Pull].[Planned-Finish], [48_Hour_Pull].Status, [48_Hour_Pull].[Complete-Status], [48_Hour_Pull].[Actual-Start], [48_Hour_Pull].[Actual-Finish], [48_Hour_Pull].[Emergency-Approver], Approval_Codes.Reason, Approval_Codes.[Code #], Count([48_Hour_Pull].Expr1) AS CountOfExpr1
FROM (48_Hour_Pull INNER JOIN BJWfindcodenumber AS BJWfindcodenumber_1 ON [48_Hour_Pull].Expr1 = BJWfindcodenumber_1.Expr1) INNER JOIN Approval_Codes ON BJWfindcodenumber_1.codeNo = Approval_Codes.[Code #]
GROUP BY [48_Hour_Pull].Expr1, [48_Hour_Pull].[2-Change-Category], [48_Hour_Pull].[1a-Request-Name], [48_Hour_Pull].[Severity-of-Change], [48_Hour_Pull].[Planned-Start], [48_Hour_Pull].[Planned-Finish], [48_Hour_Pull].Status, [48_Hour_Pull].[Complete-Status], [48_Hour_Pull].[Actual-Start], [48_Hour_Pull].[Actual-Finish], [48_Hour_Pull].[Emergency-Approver], Approval_Codes.Reason, Approval_Codes.[Code #]
HAVING ((([48_Hour_Pull].[Severity-of-Change])="Emer Br/Fix") AND (([48_Hour_Pull].[Planned-Start]) Between (DateAdd("d",0,Date())) And (DateAdd("d",-3,Date()))));