Select Query grouping by month

[scottie]

Hi Everyone.
First time here and what a site, my hat off to all the boffins here. I have looked though the forum and have found many interesting threads and could spend hours just crusing though the pages But time moves on.

I am reletively new to access, and there maybe something that i am missing.

What I would like to be able to do:- using a select query, is select all records in a table that fall into the current month. I can do it using "between [start date] and [end Date]". however I would like to be able to do it without having to enter the date sequence, and base the query on "date()" i.e the current month.

I do so hope that some of you clever people have an idea, or perhaps could point me in the right direction.

Thanks in advance for your troubles and keep up the good work. :-)

[hjozinovic]

hey Scottie!
welcome to bytes :-)
You can make this by using Format function or DatePart function...
[HTML]1. Create a new field for months:
DateMonth: Format([OldDate];"mm.yyyy")
2. In CurrentMonth criteria row put:
=Format(Now();" mm.yyyy")[/HTML]
In step 1. you isolate the month and the year for the date field.
In step 2. you select only dates from current month (and current year)

[scottie]

hjozinovic, Thank you so very very much!!!!!!

Just one thing that had me foxed for a bit was the use of the ; I kept getting a syntax error, but on changing it to a , comma it worked a treat.

Thanks again
Scottie

[hjozinovic]

hjozinovic, Thank you so very very much!!!!!!

Just one thing that had me foxed for a bit was the use of the ; I kept getting a syntax error, but on changing it to a , comma it worked a treat.

Thanks again
Scottie

I'm glad you did it! God work scottie!
Concerning ";" I'm in Croatia and that sign works for me in queries, but I also switch to "," when using VBA ;-)

[youmike]

Another way of doing what you want is to use the function "DatePart". It works both in VBA and in SQL and has a range of alternative search parameters. Look it up in the help file.