DCount Mixed With Format and Now Functions Causing Syntax Error

[VariableZ]

I'm using MS Access 2007.


I have fields in query "qryLotData" named "FormattedDate" (derived via CDate([datefield])) and "Dateweek" (derived from Format([FormattedDate],"ww")).
I'm trying to use the DCount function in a form to count the number of entries week-to-date. My formula in the Control Source field is as follows:

=DCount("[FormattedDate]","qryLotData","[DateWeek]=Format(Now(),"ww")")

I keep getting the error message "The expression you entered contains invalid syntax. You may have entered an operand without an operator." I'm pretty sure that's not the case, and Microsoft's database tells me I should be able to do this, but I can't seem to figure out what's wrong. Any suggestions?

[pbaldy]

Either switch the quotes around ww to single quotes or:

=DCount("[FormattedDate]","qryLotData","[DateWeek]=" & Format(Now(),"ww"))

[VariableZ]

...switch the quotes around ww to single quotes...

I changed the quotes to single quotes and it worked. Do you know why it's necessary sometimes to use single quotes instead of double quotes?

[pbaldy]

Sure; with this:

"[DateWeek]=Format(Now(),"ww")"

You started a string before DateWeek and intended to end it after the closing parentheses. You know by looking at it that the quotes around ww are just for the Format() function. Access isn't that smart. When you start a string with double quotes, it assumes the next set of double quotes ends that string. In this case, it was the quotes at the start of ww. That left everything after that more or less out in the middle of nowhere. It gets confused and throws an error. Using single quotes around the ww tells it that what follows is something different and not to close off the main string yet.

Clear as mud now?

[VariableZ]

I suppose it does make sense. Thank you for the explanation. I'll be keeping that in mind from now on every time I make a formula.

[pbaldy]

No problemo.