Use current month as criterion in query

[Guest]

I have a Query that lists all orders logged onto our system.
Rather than have a date parameter or a range that the user has to enter, I
would like to have a version of the Query that automatically returns just the
orders logged in the current month.
My dates are in Short Date format for the UK i.e. dd/mm/yyyy.
I have tried setting the criteria for the Order Date in the Query in various
ways but can't get it right - I'm hoping this will be a simple one for
somebody and look forward to hearing from you!
Many thanks
CW

 

[Larry Linson]

I have a Query that lists all orders logged onto our system.
Rather than have a date parameter or a range that the user
has to enter, I would like to have a version of the Query
that automatically returns just the orders logged in the
current month. My dates are in Short Date format for the
UK i.e. dd/mm/yyyy. I have tried setting the criteria for
the Order Date in the Query in various
ways but can't get it right - I'm hoping this will be a simple one for
somebody and look forward to hearing from you!

If you have more than one year's data in the table, "current month" is
determined by "current month" and "current year".

Where OrderDate represents the date in the Order:

Create a calculated field column with data value of MoToCheck:
Month([OrderDate],
with a criteria of Month(Date()).

Create a calculated field column with data value of YrToCheck:
Year([OrderDate]),
with a criteria of Year(Date()).

These fields do not have to be visible in the Query, if you prefer to
uncheck the Show box.

Larry Linson
Microsoft Access MVP

 

[fredg]

I have a Query that lists all orders logged onto our system.
Rather than have a date parameter or a range that the user has to enter, I
would like to have a version of the Query that automatically returns just the
orders logged in the current month.
My dates are in Short Date format for the UK i.e. dd/mm/yyyy.
I have tried setting the criteria for the Order Date in the Query in various
ways but can't get it right - I'm hoping this will be a simple one for
somebody and look forward to hearing from you!
Many thanks
CW

In this instance, your date format of dd/mm/yyyy is not relevant.

Add a new column to the query grid.
ReturnThese:Format([DateField],"mm/yyyy")

As criteria on this column, write:
Format(Date(),"mm/yyyy")

Only records for the current month and year will be returned.

 

[Guest]

Larry -
Thanks a lot. The issue of multiple years and finding only the current month
in the current YEAR hadn't occurred to me so I'm really grateful that you
covered that, as it will definitely be an issue going forward. We have only
been running the system since last October so we wouldn't have run into it
for a few months yet, but it's great that your code will deal with it when we
get there.
Thanks again!
CW

I have a Query that lists all orders logged onto our system.
Rather than have a date parameter or a range that the user
has to enter, I would like to have a version of the Query
that automatically returns just the orders logged in the
current month. My dates are in Short Date format for the
UK i.e. dd/mm/yyyy. I have tried setting the criteria for
the Order Date in the Query in various
ways but can't get it right - I'm hoping this will be a simple one for
somebody and look forward to hearing from you!

If you have more than one year's data in the table, "current month" is
determined by "current month" and "current year".

Where OrderDate represents the date in the Order:

Create a calculated field column with data value of MoToCheck:
Month([OrderDate],
with a criteria of Month(Date()).

Create a calculated field column with data value of YrToCheck:
Year([OrderDate]),
with a criteria of Year(Date()).

These fields do not have to be visible in the Query, if you prefer to
uncheck the Show box.

Larry Linson
Microsoft Access MVP

 

[John W. Vinson]

I have a Query that lists all orders logged onto our system.
Rather than have a date parameter or a range that the user has to enter, I
would like to have a version of the Query that automatically returns just the
orders logged in the current month.
My dates are in Short Date format for the UK i.e. dd/mm/yyyy.
I have tried setting the criteria for the Order Date in the Query in various
ways but can't get it right - I'm hoping this will be a simple one for
somebody and look forward to hearing from you!
Many thanks
CW

There are several ways to do this; Larry and Fred have given you two,
I'll toss in one more - one that takes advantage of any Index on the
date field and may therefore be more efficient if your table gets big.
A criterion on the datefield of

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

will select all records where the date field falls within the current
month.

Note that the Format of the date is completely irrelevant: a Date
field is actually stored as a number, and can be formatted any way you
like without changing what's stored or how it's searched.

John W. Vinson [MVP]

 

[Guest]

Thanks Fred but what do you mean by "ReturnThese" ??
And where within the new column do I enter Return............yyyy)??
Thanks
CW

I have a Query that lists all orders logged onto our system.
Rather than have a date parameter or a range that the user has to enter, I
would like to have a version of the Query that automatically returns just the
orders logged in the current month.
My dates are in Short Date format for the UK i.e. dd/mm/yyyy.
I have tried setting the criteria for the Order Date in the Query in various
ways but can't get it right - I'm hoping this will be a simple one for
somebody and look forward to hearing from you!
Many thanks
CW

In this instance, your date format of dd/mm/yyyy is not relevant.

Add a new column to the query grid.
ReturnThese:Format([DateField],"mm/yyyy")

As criteria on this column, write:
Format(Date(),"mm/yyyy")

Only records for the current month and year will be returned.

 

[fredg]

Thanks Fred but what do you mean by "ReturnThese" ??
And where within the new column do I enter Return............yyyy)??
Thanks
CW

I have a Query that lists all orders logged onto our system.
Rather than have a date parameter or a range that the user has to enter, I
would like to have a version of the Query that automatically returns just the
orders logged in the current month.
My dates are in Short Date format for the UK i.e. dd/mm/yyyy.
I have tried setting the criteria for the Order Date in the Query in various
ways but can't get it right - I'm hoping this will be a simple one for
somebody and look forward to hearing from you!
Many thanks
CW

In this instance, your date format of dd/mm/yyyy is not relevant.

Add a new column to the query grid.
ReturnThese:Format([DateField],"mm/yyyy")

As criteria on this column, write:
Format(Date(),"mm/yyyy")

Only records for the current month and year will be returned.

In Query design view, write
ReturnThese:Format([DateField],"mm/yyyy")
on the row that says Field, to create a new column.

Change [DateField] above to whatever the actual name is of your
DateField.


On the row that says Criteria, write:
Format(Date(),"mm/yyyy")