Solved Find First and Data Type Mismatch

[Dumferling]

I am getting a data type mismatch in a Find First. The line of code is:
rst.FindFirst ("[FSPNumber]=" & vblFSPA)

I have checked that the field (FSPNumber) is a text field and the variable vblFSPA is declared as a string and the data it stores is from a text field. I am sure I am doing something really basic wrong here. I can get the code to work if I fill the string manually (vblFSPA = "3030") but the moment I fill it with information taken from a Recordset, it throws up a Data type mismatch. Everything else works, just this one line is killing me.

Any suggestions on what I am doing wrong?

 

[Josef P.]

General troubleshooting tip: Use variables to cache values, then you can check them easier.

Dim CriteriaSqlString as String
CriteriaSqlString = "[FSPNumber]=" & vblFSPA
Debug.Print CriteriaSqlString
Stop ' check text in immediate window ... you will see missing ''
rst.FindFirst CriteriaSqlString

=>

CriteriaSqlString = "[FSPNumber]='" & vblFSPA & "'"

Or with SQL Injection prevention:

CriteriaSqlString = "[FSPNumber]='" & Replace(vblFSPA, "'", "''") & "'"

Or with BuildCriteria:

CriteriaSqlString = BuildCriteria("[FSPNumber]", dbText, vblFSPA)

Or ..

 

[Gasman]

If it is a string you need to surround with single quotes or triple double quotes (I think) ìf the string could contain a singĺe quote.
Dates need # and numbers need nothing.

 

[Dumferling]

OK. solved it - It needed more quotes. Still can't understand why exactly - the single and double quotes thing is very confusing but it works:
rst.FindFirst ("[FSPNumber]='" & vblFSPA & "'")
Thanks for the help

 

[Minty]

It's simple really, you are searching for a string so it needs delimiting - that is you need to tell Access where it starts and finishes.

So you need to pass the string as 'ABC12345'

The single quotes are used as you are already using the double ones to create the criteria string, or (and this is where it gets confusing) you can use doubled-up quotes e.g.

""" & YourVariable & """

or you can use a Chr equivalent

Chr(34) & YourVariable & Chr(34)

I personally prefer the single quote, but there are situations where that can fail, e.g. a string containing a quote character like O'Murphy will break things. That's when using the Chr() method works more reliably.

 

[Josef P.]

I personally prefer the single quote, but there are situations where that can fail, e.g. a string containing a quote character like O'Murphy will break things. That's when using the Chr() method works more reliably.

That's why I always recommend doubling. Also with " it can come to an error, if a user writes a " in the search text.

In my applications, it would look like this:

CriteriaSqlString = "[FSPNumber]=" & SqlTools.TextToSqlText(vblFSPA)
' or
CriteriaSqlString = SqlTools.BuildCriteria("FSPNumber", SQL_Text, SQL_Equal, vblFSPA)

... to always write Replace(..) would be too much work for me.

 

[Mike Krailo]

SqlTools

I wouldn't mind looking at your code module for this. It looks like a good idea to me. Currently, I have a bunch of AHK Hotstrings that I use for this. While it speeds things up a little, your solution seems to be a little more refined and I would just update my hotstrings to just include the module SqlTools instead. Plus, that's a great name for the module.

 

[MajP]

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

My Csql

 

[Mike Krailo]

I forgot about that thread MajP. You just forced me to read through the whole thing again.

 

[Josef P.]

I wouldn't mind looking at your code module for this. It looks like a good idea to me.

SqlTools class: https://github.com/AccessCodeLib/AccessCodeLib/blob/master/data/SqlTools.cls
I use VB_PredeclaredId = True for the class so that no instance variable has to be used.

 

[The_Doc_Man]

The thing about string-passage is simple. IF you are passing a variable, normally it works just fine. BUT if you are passing a literal string, the VBA argument-passing mechanism strips the outermost layer of quotes in the explicit or (in your case) implied call. So you need to add another layer of quotes to delimit the literal string after its passing in order to keep it from being mistaken for a variable or field of that name.