Solved DCount Runtime Error '13' Type Mismatch

[MattBaldry]

Hi all,

I thought I had this fixed, but I was wrong. I have the below code giving me an error.

If DCount("*", "tblFileRegisterDetail", ("FileRegisterTypeID = " & FileRegisterTypeID And "FileRegisterDetailNumber = '" & FileRegisterDetailNumber & "'")) > 0 Then

FileRegisterTypeID is Numerical
FileRegisterDetailNumber is Short Text

If I hover on the debug, it is showing me the correct values. If I use any of the criteria alone, it works. once I try to join them it fails.

Could someone be so kind as to point out my obvious mistake please?

~Matt

 

[MajP]

The 3rd argument should be a string, but you have parentheses outside of the quotes. I am surprised that compiles.

("FileRegisterTypeID = " & FileRegisterTypeID And "FileRegisterDetailNumber = '" & FileRegisterDetailNumber & "'")

Should likely be

 "FileRegisterTypeID = " & FileRegisterTypeID & " And FileRegisterDetailNumber = '" & FileRegisterDetailNumber & "'"

You can do yourself a favor

dim Criteria as string
Criteria = "FileRegisterTypeID = " & FileRegisterTypeID & " And FileRegisterDetailNumber = '" & FileRegisterDetailNumber & "'"
debug.print Criteria ' verify your work
If DCount("*", "tblFileRegisterDetail",Criteria) > 0 then

 

[MattBaldry]

The 3rd argument should be a string, but you have parentheses outside of the quotes. I am surprised that compiles.


Should likely be

 "FileRegisterTypeID = " & FileRegisterTypeID & " And FileRegisterDetailNumber = '" & FileRegisterDetailNumber & "'"

You can do yourself a favor

dim Criteria as string
Criteria = "FileRegisterTypeID = " & FileRegisterTypeID & " And FileRegisterDetailNumber = '" & FileRegisterDetailNumber & "'"
debug.print Criteria ' verify your work
If DCount("*", "tblFileRegisterDetail",Criteria) > 0 then

Thanks MajP,

This works fine. I shall use the Dim strFileCriteria As String method too.

~Matt

 

[MajP]

I am in the habit for years now to do this whenever resolving a SQL string. Especially any thing with delimiters like ', or # or concatenation.
Most of the times when I skip doing it, it fails and I have to go back and do it anyways.

Dim StrSql as string
strSql = "Select * ..... where .... Order by ...."
debug.print "Verify Sql: " & strSql
set RS = currentdb.openrecordset(strSql)

 

[Gasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

 

[The_Doc_Man]

Just for the record, the parentheses around an argument can change that argument from "String" to "Expression" via the VBA parsing action known as "Type Coercion." This is the same mechanism that allows you to add (integer) 1 to a SINGLE or DOUBLE variable or field, so it is usually beneficial. You also see that problem sometimes when you have a parenthetical argument on an in-line subroutine invocation.

 

[cheekybuddha]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Or just double up the single quotes within the string.

 

[MajP]

Or better yet use CSQL and never worry about delimiters and formatting

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk