Query to grab records in current month only

[tsison7]

I need a query that will grab records in the current month only. I know how
to do this in visual basic using datepart("m",[recorddate]) =datepart
("m",now()) but how can I do it in the query?

 

[Rick Brandt]

I need a query that will grab records in the current month only. I
know how to do this in visual basic using datepart("m",[recorddate])
=datepart ("m",now()) but how can I do it in the query?

WHERE DateField >= DateSerial(Year(Date()), Month(Date()), 1)
AND DateField < DateSerial(Year(Date()), Month(Date()) + 1, 1)

 

[Steve Schapel]

Tsison,

Your Visual Basic example will only work if the data only covers the
current year, otherwise you will get records for the same month in other
years. If this is the case, in Access you can do...
Month([recorddate])=Month(Date())
Otherwise...
Format([recorddate],"yyyymm")=Format(Date(),"yyyymm")

 

[tsison7]

Thanks Rick,

Before your post I added two extra fields to grab the month and the year and
filtered it that way using datepart. But your suggestion is much cleaner.
--
TIA

I need a query that will grab records in the current month only. I
know how to do this in visual basic using datepart("m",[recorddate])
=datepart ("m",now()) but how can I do it in the query?

WHERE DateField >= DateSerial(Year(Date()), Month(Date()), 1)
AND DateField < DateSerial(Year(Date()), Month(Date()) + 1, 1)

 

[tsison7]

I realized that once I ran the query. I went ended up using the dateserial
function instead.

Thanks,
--
TIA

Tsison,

Your Visual Basic example will only work if the data only covers the
current year, otherwise you will get records for the same month in other
years. If this is the case, in Access you can do...
Month([recorddate])=Month(Date())
Otherwise...
Format([recorddate],"yyyymm")=Format(Date(),"yyyymm")

--
Steve Schapel, Microsoft Access MVP

I need a query that will grab records in the current month only. I know how
to do this in visual basic using datepart("m",[recorddate]) =datepart
("m",now()) but how can I do it in the query?