Criteria for current month, current year

[Guest]

We started using our database last September(2006).
We have an Inquiry Date for each of our jobs, which is stored in UK format
as (for example) 07/09/2007.
I have a query that displays details of inquiries taken in the current
month, so in the Inquiry Date column I have the criteria set to Month(Now()).
This has worked fine up until now, but now that we have got into the second
year with the system it is picking up inquiries from last September well as
the current ones, so I need to filter those out.
Could somebody advise how I should modify the criteria, please?
Many thanks
CW

 

[Ken Snell \(MVP\)]

There are at least two different ways to do this:

SELECT * FROM TableName
WHERE [Inquiry Date] Between DateSerial(Year(Now()), Month(Now()), 1)
AND DateSerial(Year(Now()), Month(Now()) + 1, 0);

or

SELECT * FROM TableName
WHERE Format([Inquiry Date],"yyyymm") =
Format(Now(), "yyyymm");

 

[Marshall Barton]

We started using our database last September(2006).
We have an Inquiry Date for each of our jobs, which is stored in UK format
as (for example) 07/09/2007.
I have a query that displays details of inquiries taken in the current
month, so in the Inquiry Date column I have the criteria set to Month(Now()).
This has worked fine up until now, but now that we have got into the second
year with the system it is picking up inquiries from last September well as
the current ones, so I need to filter those out.
Could somebody advise how I should modify the criteria, please?

Unless your date field is a Text field, date/time values are
stored using a Double type value and have nothing to do with
your format settings.

You can add another criteria to check the month and year
Yr: Year([Inquiry Date])
= Year(Date())
Mo: Month([Inquiry Date])
= Month(Date())

Or you can format the date to a comparable value:
YrMo: Format([Inquiry Date], "yyyymm")
= Format(Date(), "yyyymm")

 

[Guest]

Thanks Ken, I pasted the first one into my query but I get an error message:
"Syntax error, missing operator in expression"
Any thoughts as to what is needed?
Thanks again
CW

There are at least two different ways to do this:

SELECT * FROM TableName
WHERE [Inquiry Date] Between DateSerial(Year(Now()), Month(Now()), 1)
AND DateSerial(Year(Now()), Month(Now()) + 1, 0);

or

SELECT * FROM TableName
WHERE Format([Inquiry Date],"yyyymm") =
Format(Now(), "yyyymm");


--

Ken Snell
<MS ACCESS MVP>

We started using our database last September(2006).
We have an Inquiry Date for each of our jobs, which is stored in UK format
as (for example) 07/09/2007.
I have a query that displays details of inquiries taken in the current
month, so in the Inquiry Date column I have the criteria set to
Month(Now()).
This has worked fine up until now, but now that we have got into the
second
year with the system it is picking up inquiries from last September well
as
the current ones, so I need to filter those out.
Could somebody advise how I should modify the criteria, please?
Many thanks
CW

 

[Ken Snell \(MVP\)]

Did you change TableName to the actual name of the table? Post the actual
SQL that you tried and that gave you the syntax error.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken, I pasted the first one into my query but I get an error
message:
"Syntax error, missing operator in expression"
Any thoughts as to what is needed?
Thanks again
CW

There are at least two different ways to do this:

SELECT * FROM TableName
WHERE [Inquiry Date] Between DateSerial(Year(Now()), Month(Now()), 1)
AND DateSerial(Year(Now()), Month(Now()) + 1, 0);

or

SELECT * FROM TableName
WHERE Format([Inquiry Date],"yyyymm") =
Format(Now(), "yyyymm");


--

Ken Snell
<MS ACCESS MVP>

We started using our database last September(2006).
We have an Inquiry Date for each of our jobs, which is stored in UK
format
as (for example) 07/09/2007.
I have a query that displays details of inquiries taken in the current
month, so in the Inquiry Date column I have the criteria set to
Month(Now()).
This has worked fine up until now, but now that we have got into the
second
year with the system it is picking up inquiries from last September
well
as
the current ones, so I need to filter those out.
Could somebody advise how I should modify the criteria, please?
Many thanks
CW

 

[Ken Snell \(MVP\)]

If you're using the design view (grid) for the query, use this as the
Criterion expression for the Inquiry Date field:

Between DateSerial(Year(Now()), Month(Now()), 1) AND DateSerial(Year(Now()),
Month(Now()) + 1, 0)

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken, I pasted the first one into my query but I get an error
message:
"Syntax error, missing operator in expression"
Any thoughts as to what is needed?
Thanks again
CW

There are at least two different ways to do this:

SELECT * FROM TableName
WHERE [Inquiry Date] Between DateSerial(Year(Now()), Month(Now()), 1)
AND DateSerial(Year(Now()), Month(Now()) + 1, 0);

or

SELECT * FROM TableName
WHERE Format([Inquiry Date],"yyyymm") =
Format(Now(), "yyyymm");


--

Ken Snell
<MS ACCESS MVP>

We started using our database last September(2006).
We have an Inquiry Date for each of our jobs, which is stored in UK
format
as (for example) 07/09/2007.
I have a query that displays details of inquiries taken in the current
month, so in the Inquiry Date column I have the criteria set to
Month(Now()).
This has worked fine up until now, but now that we have got into the
second
year with the system it is picking up inquiries from last September
well
as
the current ones, so I need to filter those out.
Could somebody advise how I should modify the criteria, please?
Many thanks
CW

 

[Guest]

OK, that's got it, working nicely now!
Thanks a lot, Ken.

If you're using the design view (grid) for the query, use this as the
Criterion expression for the Inquiry Date field:

Between DateSerial(Year(Now()), Month(Now()), 1) AND DateSerial(Year(Now()),
Month(Now()) + 1, 0)

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken, I pasted the first one into my query but I get an error
message:
"Syntax error, missing operator in expression"
Any thoughts as to what is needed?
Thanks again
CW

There are at least two different ways to do this:

SELECT * FROM TableName
WHERE [Inquiry Date] Between DateSerial(Year(Now()), Month(Now()), 1)
AND DateSerial(Year(Now()), Month(Now()) + 1, 0);

or

SELECT * FROM TableName
WHERE Format([Inquiry Date],"yyyymm") =
Format(Now(), "yyyymm");


--

Ken Snell
<MS ACCESS MVP>



We started using our database last September(2006).
We have an Inquiry Date for each of our jobs, which is stored in UK
format
as (for example) 07/09/2007.
I have a query that displays details of inquiries taken in the current
month, so in the Inquiry Date column I have the criteria set to
Month(Now()).
This has worked fine up until now, but now that we have got into the
second
year with the system it is picking up inquiries from last September
well
as
the current ones, so I need to filter those out.
Could somebody advise how I should modify the criteria, please?
Many thanks
CW