month query

[awake2424]

Using an access 2003 query is it possible to only select those records in a month?
if I use something like:
>=

.[Column]-30 And <=

.[Column]
The problem being that not every month has 30 days in it and my date is in the same column of the table. Any help is apreciated. Thanks.

 

[John Big Booty]

If you simply want the dates from the current month create an expression in your query grid that looks something like Expr1:Month(DateFieldName) then in the criteria use Month(Date()).

If you want a little more flexibility you could use a combo that returned the month number and use Forms!FRM_Name!ComboName in place of Month(Date())

 

[John Big Booty]

Here's an example of how it could work. The criteria in the query have been written so a result will be returned irrespective of whether a a selection has been made from the combo boxes. The combo boxes are populated by queries that will present only available months or years dependant on the dates available in the table.

 

[namliam]

If you simply want the dates from the current month create an expression in your query grid that looks something like Expr1:Month(DateFieldName) then in the criteria use Month(Date()).

If you want a little more flexibility you could use a combo that returned the month number and use Forms!FRM_Name!ComboName in place of Month(Date())

Jon,

Thought this way works, its not 'perfect' when querying on indexed fields in particular as month() will not be able to use a index.

Instead you can use: Date() - Day(date()) + 1
To always find the first of this month.
01/Feb/2010 First day of previous month: dateadd("M",-1, Date() - Day(date()) + 1)
01/Mar/2010 First day of this month: Date() - Day(date()) + 1
01/Apr/2010 First day of next month: dateadd("M",1, Date() - Day(date()) + 1)
31/Mar/2010 Last day of this month: dateadd("M",1, Date() - Day(date()) + 1) - 1
28/Feb/2010 Last day of previous month: Date() - Day(date())
31/Jan/2010 Last day of 2 months ago: dateadd("M",-1, Date() - Day(date()) + 1) - 1

Because you then use 'real' dates, this can use an index and thus will yield much better/faster results.