today - 1

B

brbaskets

I'm trying to force yesterday's date into a query criteria. My goal is to use the output as an external data source for an excel pivot table. Does anyone know how, in Access, I can get yesterday's date? My data is formatted yyyymmdd.

TIA.
 
F

fredg

I'm trying to force yesterday's date into a query criteria. My goal is to use the output as
Click to expand...
an external data source for an excel pivot table. Does anyone know
how, in Access, I can get yesterday's date? My data is formatted
yyyymmdd.
TIA.
Click to expand...

Where [DateField] = Date()-1

The actual date format is irrelevant.
 
B

Bas Cost Budde

I'm trying to force yesterday's date into a query criteria. My goal is to use the output as
an external data source for an excel pivot table. Does anyone know
how, in Access, I can get yesterday's date? My data is formatted
yyyymmdd.

Where [DateField] = Date()-1

The actual date format is irrelevant.
Click to expand...

I think it is! Dutch date format starts with the day. If an expression
is not calculated into a date somewhere in the background, "-1" will
seldom put you yesterday. I could write today as 290104 (I see that
happen! Yes I do! People act funny!).

I pledge toward using the Date/Time format for a date field anyway.
 
R

Rick Brandt

Bas Cost Budde said:
I'm trying to force yesterday's date into a query criteria. My goal is
Click to expand...
to use the output as

an external data source for an excel pivot table. Does anyone know
how, in Access, I can get yesterday's date? My data is formatted
yyyymmdd.

Where [DateField] = Date()-1

The actual date format is irrelevant.
Click to expand...

I think it is! Dutch date format starts with the day. If an expression
is not calculated into a date somewhere in the background, "-1" will
seldom put you yesterday. I could write today as 290104 (I see that
happen! Yes I do! People act funny!).

I pledge toward using the Date/Time format for a date field anyway.
Click to expand...

Date()-1 in Access VBA will always return yesterday's date. If you stuff
that result into a field in a Jet table that is of a Date DataType then
yesterday's date will be stored in that field and the format property is
completely irrelevant. The format property only deals with *display*.

If you stuff that result into a field that is not a Date DataType then yes,
you will get different results, but those results still will not have
anything to do with the format property applied to that field.
 
F

fredg

I'm trying to force yesterday's date into a query criteria. My goal is to use the output as
Click to expand...

an external data source for an excel pivot table. Does anyone know
how, in Access, I can get yesterday's date? My data is formatted
yyyymmdd.

Where [DateField] = Date()-1

The actual date format is irrelevant.
Click to expand...

I think it is! Dutch date format starts with the day. If an expression
is not calculated into a date somewhere in the background, "-1" will
seldom put you yesterday. I could write today as 290104 (I see that
happen! Yes I do! People act funny!).

I pledge toward using the Date/Time format for a date field anyway.
Click to expand...

Actually, in Access, regardless of how the field is formatted, as long
as it is a Date/Time datatype, is stored as a Double. Today is 38015.
So 38015 - 1 = 38014, yesterday.
In a Debug window, type each line with a question mark.
?cdbl(date())
Result 38015
?cdbl(Date()-1)
Result 38014
? CDate(38014)
Result 01/28/2004
 
Top