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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with text box 10
Specifying conditional Criteria in a Query or Report 6
Problem with using function in a drop down query of a row source 4
Including Null values in query design 6
Access (XP) crashes on after update of combo box 2
VBA to set Form’s *design* values 6
Problem Moving to New Records 1
Line-Break VBA to pass to a Query 6

Top