Subtracting 1 month from a date query 1

[FireViking]

Hi all,
Its been a while since my last visit.
Here goes, I have created a report that uses a query that calls for data between a start and end date. I am trying to create an SQL using the start and end dates to retreive information from 1 month earlier. I thought that I could acheive this by subtracting 1 from the month.

The following code is an example of what I have written.

strStartLastMonth = month(strStartDate)-1 &"/"& day(strStartDate) &"/"& year(strStartDate)

Can someone help out here please.

 

[KenReay]

Hi

How about:



strStartLastMonth = DataAdd("m",-1,strStartDate)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[FireViking]

Thanks heaps! i'll give it a go

 

[FireViking]

This worked fine. Thanks again. The DataAdd spun me for a sec, then realised it should be DateAdd LOL.

 

[KenReay]

Hi

oops, yes DateAdd, I wish they would stop moving the key about on the keyboard Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[MichaelRed]

Hmmmmmmmmmmmmmmm,

yes,


... but ... but ...

strStartDate = Str(Date)
? strStartDate
10/29/02
strStartLastMonth = month(strStartDate)-1 &"/"& day(strStartDate) &"/"& year(strStartDate)
? strStartLastMonth
9/29/2002

So, the original question remains unanswered and unresolved, why -indeed- does it NOT work, as it certainly APPEARS to work.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[KenReay]

Hi Michael

While the original formula (using Month()-1), may work sometimes, it will not work consistently eg try it with (for example)

30 March -> 30 Feb is an invalid date
31 October -> 31 Sep is an invalid date
any date in January -> any date with a month of zero is invalid






Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK