ADODB Recordset Error when using .Find on alphanumeric criteria

[vbdbcoder]

I have a VB6.0 application, using ADODB recordset to find a record for a searched criteria. It only errors out when searching on Alphanumeric data. It works when searching on numeric data. The Error Number is 3001. The Error Message is "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.".

Below is the code, where .Find line would error out.

'ItemQuery = "1340079" 'This item works
'ItemQuery = "0200560" 'This item works
ItemQuery = "NS" 'This item errors out

RecordSet_PriceList.MoveFirst
RecordSet_PriceList.Find RecordSet_PriceList.Fields(0).Name & " = " & ItemQuery
If RecordSet_PriceList.EOF = False Then 'find the item

'Do my stuff here....​

End If

The ADO recordset is opened to pull from an Excel worksheet as : [rs.Open "Select * from [" & sSheetName & "$]", objAdCon, adOpenKeyset, adLockOptimistic]

Can you please direct me to a good fix? Any input is appreciated.

 

[Andrzejek]

Did you try:

ItemQuery = "NS" 'This item errors out

With RecordSet_PriceList
    .MoveFirst
    .Find .Fields(0).Name & " = [blue]'[/blue]" & ItemQuery [blue]& "'"[/blue]
    If .EOF = False Then [green]'find the item
        'Do my stuff here....[/green]
    End If
End With

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Hi,

Queries don't like Text and Numbers in the same column!!!

The query looks at the data in your column and assumes its numeric. It encounters the alpha and TILT!

 

[tedsmith]

Can you change the format of the price list field to text in the original database? If so it will handle both but the sorting order will be on the first number so 6 will come after 10.