FORM CODING EXPRESSIONS

[Thorfinn]

Can anyone help with the following:

These are part of a daily calory tracking application.

The form codes below work OK on MS-Access 2003, and the literature (help
files, etc.) suggest that they should work with MS-Access 2007.

=DSum("[IN]","CALORIES","DATE=Date()")
Intended to add intake calories for a specific day.

=DSum("[OUT]","CALORIES","DATE=Date()")
Intended to add output calories for a specific day.

=DSum("[IN]","CALORIES"," DATE Between DATE() And Date()-7")
Intended to add intake calories for the last 7 days

=DSum("[OUT]","CALORIES"," DATE Between DATE() And Date()-7")
Intended to add ouput calories for the last 7 days

=DSum("[IN]","CALORIES"," DATE Between DATE() And Date()-365")
Intended to add intake calories for the last 365 days

=DSum("[OUT]","CALORIES"," DATE Between DATE() And Date()-365")
Intended to add output calories for the last 365 days

The rest of the application, including other expressions, works OK. For
some reason MS-Access 2007 seems to want something different.

Any help appreciated; Thanks

 

[Thorfinn]

AccessVandal via AccessMonster.com:

Thanks for your response. Unfortunately, neither suggestion worked. I
tried adding the brackets first. Then I made a copy of the database and
changed the table name, and subsequent coding references. That didn't work
either.

Also -- here are some of the other "older codes" that DO still work:

=DSum("[IN]","CALORIES","DATE Between #01/01/2009# And #01/31/2009#")
=DSum("[OUT]","CALORIES","DATE Between #01/01/2009# And #01/31/2009#")
=DAvg("[WK_WT]","CALORIES","[WK_WT] <> 0 And DATE Between #01/01/2009# And
#01/31/2009#")
etc ...

Note that they also do not have the dates in brackets. I also tried putting
DATE() in brackets; I tried brackets with double quotes (""); quotes without
brackets. Nothing works (so far).

The only obvious thing left is to do each week of the year the same way I
did the months (shown above). That doesn't really give me what I want
either.

SOooo -- any other suggestions? I have left the brackets around the [DATE]
field references for those that don't work; but otherwise, all is the same.

Thanks again, in advance.
--
Thorfinn

First, DATE is a reserve word in Access. If it's possible to rename this
field/column in your table, do so. If you can't, use the square bracket like
"[DATE] = Date()")

A2007 is more picky.

Can anyone help with the following:

These are part of a daily calory tracking application.

The form codes below work OK on MS-Access 2003, and the literature (help
files, etc.) suggest that they should work with MS-Access 2007.

=DSum("[IN]","CALORIES","DATE=Date()")
Intended to add intake calories for a specific day.

=DSum("[OUT]","CALORIES","DATE=Date()")
Intended to add output calories for a specific day.

=DSum("[IN]","CALORIES"," DATE Between DATE() And Date()-7")
Intended to add intake calories for the last 7 days

=DSum("[OUT]","CALORIES"," DATE Between DATE() And Date()-7")
Intended to add ouput calories for the last 7 days

=DSum("[IN]","CALORIES"," DATE Between DATE() And Date()-365")
Intended to add intake calories for the last 365 days

=DSum("[OUT]","CALORIES"," DATE Between DATE() And Date()-365")
Intended to add output calories for the last 365 days

The rest of the application, including other expressions, works OK. For
some reason MS-Access 2007 seems to want something different.

Any help appreciated; Thanks