Query that only looks for month/year????

[TYLER_DURDEN]

I need to make a query that finds date that match a specific month and year. For example Jun-02. I can do it with the day-month-year, but I wanted to find dates by only the month-year. I was just wondering if someone could help me. If you just new what I needed to type into the criteria, I'd really appreciate it.

Thanks

 

[Hypernoodle]

Select FIELD from TABLE
where format([datefield], "mm/yyyy") = "06/2002"

 

[TYLER_DURDEN]

Hmmm, still confused.

What exactly should it look like in the criteria portion. Also, I want so that when I type in any month and year it finds the match, not just June 2002. Sorry just a little confused still.

plus the comma is throwing off the program

thanks

 

[Cosmos75]

In the query design view add a field

DateMONTH: Month([datefield])
where datefield is the name of your field that is a date.


And in the criteria row enter the number of the month you are looking for, e.g. 6 for June.

 

[Cosmos75]

Now do the same for year in another query column (design viewp

DateYEAR: Year([datefield])
where datefield is the name of your field that is a date.

And in the criteria row enter the yeaer you are looking for, e.g. 2003.

You can even have the criteria be entered through a form by creating an text box for month and year. Right-click in an empty feild and choose Build to get the correct reference to the textbox on the form. Keep the open and you can just choose Loaded Forms and look for the appropriate textbox.

Hope this helps!

 

[TYLER_DURDEN]

Woo Hoo!!! Thanks Cosmos75, it's works great!!!!

TDS

 

[Cosmos75]

You're very welcome.


I actually just used this on a database where I needed someway to calculate rolling 12-month usages (This months usage plus previous 11 months, e.g. Feb 2002 - Jan 2003)).

You could also use a parameter where you query asks you to enter the criteria when you run the query instead of having to specify it in the design of the query. Instead of having to enter a number in the query, use a paramter for the month like [Enter number for month] for the month column and [Enter Year] for the year column.