Trouble using DCount within a form

[newyorkjets1721]

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]

And the name of your table is 2020???

 

[newyorkjets1721]

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]

What is the datatype of CustomerID? Is it a number or string/text?

 

[newyorkjets1721]

It's a short text field but the ID itself is always 10 numerical digits

 

[jdraw]

Then try this, and let us know what you find.
=DCount("*","tbl2020","[CustomerID]='" & [txtID]) & "'"

 

[newyorkjets1721]

Still getting a #NAME error. Whats the "'" supposed to mean? And "txtID" doesn't need to be changed?

 

[jdraw]

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.

 

[bob fitz]

Is [txtID] the name of a control (textbox?) on the form

 

[newyorkjets1721]

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]

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.