CODEWITHSUNDEEP
sqlvbams-access
MJH
MJH

Reputation: 859

dcount function is showing error datatype mismatch

When running the code I get the following error:

Data type mismatch in criteria expression.

I am trying to check if record exists in table tblMain with an autoIncrement field Id. 

Dim myId as Long
Dim tableName as string

myId = 1145589
tableName = "Main"

Note: The above part is just to show what these variable types and worth are. they are not actually presented like this in my code.

If Not DCount("Id", "tbl" & tableName, "Id='" & myId & "'") = 1 Then
    Err.Raise 540, "This record does not exist."
End If

I tried running the following query, and it was fine:

SELECT Count(Id) FROM tblMain WHERE Id = 1145589

What is wrong with the code?

Upvotes: 0

Views: 875

Answers (2)

Ageonix
Ageonix

Reputation: 1808

You have single ticks around your myId variable, but it's numeric so you don't need them.

"Id='" & myId & "'"

should be 

"Id=" & myId

Here is the complete updated code:

If Not DCount("Id", "tbl" & tableName, "Id=" & myId) = 1 Then
    Err.Raise 540, "This record does not exist."
End If

Upvotes: 2

Gustav
Gustav

Reputation: 55806

A Long shall not be quoted:

If Not DCount("*", "tbl" & tableName, "Id=" & myId & "") = 1 Then
    Err.Raise 540, "This record does not exist."
End If

Upvotes: 2

Related Questions