Number+Text --> Date 1

[endoflux]

Gentlemen--

I'm writing a query to make me a table of "Resulting Dates", which are the start of each month from the current month back 11 months (11/01/2005, 10/01/2005 ... 12/01/2004).

I've created a table to give me a 'subtracting value' 0-11), and with that I begin my first field:

RSLTMONTH: CInt(Format(Date(),"mm"))-[~SubtractMonth]![SubtractMonth]

...which gives me a number from 12 to -11, depending on what the current date is-- this works.

From there, I created a second field:

IIF(
RSLTMONTH >=1, Cdate(RSLTMONTH+"/01/"+right(Cstr(DATE()), 4)),Cdate(12-RSLTMONTH+"/01/"+Cint(right(Cstr(DATE()), 4))-1))

...which says in english: If the RSLTMONTH is this January or greater, print out RSLTMONTH/01/This Year, Else subtract RSLTMONTH from 12 to give me a valid month, and print that along with /01/Last Year. --This DOESN'T work (it gives me an output of #Error for each record in Datasheet View, but no coding error)...I suspect it's not happy with my "/01/" nomenclature...can anyone see what I'm doing wrong here?

Thx!

 

[SkipVought]

Hi,

Why not use DateDiff.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[endoflux]

WHOA! ...Cause I didn't know it existed

I did away with both formulas and did this:

Expr1: DateAdd("m",-([~SubtractMonth]![SubtractMonth]),IIf(Left(CStr(Date()),2) Like "?/",Left(CStr(Date()),1),Left(CStr(Date()),2))+"/01/"+Right(CStr(Date()),4))

...and it's beautiful! Thanks!