Current month

[Guest]

I am trying to run query where I need to pull only current month data.
My date field format is 'mm/dd/yyyy'. What format should i use to pull only
current month data.

Thank you.

 

[Jeff Boyce]

If you add a field to your query that calculates month, you can compare to
the current month.

One way to do this is to use something like:

Month([YourDateField])

as the field and compare it to

Month(Date())

in the Selection Criterion.

NOTE! Last year, in February, the month was ALSO = 2. You may want to
compare both Month and Year.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

In a query i added extra fields for month and year. Where should i use
Month(Date()) format?

If you add a field to your query that calculates month, you can compare to
the current month.

One way to do this is to use something like:

Month([YourDateField])

as the field and compare it to

Month(Date())

in the Selection Criterion.

NOTE! Last year, in February, the month was ALSO = 2. You may want to
compare both Month and Year.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to run query where I need to pull only current month data.
My date field format is 'mm/dd/yyyy'. What format should i use to pull
only
current month data.

Thank you.

 

[Rick Brandt]

I am trying to run query where I need to pull only current month data.
My date field format is 'mm/dd/yyyy'. What format should i use to
pull only current month data.

Thank you.

SELECT *
FROM TableName
WHERE DateField >= DateSerial(Year(Date()), Month(Date()), 1)
AND DateField < DateSerial(Year(Date()), Month(Date()) + 1, 1)

Always try to avoid expressions on your fields when making comparisons in a
query. Expressions involving constants or VBA functions don't hurt
performance, but an expression around your field makes a big difference on
larger tables.

 

[Guest]

When i tryied to run sql i am getting msgerror "Undefined function 'Date' in
expression"? .....

 

[Jeff Boyce]

Didn't you want the month of the current date to be your selection
criterion? There's a row for selection criterion in the query design grid.
It would go under the field you added to calculate the
Month([YourDateField]).

Regards

Jeff Boyce
Microsoft Office/Access MVP>

In a query i added extra fields for month and year. Where should i use
Month(Date()) format?

If you add a field to your query that calculates month, you can compare
to
the current month.

One way to do this is to use something like:

Month([YourDateField])

as the field and compare it to

Month(Date())

in the Selection Criterion.

NOTE! Last year, in February, the month was ALSO = 2. You may want to
compare both Month and Year.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to run query where I need to pull only current month data.
My date field format is 'mm/dd/yyyy'. What format should i use to pull
only
current month data.

Thank you.

 

[Rick Brandt]

When i tryied to run sql i am getting msgerror "Undefined function
'Date' in expression"? .....

Failure of the Date function usually means you have a broken reference. Open a
VBA window and then look at Tools - References. Any references currently active
in your file will be at the top of the list and "Checked".

If any of the checked ones have the word "MISSING" in the description then that
is your problem. Often these are unneeded references that might have gotten
added during experimenting with ActiveX controls and you can just uncheck them
to solve the problem. If you can uncheck the reference and still compile
without errors then you didn't need the reference anyway.

In none are marked missing uncheck one of the checked ones (take note of what it
is), close the reference box, then go right back in and re-check the same one.
This can sometimes fix the problem.

 

[Guest]

Thank you so much, you are right. I was missing some object.

Thanks again.