Criteria to get the day before today's date-time 1

[sawilliams]

I am writing a simple query to pull all customers whose record was updated yesterday. My date field is date-time (4/16/2009 9:30:12 PM, for example). With a short date, I can just do

date() - 1

for the criteria but I don't know what to do with a date time field.

Any suggestions would be appreciated.

 

[dhookom]

Convert your datetime field to a date only value using DateValue().

If you can't figure out the solution, please reply back with your attempted SQL statement.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Now() - 1?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Golom]

Can you clarify a bit?

Yesterday is, as you have noted, Date() - 1.

What do you mean by "My date field" and what does it have to do with computing yesterday's date?

 

[MajP]

Listen to Duane. The other two posts are incorrect. You have to convert your datefield to just a date only value. This can be done simply with the Int function as well

WHERE int(tblOne.myDateField)=Date()-1

 

[Golom]

MajP

How does my asking a question qualify as "incorrect"?

... or is this also "incorrect"?

 

[MajP]

Maybe I was confused, because I could not figure out what additional clarification was needed. It seemed pretty clear the OP has a dateTime field that stores a date and time component and wants to query all values that occured the prior day. I misread your post as stating that date()-1 was the solution, when in fact it is only part of the solution. My fault.

 

[sawilliams]

Wow, thanks for the lively responses.

To dhookom, I respond that I'm not quite sure how to implement the DateValue(), but I will try some stuff.

To SkipVought, I had already tried Now()-1 but that returns no results.

To Golum, Date()-1 works fine if the date field in the table is a short date. The date field in my table is formatted like "4/16/2009 9:30:12 PM". And sorry I was confusing when I used the term "My date field". I meant the field in the table I am querying. The field is called "merge_dt" and is formatted as I mention above.

To MajP, I tried this:

SELECT T_MERGED.merge_dt,
       T_MERGED.kept_id
FROM T_MERGED
WHERE (((Int([T_MERGED].[merge_dt]))=Date()-1));

I get an error that says: "ODBC--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type datetime to data type float, table 'T_MERGED', column name 'merge_dt'. Use the CONVERT function to run this query."

Many thanks to all for your replies. If you can offer further assistance, that would be great.

 

[dhookom]

Is this an ADP or linked SQL Server table or what? If it's an ADP then you must use functions available in SQL Server, not Access.

SELECT T_MERGED.merge_dt,
       T_MERGED.kept_id
FROM T_MERGED
WHERE DateValue([T_MERGED].[merge_dt])=Date()-1;

or

SELECT T_MERGED.merge_dt,
       T_MERGED.kept_id
FROM T_MERGED
WHERE [T_MERGED].[merge_dt] between Date()-1 AND Date();

Duane
Hook'D on Access
MS Access MVP

 

[sawilliams]

dhookom!

This is a linked SQL Server table. So your 2nd solution works beautifully. Many thanks.