SQLite substr problem

[phaworth]

Something weird going on with a SELECT statement that includes a substr function.

This statement yields the ubiquitous "SQL error or missing database" rev error:

SELECT AccountID,substr(AccountName,12) FROM AccountHdr WHERE AccountType='Asset' AND substr(AccountName,1,11)='Receivable:' AND Hidden =0 ORDER BY AccountName

The exact same statement (copied and pasted) in the Firefox SQLite Manager plug in works fine and produces the correct results.

Trial and error shows that the following works:

SELECT AccountID,AccountName FROM AccountHdr WHERE AccountType='Asset' AND substr(AccountName,1,11)='Receivable:' AND Hidden =0 ORDER BY AccountName

So something about the substr function in the SELECT appears to be the problem. I'm using revDataFromQuery to execute the SELECT.

Any ideas?

As an aside, why does rev not return specific SQLite error messages?

Thanks,
Pete