Handeling various data types and white spaces when searching Access DB

[KingKen]

I have created a search form for my access database. this form submits the table name, the field name and the search text.
After identifying the table to search i then use this clause in my SQL statement to find the results

WHERE " & myfield & " = '" & mySchTxt & "'"

This works fine when mySchTxt is a single word string. Problem is mySchTxt could sometimes be a string of two or more words or even a number and that's when the errors flow.

I need help in allowing a search for all data types and handling white spaces between submitted strings.

[DrBunchman]

Hi KingKen,

What errors are you getting?

Is the datatype of myfield always appropriate for the search parameter?

When you pass in mySchTxt and it causes an error have you checked what the value of mySchTxt is at this point (By using a response.write or a msgbox say)? Has it changed from the text that you submitted?

Dr B

[KingKen]

No I have nothing setup to change from text to number. For this it gives me a type mismatch error.

When i search the with white space in the search text i get
"Syntax error in date in query expression 'Model# = 'dell 620''. "

[jeffstl]

Ken

If there are multiple strings you want to search on you can't just point to a single string in a query. You would have to divide out your string and make your query more like this

WHERE " & myfield & " = '" & mySchTxt & "' or " & myfield & " = '" & mySchTxt2 & "' or " & myfield & " = '" & mySchTxt3 & "'"

As far as varying datatypes, no data type matters whatsoever except for one. The data type that is in the table behind myfield.

You are going to have to check that datatype if myfield can be different datatypes in the table and print appropriate SQL for each data type. For this you will need to have a select case hard coded with the data types (or something like that)

so....

Select Case MyDataField

     Case "ColumnName"
          'for this example I assume ColumnName in a table is a number
          SQL = "place sql here for number datatype"
     Case "AnotherColumnName"
          SQL = "place sql here for text datatype" 
     'and so on..........
End Select

I see you are essentially constructing a custom query builder that would allow users to search the table for whatever field. These are the kinds of tricks you will need to do to allow that kind of flexibility with a user search from an application side.

[KingKen]

I tried your solution but am still getting data type missmatch error. here is what I have

SELECT ... WHERE " & myfield & " = "& mySchTxt &""

I even tried the Cint() function but nothing budges.

Eventhough the error tells me it is a type mismatch the line points to the following codes

RecordSet.Open cmdDC, , 3, 3

Does this have anything to do with that error? if so what to do.

One last thing. How do I deal with dates

[jeffstl]

I tried your solution but am still getting data type missmatch error. here is what I have

SELECT ... WHERE " & myfield & " = "& mySchTxt &""

I even tried the Cint() function but nothing budges.

Eventhough the error tells me it is a type mismatch the line points to the following codes

RecordSet.Open cmdDC, , 3, 3

Does this have anything to do with that error? if so what to do.

One last thing. How do I deal with dates

So you got the error, but what is in myfield? What data type is that column? Is it a int or a string?

Dates just use # instead of ' , but sometimes this depends on what database system your using. Most should accept #

So if myfield is a string you use '" & schtxt & "'
if myfield number you use " & schtxt & "
if myfield date you use #" & schtxt & "#

And yes that would be the line that causes the error because that's where your EXECUTING the sql. When you are doing sql = whatever all you are doing is preparing the string

[KingKen]

Ok.
I have implimented the solutions as suggested and still am having trouble.
the string and date data type are functioning just fine but the intiger is still giving errors.

Here is what I am having

Select Case myTable
     Case "Table1":
     SQL = "SELECT * FROM "& myTable &""

If mySchTxt <> "" Then
    If myField="ID" Then
    'CInt ("& mySchTxt &")
    SQL = "SELECT ... WHERE " & myfield & " = " & mySchtxt & ""
        else If myField="DateReported" Then
        SQL = "SELECT ... WHERE " & myfield & " = #"&mySchTxt&"# "
    else
    SQL = "SELECT ... WHERE " & myfield & " = '" & mySchTxt & "'"
    End If
    End If
End If
cmdDC.CommandText = SQL
Set RecordSet = Server.CreateObject("ADODB.Recordset")
RecordSet.Open cmdDC, , 3, 3

It is giving me a data type mis match when searchtxt is intiger.

[jeffstl]

Ok.
I have implimented the solutions as suggested and still am having trouble.
the string and date data type are functioning just fine but the intiger is still giving errors.

Here is what I am having

Select Case myTable
     Case "Table1":
     SQL = "SELECT * FROM "& myTable &""

If mySchTxt <> "" Then
    If myField="ID" Then
    'CInt ("& mySchTxt &")
    SQL = "SELECT ... WHERE " & myfield & " = " & mySchtxt & ""
        else If myField="DateReported" Then
        SQL = "SELECT ... WHERE " & myfield & " = #"&mySchTxt&"# "
    else
    SQL = "SELECT ... WHERE " & myfield & " = '" & mySchTxt & "'"
    End If
    End If
End If
cmdDC.CommandText = SQL
Set RecordSet = Server.CreateObject("ADODB.Recordset")
RecordSet.Open cmdDC, , 3, 3

It is giving me a data type mis match when searchtxt is intiger.

Try one more check then to make sure that mySchtxt is actually an integer before you try comparing it to a numeric data type from your database

if IsNumeric(schtxt) then
     'proceed with SQL
else
     'either set schtxt to 0 or report error to user
end if

[KingKen]

I found the error... I didn't realize that my field name was case sensative so i was using a lowercase where it should have been an uppercase latter.
Thanks for the troubleshooting tip. I'll remember that one.

So problem solved here. Once again thanks you guys.