Query to pull all Records in the Past 48 hours from today

[Ruenells]

I have tried to use the =Date()-2 and it does not return any records. I have also tried DateAdd("d",=2,Date()) and not getting any results either. I am sure it is something simple I am missing

Am I doing something wrong. I am trying to create 2 separate reports
1. 48 Hours in the Past
2. 48 Hours in the future

I do not want them on the same report as the information has to be displayed in separate reports.

I would really appreciate any help as I have been trying to get this to work all weekend.

Thanks
Rue

 

[KenReay]

Hi

The syntax should be

DateAdd("d",-2,Date()) for 2 days in the past

DateAdd("d",2,Date()) for 2 days in the future

But, you say 48 hours, not two days, this may not mean the same thing, depending on the Content of the Date/Time you are comparing with, if you want 48 hours it would be

DateAdd("h",-48,Now())
or
DateAdd("h",48,Now())

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Ruenells]

Thanks for the reply. I am looking for the last 48 hours meaning 2 days. The field the information is pulling from is a combo of date/time. I know that they share the field anyway. But I really am looking for anything that has occured in the last 2 days not specific to hours.

Hope that clarifies

 

[Ruenells]

Also I tried your suggestion and no records are being returned?

 

[jrbarnett]

Can you post your full query SQL?

John

 

[Ruenells]

SELECT [48_Hour_Pull].Expr1, [48_Hour_Pull].[2-Change-Category], [48_Hour_Pull].Status, [48_Hour_Pull].[Planned-Start], [48_Hour_Pull].[Planned-Finish], [48_Hour_Pull].[1e-Requestor], [48_Hour_Pull].[Emergency-Approver], [48_Hour_Pull].Expr2, [48_Hour_Pull].[1b-Description], [48_Hour_Pull].[4-Require-Outage], [48_Hour_Pull].[Operational-Owner-Last-Name], [48_Hour_Pull].[Operational-Owner-First-Name], [48_Hour_Pull].[Operational-Dir-Last-Name], [48_Hour_Pull].[Operational-Dir-First-Name]
FROM 48_Hour_Pull
WHERE ((([48_Hour_Pull].[Planned-Start])=DateAdd("h",-48,Now())))
ORDER BY [48_Hour_Pull].[2-Change-Category];

 

[jrbarnett]

This line:

WHERE ((([48_Hour_Pull].[Planned-Start])=DateAdd("h",-48,Now())))

Indicates you are looking for a record in the Planned-Start field that is exactly 48 hours before the current date. The accuracy of that will depend on your computer clock and how accurate the data in that field is (ie is it just date, hours and minutes, or does it go to more accurate.

This is most likely why there are no exact matches - Access will look at the whole time, not just the hours.

If you want anything up to and including 48 hours old:

WHERE ((([48_Hour_Pull].[Planned-Start])<=DateAdd("h",-48,Now())))

and for over 48 hours old:

WHERE ((([48_Hour_Pull].[Planned-Start])>DateAdd("h",-48,Now())))

they are simple tweaks to the SQL you posted.

John

 

[CaptainD]

If you have a set time you want to go back 48 hours from you could use something like:

WHERE ([48_Hours_Pulled].[Planned-Start])BETWEEN Format(DateAdd("h", -48, Now()), "mm/dd/yyyy 08:00:00") AND Format(Now(), "mm/dd/yyyy 08:00:00")

 

[Ruenells]

Basically I need to pulls 2 days in the past and 2 days ONLY in the future. Right now what has been suggested is working for the past but for the future I am getting all future records.

 

[CaptainD]

Then how about:

WHERE ([48_Hours_Pulled].[Planned-Start])BETWEEN Format(DateAdd("h", -48, Now()), "mm/dd/yyyy 08:00:00") AND Format(DateAdd("h", 48,Now()), "mm/dd/yyyy 08:00:00")