DATE EXPRESSION

[fishqqq]

Can someone offer a date expression that will pull records of the
current month from a query which lists the dates as follows mmddyy

i tried Month=Now() but this won't work
any suggestions are greatly appreciated.
tks
Steve

 

[Ken Snell \(MVP\)]

Fieldname = Format(Date(), "mmddyy")

 

[John W. Vinson]

Can someone offer a date expression that will pull records of the
current month from a query which lists the dates as follows mmddyy

i tried Month=Now() but this won't work
any suggestions are greatly appreciated.
tks
Steve

If - as I'd recommend!!! - it's a Date/Time field then you can use a criterion

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

If the field in the query is a Text or a Number field (e.g. "011909" as a
string, or 11909 as an integer number) it'll be more complicated (you'll want
to convert it to a date/time value and use the same expression on that value).

 

[fishqqq]

If - as I'd recommend!!! - it's a Date/Time field then you can use a criterion


If the field in the query is a Text or a Number field (e.g. "011909" as a
string, or 11909 as an integer number) it'll be more complicated (you'll want
to convert it to a date/time value and use the same expression on that value).

Thanks John, that expression above worked (it is a date/time field
btw).
i would also like to total the months ie if there are 5 records in
may, 9 records in jun etc i would like a total query to return the
totals only. Is there a way to set the criteria to do this? right now
it lists all the entries and if i put a "group by" action it will
group by individual days (not months) this would return all 5 records
in may (assuming they are done on separate days - instead of grouping
all of may and telling me there are 5 records.
Is there a way around this?

 

[John W. Vinson]

On Tue, 20 Jan 2009 15:15:54 -0800 (PST), "(e-mail address removed)"

..

i would also like to total the months ie if there are 5 records in
may, 9 records in jun etc i would like a total query to return the
totals only. Is there a way to set the criteria to do this? right now
it lists all the entries and if i put a "group by" action it will
group by individual days (not months) this would return all 5 records
in may (assuming they are done on separate days - instead of grouping
all of may and telling me there are 5 records.
Is there a way around this?

Yes. Use a calculated field in the query:

GroupMonth: Format([datefield], "yyyy-mm")

This will give you values like 2008-12 and 2009-01, which will sort
chronologically (month names will sort alphabetically, Apr, Aug, Dec, Feb...);
you can group by this calculated field.