Using Format and DateAdd

[Tim Long]

Hello, can't understand what I'm doing wrong here.. I'm getting a 'The
expression you entered contains invalid syntax' message.

=DCount("[Client_ID]","tblClients","Format([d_received],"mmyyyy") = #" &
Format(DateAdd("mm",-11,[Forms]![frmReports].[txtYourEndDate]),"mmyyyy") & "#
")

I'm trying to get the total for each of twelve months into text boxes. The
above code, I hoped, would give me the total number of clients received in
the first month of a twelve month period. The text box giving the month
heading (=Format(DateAdd("m",-11,Forms!frmReports.txtYourEndDate),"mmm")
works fine.. Can anyone tell me what I am doing wrong?

Many thanks in advance

 

[Duane Hookom]

I think you only need to change one set of double quotes to single quotes:

=DCount("[Client_ID]","tblClients","Format([d_received],'mmyyyy') = #" &
Format(DateAdd("mm",-11,[Forms]![frmReports].[txtYourEndDate]),"mmyyyy") &
"# ")

 

[Tim Long]

Duane, many thanks, but the single quotes don't work either. Any ideas? It's
quite baffling!

I think you only need to change one set of double quotes to single quotes:

=DCount("[Client_ID]","tblClients","Format([d_received],'mmyyyy') = #" &
Format(DateAdd("mm",-11,[Forms]![frmReports].[txtYourEndDate]),"mmyyyy") &
"# ")

--
Duane Hookom
Microsoft Access MVP

Hello, can't understand what I'm doing wrong here.. I'm getting a 'The
expression you entered contains invalid syntax' message.

=DCount("[Client_ID]","tblClients","Format([d_received],"mmyyyy") = #" &
Format(DateAdd("mm",-11,[Forms]![frmReports].[txtYourEndDate]),"mmyyyy") & "#
")

I'm trying to get the total for each of twelve months into text boxes. The
above code, I hoped, would give me the total number of clients received in
the first month of a twelve month period. The text box giving the month
heading (=Format(DateAdd("m",-11,Forms!frmReports.txtYourEndDate),"mmm")
works fine.. Can anyone tell me what I am doing wrong?

Many thanks in advance