Multiple Criteria in a DCount

[Deb H]

I have an unbound text box on a form in which I want to show the number of
visits in 2009 for the client number displayed in the form. I can't seem to
get the syntax right. Here is my control source:

=DCount("[VisitNumber]","[tblClient Visits]","[Client Nb]=" &
[Forms]![frmVolClientName]![Client Nb] And [Date of Visit]>#12/31/2008#")

Thanks for your help.

 

[Tom van Stiphout]

On Wed, 28 Jan 2009 20:00:04 -0800, Deb H

=DCount("[VisitNumber]","[tblClient Visits]","[Client Nb]=" &
[Forms]![frmVolClientName]![Client Nb] & " And [Date of
Visit]>#12/31/2008#")
DCount takes three strings. The third one is a concatenation of
several elements.

-Tom.
Microsoft Access MVP

 

[fredg]

I have an unbound text box on a form in which I want to show the number of
visits in 2009 for the client number displayed in the form. I can't seem to
get the syntax right. Here is my control source:

=DCount("[VisitNumber]","[tblClient Visits]","[Client Nb]=" &
[Forms]![frmVolClientName]![Client Nb] And [Date of Visit]>#12/31/2008#")

Thanks for your help.

You've made a couple of mistakes
1) The word AND is outside of the " after [Cliend Nb] = .
2) You are counting 'All' records that have the wanted criteria, not a
particular field in the record.
3) The actual syntax depends upon the datatype of the criteria field.
If [Client Nb] is a number datatype, then

=DCount("*","[tblClient Visits]","[Client Nb]=" & [Client Nb] & "
And [Date of Visit]>= #1/1/2009#")

4) If [Client Nb] is a Text datatype, then use:

=DCount("*","[tblClient Visits]","[Client Nb]='" & [Client Nb] & "'
And [Date of Visit] >= #1/1/2009#")

Using your criteria, you will need to edit the syntax each January.
Would it not be better to have the code automatically update itself
each year?
Assuming [Client Nb] is a Number datatype:

=DCount("*","[tblClient Visits]","[Client Nb] =" & [Client Nb] & " And
[Date of Visit] >= DateSerial(Year(Date()),1,1)")

You will always get the current year's count.