Data Type Mismatch in SQL

[EddyLLC]

Hi All, I call the LoadRecord sub in the AfterUpdate event of a combobox. The code blows in the Set rs = line telling me Data Type mismatch in criteria expression. The data type is text throughout, even the field FileRef in tbl1 is text.

What am I missing? Thanks!

'Calls the LoadRecord in cboFind AfterUpdate event
Dim strRef As String
strRef = cboFind.Value
frm1.Show
frm1.LoadRecord strRef


Sub LoadRecord(strFile As String)
strSQL = "SELECT * FROM tbl1"
strSQL = strSQL & " WHERE tbl1.FileRef = " & strFile

Set rs = Conn.Execute(strSQL)

 

[Golom]

Assuming that the data type of tbl1.FileRef is text, you need single quotes around the value

Sub LoadRecord(strFile As String)
    strSQL = "SELECT * FROM tbl1"
    strSQL = strSQL & " WHERE tbl1.FileRef = [red]'[/red]" & strFile [red]& "'"[/red]

 

[Andrzejek]

or

Sub LoadRecord(strFile As String)[blue]
strFile = "'" & strFile & "'"[/blue]
    strSQL = "SELECT * FROM tbl1"
    strSQL = strSQL & " WHERE tbl1.FileRef = " & strFile

which is the same as Golom's

Have fun.

---- Andy

 

[Golom]

Andrzejek
Just because I'm paranoid

Sub LoadRecord([red]ByVal[/red] strFile As String)
strFile = "'" & strFile & "'"

 

[EddyLLC]

That was it, of course. Thanks yet again for the help!

Dave

 

[gmmastros]

Now try a value for strFile that contains a single quote and see it fail again.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

Hey George, you are a mind reader, but don't read my mind from 4 to 7, that's my 'private' time

Sub LoadRecord(strFile As String)
strFile = "'" & [blue]Replace(strFile, "'", "''")[/blue] & "'"
    strSQL = "SELECT * FROM tbl1"
    strSQL = strSQL & " WHERE tbl1.FileRef = " & strFile

Have fun.

---- Andy