how do i fix type mismatch error with Dcount function?

[martinr]

I ahve declared custCount as an integer and id as a string that comes from the user form (id = me.CustID) and trying to count the number of matching records in the recordset (rst) using the following:
custCount = DCount("[customerId]", rst, "[customerId] like '" & id & "'")

I'm getting a type mismatch error on the DCount statement???
i believe the syntax is correct is something else wrong?

 

[WayneRyan]

martin,

The middle argument for the DCount function is the name of the Query or Table to be searched.

I don't think a recordset is fair game.

Wayne

 

[Mihail]

Why you use "LIKE" ?
I understand that you have a certain ID.

Try this:
custCount = DCount("customerId", rst, "customerId = '" & me.CustID & "'")

 

[Galaxiom]

The arguments of DCount are strings. Suppling an object variable will cause the mismatch.

Moreover the string must be the name of a table or query.

Assuming it is a DAO recordset use:

With rs
   .Filter = "customerId = '" & Me.CustID & "'"
   .MoveLast
   custCount =  .RecordCount
End With

http://msdn.microsoft.com/en-us/library/office/ff821452.aspx
http://msdn.microsoft.com/en-us/library/office/ff837300.aspx

Include a MoveFirst if you need to get back to the first record.

Edit: The MoveLast might not be required since the Filter has been applied and hence the whole recordset presumably loaded. I have not tried it.