DateAdd in calculated field

J

John J.

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
 
A

Allen Browne

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().)
 
J

John J.

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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.

John J. said:
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?
 
A

Allen Browne

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

Reply to group, rather than allenbrowne at mvps dot org.

John J. said:
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 said:
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().)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top