DateAdd in calculated field

In a calculated field I'm trying to show the number of previous month
requests:

=DCount("[ID_request]";"tbRequest";"month([Date_entry]) =
month(DateAdd('m';-1;Now())) AND year([Date_entry]) =
year(DateAdd('m';-1;Now()))")

Although this formula is accepted it results in an ?error.

The formula for the current month does work:

=DCount("[ID_request]";"tbRequest";"month([Date_entry]) = month(Now()) AND
year([Date_entry]) = year(Now())")

Can't I use dateadd in a calculated field or am I doing something wrong?

Thank you,
John

Perhaps something like this:
=DCount("[ID_request]", "tbRequest",
"(Date_entry = DateSerial(Year(Date()), Month(Date()) - 1, 1)) AND
(Date_entry DateSerial(Year(Date()), Month(Date()), 1))")

That should also be more efficient: JET can use an index on the Date_entry
field (which it could not do if you wrap it in a function such as Month().)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John J." wrote in message
...
In a calculated field I'm trying to show the number of previous month
requests:

=DCount("[ID_request]";"tbRequest";"month([Date_entry]) =
month(DateAdd('m';-1;Now())) AND year([Date_entry]) =
year(DateAdd('m';-1;Now()))")

Although this formula is accepted it results in an ?error.

The formula for the current month does work:

=DCount("[ID_request]";"tbRequest";"month([Date_entry]) = month(Now()) AND
year([Date_entry]) = year(Now())")

Can't I use dateadd in a calculated field or am I doing something wrong?

Great! thanks.

I did not know that in a DateSerial declaration subtracting a month from
january this year would result in december the previous year. So while the
code shows DateSerial(Year(Date()).... it shows the previous year. Cool!

"Allen Browne" schreef in bericht
...
Perhaps something like this:
=DCount("[ID_request]", "tbRequest",
"(Date_entry = DateSerial(Year(Date()), Month(Date()) - 1, 1)) AND
(Date_entry DateSerial(Year(Date()), Month(Date()), 1))")

That should also be more efficient: JET can use an index on the Date_entry
field (which it could not do if you wrap it in a function such as
Month().)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John J." wrote in message
...
In a calculated field I'm trying to show the number of previous month
requests:

=DCount("[ID_request]";"tbRequest";"month([Date_entry]) =
month(DateAdd('m';-1;Now())) AND year([Date_entry]) =
year(DateAdd('m';-1;Now()))")

Although this formula is accepted it results in an ?error.

The formula for the current month does work:

=DCount("[ID_request]";"tbRequest";"month([Date_entry]) = month(Now())
AND year([Date_entry]) = year(Now())")

Can't I use dateadd in a calculated field or am I doing something wrong?

Yes, it's quite intelligent.

Similarly, the end of of last month is:
DateSerial(Year(Date()), Month(Date()), 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John J." wrote in message
...
Great! thanks.

I did not know that in a DateSerial declaration subtracting a month from
january this year would result in december the previous year. So while the
code shows DateSerial(Year(Date()).... it shows the previous year. Cool!

"Allen Browne" schreef in bericht
...
Perhaps something like this:
=DCount("[ID_request]", "tbRequest",
"(Date_entry = DateSerial(Year(Date()), Month(Date()) - 1, 1)) AND
(Date_entry DateSerial(Year(Date()), Month(Date()), 1))")

That should also be more efficient: JET can use an index on the
Date_entry field (which it could not do if you wrap it in a function such
as Month().)