Use the "LIKE" operator

[rmcmorris]

I have a VBA module in Excel where I am returning data from a Paradox database using ADO. Previously, the query would match a number variable. This worked fine and returned all of the records. Now I am changing the code to match a string variable instead on a different database field. When using an "=" operator for the match in the query, only the first matched record is returned to the ADO recordset. If I use the "LIKE" operator, all of the records are returned. Any ideas why this happens????

The problem also occurs if using VBA in Word or if using the Excel Data Wizard to retrieve external data. If I link the Paradox table in Access and run an Access query, all of the data returns fine.

'This works fine with a number field.
MyNumber=123
rst.Open "SELECT MyTable.Field1, MyTable.Field2, MyTable.Field3 FROM MyTable WHERE (((MyTable.`NumField`)=" & MyNumber & &quot)", ADOcon, adOpenForwardOnly, adLockReadOnly

'This Does not work with an alpha field. It returns first
'matching record only.
MyText="abc"
rst.Open "SELECT MyTable.Field1, MyTable.Field2, MyTable.Field3 FROM MyTable WHERE (((MyTable.`TextField`)="' & MyText & "'))", ADOcon, adOpenForwardOnly, adLockReadOnly

'But using the LIKE operator in the Text field
'Works fine.
MyText="abc"
rst.Open "SELECT MyTable.Field1, MyTable.Field2, MyTable.Field3 FROM MyTable WHERE (((MyTable.`TextField`) LIKE "' & MyText & "'))", ADOcon, adOpenForwardOnly, adLockReadOnly

I have installed the Jet40 Service Pack 6. My OS is Windows 2000 Professional. I am using Excel 97.

Thanks,

Regan

 

[Zathras]

Not sure, but it looks like the double-quote and single quote are reversed:

SELECT ....`)="' & MyText....
              ^^
  should be   '"   (??)

If it's not that, then someone else will have to jump in here...

 

[rmcmorris]

Hard to tell sometimes, but the quotes are correct with the apostrophe then the quote.