DataType Mismatch Error

[Pesteo]

I am working on an access database, writing a dynamic search form.

Code that builds that dynamic search query...
*.........................................................*
Private Sub cmdSearch_Click()

Dim db As Database
Dim rst As Recordset

gstrWhereRec = ""

If IsNothing(Me!searchField) Then
MsgBox "No criteria specified.", vbExclamation
Forms!Search_Form!searchField.SetFocus
Exit Sub
ElseIf IsNothing(Me!searchTerm) Then
MsgBox "Please enter a search term.", vbExclamation
Forms!Search_Form!searchTerm.SetFocus
Exit Sub
ElseIf Me!searchField = "XRD Sample Name:" Then
gstrWhereRec = "XRD_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Field Sample Name:" Then
gstrWhereRec = "Field_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Maximum CRS:" Then
gstrWhereRec = "Max_CRS LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Dominant Mineral(s):" Then
gstrWhereRec = "Mineral_Dominant LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Moderate Mineral(s):" Then
gstrWhereRec = "Mineral_Moderate LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Trace Mineral(s):" Then
gstrWhereRec = "Mineral_Trace LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Mineral(s):" Then
gstrWhereRec = "Mineral_Possible LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Low-Angle Mineral(s):" Then
gstrWhereRec = "Mineral_Possible_Low_Angle LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Computer Search Match Possibilities:" Then
gstrWhereRec = "Mineral_Match_Possibilities LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
End If

If Me!searchType = 2 And Not IsNothing(Me!searchTerm2) Then
If Me!SearchField2 = "XRD Sample Name:" Then
gstrWhereRec = gstrWhereRec & " AND XRD_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Field Sample Name:" Then
gstrWhereRec = gstrWhereRec & " AND Field_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Maximum CRS:" Then
gstrWhereRec = gstrWhereRec & " AND Max_CRS LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Dominant Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Dominant LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Moderate Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Moderate LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Trace Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Trace LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Possible LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Low-Angle Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Possible_Low_Angle LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Computer Search Match Possibilities:" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Match_Possibilities LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
End If

ElseIf Me!searchType = 3 And Not IsNothing(Me!searchTerm2) Then
If Me!SearchField2 = "XRD Sample Name:" Then
gstrWhereRec = gstrWhereRec & " OR XRD_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Field Sample Name:" Then
gstrWhereRec = gstrWhereRec & " OR Field_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Maximum CRS:" Then
gstrWhereRec = gstrWhereRec & " OR Max_CRS LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Dominant Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Dominant LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Moderate Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Moderate LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Trace Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Trace LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Possible LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Low-Angle Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Possible_Low_Angle LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Computer Search Match Possibilities:" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Match_Possibilities LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
End If
ElseIf Not IsNothing(Me!SearchField2) And IsNothing(Me!searchTerm2) Then
MsgBox "Please enter a search term.", vbExclamation
Forms!Search_Form!searchTerm2.SetFocus
Exit Sub
End If

'Turn on Hourglass
Me.Visible = False
DoCmd.Hourglass True
Dim SearchString As String
SearchString = "SELECT * FROM XRD_Sands_All_Upgradeable WHERE XRD_Sample LIKE " & gstrWhereRec & ";"

Set db = CurrentDb

Set rst = db.OpenRecordset(SearchString)


DoCmd.Hourglass False

'If none found, then inform user and make form visible to try again
If rst.RecordCount = 0 Then
MsgBox "No publications meet your criteria.", vbInformation
gstrWhereRec = ""
rst.Close
Me.Visible = True
Exit Sub
End If

DoCmd.OpenReport ReportName:="XRD_Search_Results", View:=acViewPreview, WhereCondition:=gstrWhereRec

Me!searchField = Null
Me!searchTerm = Null
Me!SearchField2 = Null
Me!searchTerm2 = Null

End Sub

*---------------------------------------------------------*

I get my error with this piece of code...

Dim SearchString As String
SearchString = "SELECT * FROM XRD_Sands_All_Upgradeable WHERE XRD_Sample LIKE " & gstrWhereRec & ";"

Set db = CurrentDb

Set rst = db.OpenRecordset(SearchString)


Specifically the "Set rst..."

The recordset and the database object are initialized at the begining of the routine...

gstrWhereRec is initialized as a global string using a module...

I keep getting an error that says I have a datatype mismatch when I try to search for a record...All the fields in my table are text fields....

Can anyone spot the error, I've been looking at it for three days....

Thanks,
Pesteo....

 

[zemp]

The SQL statement may need single quotes around the text variable. Try,

SearchString = "SELECT * FROM XRD_Sands_All_Upgradeable WHERE XRD_Sample LIKE '" & gstrWhereRec & "';"
Thanks and Good Luck!

zemp

 

[Pesteo]

Zemp,

Due to your inquiry/suggestion, I found one error, I had an extra clause in there...

SearchString = "SELECT * FROM XRD_Sands_All_Upgradeable WHERE " & gstrWhereRec & ";"

gstrWhereRec should combine to:

XRD_Sample Like "*C017*"

This is formed dynamically depending on the serch criteria that the user enters....

Or Like:

XRD_Sample Like "*C016*" AND/OR Mineral_Moderate Like "*quartz*"

The double quotes are being placed around the Search Criteria automatically...How do I change those to single quotes?

 

[zemp]

Your variable 'gstrWhereRec' is not a search value but rather it is a string that will contain the search value.

gstrWhereRec = &quot;XRD_Sample Like '%&quot; & <Variable> & &quot;%'&quot;

and this should be placed into the search String,

SearchString = &quot;SELECT * FROM XRD_Sands_All_Upgradeable WHERE &quot; & gstrWhereRec & &quot;;&quot;

So your final SQL should look something like,

SearchString = &quot;SELECT * FROM XRD_Sands_All_Upgradeable WHERE XRD_Sample Like '%&quot; & <Variable> & &quot;%';&quot;

You will notice that I used the '%' as the wildcard character. That is what I usually use for Access. Thanks and Good Luck!

zemp

 

[Pesteo]

Set rst = db.OpenRecordset(SearchString)

equivocates to:

Set rst = db.OpenRecordset(&quot;SELECT * FROM XRD_Sands_All_Upgradeable Where XRD_Sample Like '*C017*';&quot

and I still get the datatype mismatch error....

The funny thing is that I copied my original code from a search routine I wrote that works great....All I did was change the names of the fields, and the other information to make it fit this database....

Thanks....

 

[zemp]

Check to make sure that the field XRD_Sample is text field.

Check to see if you are using the correct wildcard character.

Otherwise you will need to compare the code, tools and database from the search that works with the ones that don't and see if you can find any differences. Thanks and Good Luck!

zemp