Trouble using DCount within a form (1 Viewer)

newyorkjets1721

New member
Local time
Today, 14:09
Joined
Jan 28, 2020
Messages
9
I'm trying to use a DCount expression within a text field in a Form to populate how many entries a CustomerID has in a table. So if I have an entry pulled up in a form, there's a field that basically says "in addition to this entry, there's other 4 customer complaints associated with this CustomerID in the table"

I've been using the following expression but it's just returning a #NAME error:

=DCount("*","2020","[CustomerID]=" & [txtID])

Any tips?
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:09
Joined
Jan 23, 2006
Messages
15,364
And the name of your table is 2020???
 

newyorkjets1721

New member
Local time
Today, 14:09
Joined
Jan 28, 2020
Messages
9
Yes that's the table. I typed in wrong though, my apologies. Below is the exact expression copied and pasted:

=DCount("*","tbl2020","[CustomerID]=" & [txtID])

"CustomerID" is the field that I'm using to match from the form and the table.
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:09
Joined
Jan 23, 2006
Messages
15,364
What is the datatype of CustomerID? Is it a number or string/text?
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:09
Joined
Jan 23, 2006
Messages
15,364
Then try this, and let us know what you find.
=DCount("*","tbl2020","[CustomerID]='" & [txtID]) & "'"
 

newyorkjets1721

New member
Local time
Today, 14:09
Joined
Jan 28, 2020
Messages
9
Still getting a #NAME error. Whats the "'" supposed to mean? And "txtID" doesn't need to be changed?
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:09
Joined
Jan 23, 2006
Messages
15,364
They are single quotes. I highlighted them.

=DCount("*","tbl2020","[CustomerID]='" & [txtID]) & "'"
The expression is saying
Return the Count of all records in tbl2020 where the CustomerID = the value in txtID
The single quotes are used to surround a string/text datatype. You did say CustomerID was short text.
 
Last edited:

bob fitz

AWF VIP
Local time
Today, 18:09
Joined
May 23, 2011
Messages
4,717
Is [txtID] the name of a control (textbox?) on the form
 

newyorkjets1721

New member
Local time
Today, 14:09
Joined
Jan 28, 2020
Messages
9
So what should the "txtID" be ? I'm confused about that part. Th CustomerID is listed on the form for each entry. Basically I want the expression to take that CustomerID and return the count for all records in tbl2020 that match it. So should't the "txtID" pull whatever CustomerID is listed on the form?
 

bob fitz

AWF VIP
Local time
Today, 18:09
Joined
May 23, 2011
Messages
4,717
So what should the "txtID" be ? I'm confused about that part. Th CustomerID is listed on the form for each entry. Basically I want the expression to take that CustomerID and return the count for all records in tbl2020 that match it. So should't the "txtID" pull whatever CustomerID is listed on the form?
If you use the name of the control (probably a textbox) on your form that is bound to the CustomerID it will work.
I think you can also use the name of the actual field in the forms RecordSource. From what I've read so far I would guess that that would be CustomerID.
 

Users who are viewing this thread

Top Bottom