If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Criteria in a DCount
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. |
#2
|
|||
|
|||
Multiple Criteria in a DCount
On Wed, 28 Jan 2009 20:00:04 -0800, Deb H
wrote: =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 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. |
#3
|
|||
|
|||
Multiple Criteria in a DCount
On Wed, 28 Jan 2009 20:00:04 -0800, Deb H wrote:
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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
Thread Tools | |
Display Modes | |
|
|