asked on

DateAdd Function ???

I have a date in the format yyyymm and I want to be able to take 1 month off.

IE: 200606 would become 200605 I could just use [month]-1 but that would only work for 11 months of the year as 200601 would become 200600 and I want it to be 200512.

I think I need the dateadd function but cant for the life of me figure it out.

Carl.

peter57r

Hi Carl2002,

If you are storing the date in a datetime field then you can use Dateadd() to subtract 1 month.

Pete

Carl2002

ASKER

The field is a result of a query the query is simply Format(now(),"yyyymm"). Will this be a string ?

Rick_Rickards

Format(DateSerial(Left([Field], 4), Right([Field], 2), 0), "yyyymm")

Carl2002

ASKER

This works to find the prior month but how would it then find 6 months ago ?

peter57r

Carl2002,
You said you wanted to take 1 month off; where does 6 months come into it?

Pete

Carl2002

ASKER

OK sorry guys think my initial question didn't explain it all. I do want to take 1 month off, but there will be times I want to take more than 1 month off. Is this possible?

Rick_Rickards

Another way to take 1 Month off...
Format(DateSerial(Left([Field], 4), Right([Field], 2)-1,1), "yyyymm")

To Take 6 months off...
Format(DateSerial(Left([Field], 4), Right([Field], 2)-6, 1), "yyyymm")

To Add 6 Months...
Format(DateSerial(Left([Field], 4), Right([Field], 2)+6, 1), "yyyymm")

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial