Using Format and DateAdd

  • Thread starter Thread starter Tim Long
  • Start date Start date
T

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
 
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, many thanks, but the single quotes don't work either. Any ideas? It's
quite baffling!

Duane Hookom said:
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


Tim Long said:
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
Click to expand...
Click to expand...
 
Back
Top