Problem with DateAdd function

[Guest]

I am trying to create a calculated control on a form using the DSum function.
The problem I am running into is when I try to limit the domain using the
criteria section of the DSum function. I have something like this:

=DSum("[Transaction]","tblUsage","[PaperType] = '35in 30/32lb'")

So far, everything is fine. But I would like to further restrict the
criteria to transactions that took place between certain dates and that is
where I think I am hitting the snag. It seems to me that I could use this:

=DSum("[Transaction]","tblUsage","[PaperType] = '35in 30/32lb' AND
[dtDate] Between Date() and DateAdd("d",-7,Date())")

That is, I want all transactions that involve 35in paper and occured between
today and a week ago. But I must have done something wrong because Access
doesn't like that statement. Did I make a typo or do I need some additional
punctuation in there?

Any help would be appreciated.

Steven Sutton

 

[Douglas J. Steele]

\Try:

=DSum("[Transaction]","tblUsage","[PaperType] = '35in 30/32lb' AND
[dtDate] Between " & Date() & " and " & DateAdd("d",-7,Date()))

If that doesn't work, go to:

=DSum("[Transaction]","tblUsage","[PaperType] = '35in 30/32lb' AND
[dtDate] Between " & Format(Date(), "\#mm\/dd\/yyyy\#") " & " and " &
Format(DateAdd("d",-7,Date()), "\#mm\/dd\/yyyy\#"))

 

[John Vinson]

That is, I want all transactions that involve 35in paper and occured between
today and a week ago. But I must have done something wrong because Access
doesn't like that statement. Did I make a typo or do I need some additional
punctuation in there?

The problem is that the doublequotes around the "d" in the DateAdd are
being interpreted as the end of the quoted string. Try concatenating
the values of the date functions rather than just the name, using #
delimiters to identify the fields as being of date/time type:

DSum("[Transaction]","tblUsage","[PaperType] = '35in 30/32lb' AND
[dtDate] Between #" & Date() & "# and #" & DateAdd("d",-7,Date()) &
"#")


John W. Vinson[MVP]