Where condition Date Problem (1 Viewer)

[Adam Caramon]

Hi all,

I'm sure this is just a syntax problem, but I cannot figure it out for the life of me. I'm working on a bound form and I'm trying to set the where condition of a DSum to Month(ReviewDate) = Month(me.txtReviewDate), but having trouble. I have tried several different ways.

As an overview, I am trying to sum all of the records from the recordset where the month is the same. So if I am on a July record, for example, I want to see a sum of all July records only, no other months. When I navigate to an August record, then the DSum should provide the sum of only August records, and so forth.

This works:

Me.txtRecordsforCurrentIndividualforCurrentMonth = DSum("NumberofPresentations", "tblCCMECPresentation", "ReviewDate = #8/1/2013#")

But as soon as I try to replace the actual number (#8/1/2013#) with a variable, I run into trouble.

Any help is appreciated.

 

[plog]

You need to escape it with single quotes:

Me.txtRecordsforCurrentIndividualforCurrentMonth = DSum("NumberofPresentations", "tblCCMECPresentation", "ReviewDate = #' & [YourVariableHere] &"'#")

 

[Adam Caramon]

Thanks for the reply. Still getting an error message. Here's my code:

    Me.txtRecordsforCurrentIndividualforCurrentMonth = DSum("NumberofPresentations", "tblCCMECPresentation", "ReviewDate = #'" & Me.txtReviewDate & "'#")

The error message is:

Run-time error '3075'

Syntax error in date in query expression 'ReviewDate = #'7/22/2013".

 

[pbaldy]

No single quotes for a date:

http://www.mvps.org/access/general/gen0018.htm

 

[Adam Caramon]

Perfect. Thanks for the help.

 

[pbaldy]

No problem.