QUERY BY MONTH

[FluffyKitty]

ok, here's the deal:

> I have a date field [ReminderDate]

> The user needs to be able to select a month (Jan-Dec) via a combo box on a form...then

> I need the query to return all records that have the matching month (i.e. March) EXCEPT current year ('03)

> Example: 3/1/01, 3/1/02, 3/1/03 - The query would return the 3/1/01 & 3/1/02 BUT NOT the 3/1/03

I've tried to run a query using the month() and the <>Year(Now()) functions but I can't seem to figure out how to work the form into the picture because the month is a 'string'. Anyway, I searched the threads and couldn't find a matching answer, so if anyone can direct/help me I would greatly appreciate it.

Thanks in advanced for all your help,
FluffyKitty

 

[WildHare]

There are really two logical operations being done here.
1) get all the matching months guys
2) scratch off the one for THIS year.

Now, to make this a little easier, you might make one minor change to your combo. I assume it's just displaying January, February, March, etc etc.

What we want to do is convert that STRING to the MONTH NUMBER that it is, e.g. January --> 1, Feb --> 2, etc.

You can do this about 17 different ways, I'd just add another zero width column to the combo, with the month number in it, and make it the bound column, so when you make a choice, the NUMBER is saved, not the text description.

To select all the records whose RemDate is the same MONTH as one selected from a combo or whatever, this is the syntax, once you've got a NUMBER rather than a STRING

Where Month(remDate) = myCombo,

So if you select February, it becomes

Where Month(remDate) = 2

This grabs all the guys whose MONTH is February.

Now, to exclude all the guys who are NOT this year guys, add this part

Where Month(RemDate) = MyCombo and Year(RemDate) < Year(Date())

This would resolve to

Where Month(remDate) = 2 and Year(RemDate) < 2003

So Feb 2001 would get by, and Feb 2002, and Feb 1979 and so on.. but NOT Feb 2003

Got it?

Jim


Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[FluffyKitty]

Thank you WildHare so much...

I actually worked it out w/ a list box to select the months w/ a row source from a static months table (MonthNum & MonthName fields) and just bound that to my parameter query which points back to the list box, so when I select a month and click &quot;print report&quot; it works perrrrrrfectly. (yippiee)

again, thanks for the advice...help is always appreciated.

FluffyKitty