.Find on integer data

[StephanieHarris]

Hello,

I am using a sql server database and I have a smallint field. I've used the code below successfully whenever I'm searching for char data. But I get an error when I try to search for integer data. My error is 'Find cannot be performed over the specified column or compare operator is invalid.'

Here is my code:

Dim SearchValue As Integer
Dim SearchField As String
    
    Select Case cmbSearchField
        Case Is = "Trunk Number"
            SearchField = "TrunkNumber"
        Case Is = "Trunk Group Number"
            SearchField = "GroupNumber"
        Case Else
            MsgBox ("You must select a valid search field.")
            Exit Sub
    End Select
    
    SearchValue = InputBox("Enter your search value:", "Database Search")
    
    If Trim(SearchValue) = "" Then
        'do nothing
    Else
        With Adodc1.Recordset
            .Find (SearchField & " Like '%" & SearchValue & "%'")
            If Adodc1.Recordset.EOF Then
                MsgBox ("Record could not be found.")
            End If
        End With
    End If

Any ideas why I am having trouble with integer data?

Thanks, Stephanie

[HanneSThEGreaT]

Try changing
SearchValue = InputBox("Enter your search value:", "Database Search")

to
SearchValue = Val(InputBox("Enter your search value:", "Database Search"))

[StephanieHarris]

No luck! Unfortunately I'm still getting the same error. Any other suggestions?

Thanks for the help!

Stephanie

[doofusboy]

I may be wrong, but think it may have something to do with the single quotes in this line:

.Find (SearchField & " Like '%" & SearchValue & "%'")

When inserting or querying numeric values in databases single quotes are not typically used in SQL statements, so maybe you're seeing a related issue? Also, I'm not sure whether LIKE can be used for numeric data.

For example, if data in db is of string/text type, the values are surrounded by single quotes in SQL and if data is of date type, the values are surrounded by # marks in SQL.

SELECT your_string_data FROM your_table
WHERE your_string_data LIKE '%blah%';

SELECT your_numeric_data FROM your_table
WHERE your_numeric_data = 12;