DCount Criteria Problem (1 Viewer)

[DSimpson]

I'm trying to use the DCount function to count the # of records on another form when the "Location" field matches the value of a field on the current form. The expression I'm using is: =DCount("[Location]","Parts","[Location] = '20' ")
this works when Loaction=20 but, I would like to replace 20 with the value of a text box on my current form. Any ideas?

 

[MarionD]

Hi there,
DCount("[Location]","Parts","[Location] = " & me.textboxname)if the field is numeric or

DCount("[Location]","Parts","[Location] = " & "'" & me.textboxname & "'") if it's a string




[This message has been edited by MarionD (edited 04-26-2001).]

 

[DSimpson]

It's a string and when I tried the appropriate expression I got the following error:

object doesn't contain the automation object "me."

 

[KevinM]

Try...

DCount("[Location]","Parts","[Location] = " & "'" & [Forms]![CurrentFormName]![textboxname] & "'")

HTH

 

[DSimpson]

I tried this and got no errors, but it didn't pull any records (i.e. the output text box in the current form always shows 0 records no matter what value is given to the text box I'm referencing in the dcount criteria statement).

 

[Rich]

Your original post says that you are trying to count the number of records on another form and not the underlying table is that correct?

 

[DSimpson]

That is right. I have since made a change to make the location field numeric and now it is working. But, now I would like to add a second criteria that only counts the records from that location if the RETURNED field on that form is NOT checked. I am using the following expression and it is not working.

=DCount("[Location]","Parts","[Location]= " & [Location] AND [Returned]=No")