I am writing Visual Basic code to access a Pervasive 2000 SQL database and want to use the CONCAT, IF, and LEFT functions in SELECT statements. So far, though, I seem to get an empty recordset when I use any of them. I don't get error messages when VB executes the project, though.
If I go into the Pervasive Control Center and execute the queries from there, they work, but not in my VB code. If I go into SQL Builder in VB, it will say the sql code could be 'verified against the data source', but won't run--'data provider or other service returned an E_FAIL status'.

EXAMPLE:

strcnx = "Provider = PervasiveOLEDB;Data Source=StarTracer;Password=<pwd>;User ID=<uid>;Persist Security Info=True"

<this statement works--rs.RecordCount shows records returned>
rs.Open "select lastname AS LName, firstname as FName from people", strcnx, adOpenDynamic, adLockOptimistic, adCmdText

<but this one doesn't--rs.RecordCount is zero, same if I use CONCAT or IF statements>
rs.Open "select left(lastname, 5) AS LName, firstname as FName from people", strcnx, adOpenDynamic, adLockOptimistic, adCmdText

MsgBox rs.RecordCount
MsgBox Err.Number & ", " & Err.Description & ", " & Err.Source

Do you actually get records but no record count? What are rs.EOF and rs.BOF set to? Can you iterate through the record set and get data?

Thanks for your reply.
bof and eof are both true, and rs.movenext gives an error

Another clue might be that I can use subtraction:

SELECT (ComputerCharge - ActualCharge) AS RcptDiff FROM TxCash

If BOF and EOF are both true, then there's no records in the record set. What happens if you change from adOpenDyanmic to adOpenStatic?
What happens if you change from the PervasiveOLEDB provider to ODBC (Change the Connect String to "DSN=<dsnname>;Pwd=jfltlc;UID=Master".
Also, what version of 2000 are you using (easiest way is to view the version of W3ODBCCI.DLL or W3ODBCEI.DLL)?

Changing to ODBC from PervasiveOLEDB did the trick, Concat and Left work, anyway.
My larger problem is that I'd still like to manipulate the data before passing it to the report object--I'm using a VB DataReport object and binding it to the recordset in question. Once you pass it to the DataReport, you don't have access to the records as you move through the recordset. If I want, for example, to display "Checkout" for a TxType of '1' and "Checkin" for a TxType of '2' and so on (for about 15 transaction types).
Can I create some kind of temporary table for a set of several hundred to a thousand records and bind the DataReport object to that?

There's not an easy way to build temp tables and insert records. You would actually have to create the table, read the records from one table, insert them into the temp table then use the new table in the DataReport. You might try just building the query to return the values. For example, I have the following query in one of my apps:
select i.id, i.title, m.media, i.price, i.notes from tblItem i, tblMedia m where i.media = m.id
This displays media data and has things like "DVD" for the media rather than 1.