Today's date less 4 months

[Les]

Hi all, I would like to create a querie whereby i have dates of orders raised
and need to calculate from this month less 4 months.
In other words i need to know how many orders were raised 0 to 4 months ago
and then 4 to six and over six months.

Any help would be greatly appreciated..

 

[Michel Walsh]

DateAdd("m", -4, Date())

subtracts 4 months, but does not give the date which would be the first of
thath month, four month ago.



DateAdd("m", -4, Date() - DatePart("d", Date()) +1 )


does.




Hoping it may help,
Vanderghast, Access MVP

 

[Les]

Hi Michel, it will definately thanks. :-0)

 

[Les]

A further question to this, i have made the following
4_Months_Ago: DateAdd("m",-4,Date()-DatePart("d",Date())+1) and
This_Month: DateAdd("m",0,Date()-DatePart("d",Date())+1).

I get the right results, but now i need to use these two dates as my search
criteria...

between 4_Months_Ago and This_Month

 

[Michel Walsh]

Ah, there is a small problem with aliases in the WHERE clause, with Jet: DO
NOT USE the alias, but the expression (cut and paste the expression,
replacing the alias) :


BETWEEN DateAdd("m",-4,Date()-DatePart("d",Date())+1)
AND Date() -DatePart("d", Date()) +1




should work, though.



Vanderghast, Access MVP

 

[Les]

Thanks again Michel, works 100%.
--
Les

Ah, there is a small problem with aliases in the WHERE clause, with Jet: DO
NOT USE the alias, but the expression (cut and paste the expression,
replacing the alias) :


BETWEEN DateAdd("m",-4,Date()-DatePart("d",Date())+1)
AND Date() -DatePart("d", Date()) +1




should work, though.



Vanderghast, Access MVP

 

[Jamie Collins]

DateAdd("m", -4, Date())

subtracts 4 months, but does not give the date which would be the first of
thath month, four month ago.

DateAdd("m", -4, Date() - DatePart("d", Date()) +1 )

does.

You expression assumes that DATETIME values have no time elements;
replace 'Date()' with 'Now()' and you no longer get the first time
granule of the month as desired.

Here's an improved version:

DATEADD('M', DATEDIFF('M', #1990-05-01 00:00:00#, NOW()), #1990-01-01
00:00:00#)

Jamie.

--

 

[Michel Walsh]

But Date() as no time element. As for the *FIELD* to which the computed
expression is compared, I assumed it is in the style of:

... WHERE fieldName
BETWEEN DateAdd("m", -4, Date() - DatePart("d", Date()) +1 )
AND Date() -DatePart("d", Date()) +1

it is not transformed neither implied in the expressions (which make the
expression seen as 'constant').


Sure, the upper limit is the first of this month at t=00:00:00

So, as example, a record with fieldName value = August 31st, 2007 at
23:45:56 will be included, but not if its value is September, 1st, 2007
at 02:03:06. SURE, if the tested field has NO time value, then the last +1
is probably to be removed, indeed:


... WHERE fieldName
BETWEEN DateAdd("m", -4, Date() - DatePart("d", Date()) +1 )
AND Date() -DatePart("d", Date())



Vanderghast, Access MVP

 

[Jamie Collins]

But Date() as no time element. As for the *FIELD* to which the computed
expression is compared, I assumed it is in the style of:

... WHERE fieldName
BETWEEN DateAdd("m", -4, Date() - DatePart("d", Date()) +1 )
AND Date() -DatePart("d", Date()) +1

it is not transformed neither implied in the expressions (which make the
expression seen as 'constant').

OK, I see what you mean now. So yours is functionally equivalent to
mine, then

Sure, the upper limit is the first of this month at t=00:00:00

So, as example, a record with fieldName value = August 31st, 2007 at
23:45:56 will be included, but not if its value is September, 1st, 2007
at 02:03:06. SURE, if the tested field has NO time value, then the last +1
is probably to be removed, indeed:

... WHERE fieldName
BETWEEN DateAdd("m", -4, Date() - DatePart("d", Date()) +1 )
AND Date() -DatePart("d", Date())

I don't agree because it would include the value #2007-09-01
00:00:00#, which I do not consider to fall within the four month
period commencing #2007-05-01 00:00:00#.

Jamie.

--

 

[Michel Walsh]

Yes, it will include also that second. To exclude it, you have to replace

x BETWEEN a AND b

which is equivalent to x >= a And x <= b

to a strict inequality for the upper limit:

x >= a AND x < b



Vanderghast, Access MVP