Thread: Update Query Date Substract

I'm trying to update column 2 based on column 1, by subtracting 1 month.

exaple:

Column1 -----> Column2
01-09-2011 01-08-2011
02-09-2011 02-08-2011
01-10-2011 01-09-2011

So any idea how to write the Update Query ?

Hi ciprian,

I hate dates too!!
Excel has this simple Date(Year, Month, Day) function that you simply do a
=Date(Year,Month - 1, Day) of the first date and poof - the correct answer (except fo the March 29th that don't have leap year days).

Access seems to not have this ability. I'd love to find an easier way but here is what I came up with. In the Update To: box in the QBE (Query By Example) put this formula for the "Month Before Field". I'm using the first date as a "Start Date" field.

DateValue(CStr(Choose(Month([Sheet1]![Start Date]),12,1,2,3,4,5,6,7,8,9,10,11)) & "/" & CStr(Day([Sheet1]![Start Date])) & "/" & CStr(IIf((Month([Sheet1]![Start Date])=1),Year([Sheet1]![Start Date])-1,Year([Sheet1]![Start Date]))))

What the above does is takes the month, day and year and breaks them out from the start date. Then if the month is 1 (of the start date) make it 12 (Access couldn't handle a month of zero). Also subtract a year if the month was 1.

There must be an easier way but the above seems to work. Maybe one of the smart gurus can show us a much easier way.

I'm attaching the Access file in 2010 format as that is what I'm using.

Try using the DateAdd function in Access in a query.

http://www.techonthenet.com/access/f...te/dateadd.php

SELECT Table1.Date1, DateAdd("m",-1,[Date1]) AS LastMonth
FROM Table1
ORDER BY Table1.Auto;

Alan

Hi ciprian,

Alan WINS!! His formula works and mine was way too hard. Find it above and in my attached.

DateAdd using a negative one month works much better than mine.

What I do find amusing is that Excel and Access give a different answer for month before for 3/29/2011.

Excel gives 3/1/2011 and Access gives 2/28/2011. Go figure??

This board won't let me attach an accdb file so here is the SQL equivalent to be put in the Update row under the MonthBefore field of Table Sheet1.

UPDATE Sheet1 SET Sheet1.[Month Before] = DateAdd("m",-1,[Sheet1]![Start Date]);