Hi,

I get the following error:
"[QODBC] Expected Lexical element not found: From"

when trying to execute this SQL command:
select ListID = (select ListID from Vendor where Name = 'ABC')

I am trying to insert a new bill, but my program only knows the Vendor Name, not the vendorRefListID, so I have to query for it.  I am trying to do this all in one statement as follows, but QODBC gives the above error:

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber", "DueDate", "Memo") select VendorRefListID = (select ListID from Vendor where Name = 'ABC'), APAccountRefListID = (select ListID from account where Name = 'Accounts Payable'), {d'2006-06-26'}, '100', {d'2006-06-30'},'Expense Memo Test 5'

(of course the above insert is done after the invoice details are first inserted)

is there a better way to do this query?
thanks!
rob

When doing INSERTs you need to use either the ListID or FullName, so in your case you can use the FullName like this:

INSERT INTO "Bill" ("VendorRefFullName", "APAccountRefFullName", "TxnDate", "RefNumber", "DueDate", "Memo")
VALUES ('ABC', 'Accounts Payable',  {d'2006-06-26'}, '100',  {d'2006-06-30'}, 'Expense Memo Test 5' )

See: How do I create Bills? for more.