Combo Box - Past 12 Months

[shift2076]

after a lot of googling and trying to figure this one out on my own, i've come up with nothing. all i need is a combo box that starts with the current month and goes back one year. something like:

May 2007
Apr 2007
Mar 2007
Feb 2007
Jan 2007
Dec 2006...

i've tried a couple methods with no success so far. any help as to where to start out would be appreciated.

thanks,
mj

 

[pbaldy]

One way would be a UNION query using Date(), DateAdd() and Format().

 

[shift2076]

i tried to run a union query and it's giving me the error - query input must contain at least one table or query.

 

[shift2076]

next step, of course, was to write the dates into one query and then write the union query. it's giving me the same error now. this makes no sense as i AM referencing a query now.

computers suck

 

[pbaldy]

I should have mentioned that you'd need a one record table to use as the source. It's irrelevant what's in it. The work is done in the UNION query.

 

[shift2076]

ok.

it's working alright now, referencing a 'dummy' table. the only thing i can't seem to figure out is the format -

Format(Now(), "mmm yyyy") is returning a charactor string (arranges the months alphabetically)

Format(Now(), mmm yyyy) is telling me - syntax error, missing operator.

i had this problem before, and the workaround won't work in this instance. i'm still working on it though.

thanks,
mj

 

[boblarson]

Format(Now(), mmm yyyy) is telling me - syntax error, missing operator.

The format is a string so mmm yyyy has to be in double quotes.

If you want to sort by month you'll need one more column to sort (DESC) with to go with your other one (not sorted).

CInt(Format(Date(), "yyyymm"))

and the reason I used yyyymm is that it will sort by year and then by month, which is the way you probably want it, eh?

 

[pbaldy]

What he said, except that you don't need to do the CInt(Format()) bit. Simply having the pure calculated field as the second column will let you sort on it. I mean the bit that would be the first argument within your format function (Now() in your example).

 

[boblarson]

Yeah, I forgot you already had that in there.