convert access query to vba

G

Grimwadec

I wrote this VBA CODE:
Dim strgSQLIsLoaded As String
strgSQLSelect = "SELECT * FROM tblPurchOrders "
strgSQLWhere = "WHERE JobID = " &
([Forms]![frmCustomerG].[subfrmJob].[Form].[JobID]) & " "
strgSQLAnd = "And SuppName = " &
(IIf(([Forms]![frmCustomerG].[subfrmJob].[Form].[PurchType] = "Make"),
([Forms]![frmCustomerG].[subfrmJob].[Form].[MakingSupplier]),
([Forms]![frmCustomerG].[subfrmJob].[Form].[TrimSupplier]))) & " "
strgSQLIsLoaded = strgSQLSelect & strgSQLWhere & strgSQLAnd

From the following ORIGINAL QUERY
SELECT tblPurchOrders.ID, tblPurchOrders.JobID, tblPurchOrders.SuppName,
tblPurchOrders.OrderDate, tblPurchOrders.OrderDetail, tblPurchOrders.OurCo,
tblPurchOrders.OurPersonShortName
FROM tblPurchOrders
WHERE (((tblPurchOrders.JobID)=Forms!frmCustomerG.subfrmJob.Form.JobID) And
((tblPurchOrders.SuppName)=IIf((Forms!frmCustomerG.subfrmJob.Form.PurchType="Make"),(Forms!frmCustomerG.subfrmJob.Form.MakingSupplier),(Forms!frmCustomerG.subfrmJob.Form.TrimSupplier))));

I get Runtime error “Syntax Error (Missing Operator)â€

Please can anyone help me with locating the Syntax error – I guess the IIf
didn’t help
 
J

Jeanette Cunningham

Hi Grimwadec,
use this process.
At the end of the vba for the query, add this line

Debug.Print strgSQLIsLoaded

Run the form and then open the immediate window (Ctl + G).
You will see what access has for strgSQLIsLoaded
Copy what access has into a new query in sql view.

Switch the query to datasheet view and the error message will help you to
debug the query.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
G

Grimwadec

Yep, Allen Browne's converter worked perfectly. Thanks heaps!
--
Grimwadec


Jack Leach said:
Between Stuart and Allen's work maybe you can use these converters to
pinpoint the issue:

http://www.smccall.demon.co.uk/Downloads.htm#SQLFormat

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



Grimwadec said:
I wrote this VBA CODE:
Dim strgSQLIsLoaded As String
strgSQLSelect = "SELECT * FROM tblPurchOrders "
strgSQLWhere = "WHERE JobID = " &
([Forms]![frmCustomerG].[subfrmJob].[Form].[JobID]) & " "
strgSQLAnd = "And SuppName = " &
(IIf(([Forms]![frmCustomerG].[subfrmJob].[Form].[PurchType] = "Make"),
([Forms]![frmCustomerG].[subfrmJob].[Form].[MakingSupplier]),
([Forms]![frmCustomerG].[subfrmJob].[Form].[TrimSupplier]))) & " "
strgSQLIsLoaded = strgSQLSelect & strgSQLWhere & strgSQLAnd

From the following ORIGINAL QUERY
SELECT tblPurchOrders.ID, tblPurchOrders.JobID, tblPurchOrders.SuppName,
tblPurchOrders.OrderDate, tblPurchOrders.OrderDetail, tblPurchOrders.OurCo,
tblPurchOrders.OurPersonShortName
FROM tblPurchOrders
WHERE (((tblPurchOrders.JobID)=Forms!frmCustomerG.subfrmJob.Form.JobID) And
((tblPurchOrders.SuppName)=IIf((Forms!frmCustomerG.subfrmJob.Form.PurchType="Make"),(Forms!frmCustomerG.subfrmJob.Form.MakingSupplier),(Forms!frmCustomerG.subfrmJob.Form.TrimSupplier))));

I get Runtime error “Syntax Error (Missing Operator)â€

Please can anyone help me with locating the Syntax error – I guess the IIf
didn’t help
Click to expand...
Click to expand...
 
Top