SQL statement in VBA

[tmercier]

I am attempting to edit code that was written by a different developer (I'm not familiar with SQL) to include a field from another table 'tblInstallSummary'. How do I edit the SQL statement to reflect this? I've bolded my attempt below but I am just getting an error.

Thanks to anyone who can help

SQL = "SELECT tblHeader.[Transaction Number], tblDetails.[Installation Order Number]," _
& "tblDetails.[Item Number], tblHeader.BP, tblDetails.[Order Quantity], tblDetails.Material, tblDetails.[Line SOI Date]," _
& "tblDetails.[Material Desc from SO line], tblHeader.[Ship-to Party]," _
& "tblHeader.[CAM: Street], tblHeader.[CAM: District], tblHeader.Region" _
& " FROM tblHeader INNER JOIN tblDetails ON tblHeader.[Transaction Number] = tblDetails.[Transaction Number]" _
& " AND ON tblInstallSummary.[TransactionNumber]" _ & " WHERE" & strCriteria & " ORDER BY tblDetails.[Item Number]"

 

[dcobau]

Include tblInstallSummary and it's fields in the SELECT and FROM parts of the query.

 

[tmercier]

I've tried but am still getting a run-time error 2147217900 The Select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. Here's my latest changes but I can't find where the error is.

& " FROM (tblDetails INNER JOIN tblHeader ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number]) LEFT JOIN tblInstallSummary ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1," _
& " WHERE" & strCriteria & " ORDER BY tblDetails.[Item Number]"

 

[dcobau]

try the query in the query environment and see if it works there.

 

[tmercier]

Yes, it does work in the query environment. The problem seems to occur during the translation into vba at the 'where' statement.

 

[pbaldy]

Unless strCriteria provides it, you need a space after WHERE. The easiest way to debug it is to add

Debug.Print SQL

and review the completed SQL statement in the immediate window. If you don't spot the problem, post that SQL here.

 

[tmercier]

Thanks for the tip! I'm new to this so am not aware of debugging tools. Here's what I get when I run debug in the immediate window... I don't see anything wrong though ;-(

SELECT tblHeader.[Transaction Number], tblHeader.BP, tblHeader.[Ship-to Party], tblHeader.[CAM: Street],tblHeader.[CAM: District], tblHeader.Region, tblDetails.[Installation Order Number],tblDetails.[Item Number], tblDetails.[Order Quantity], tblDetails.Material,tblDetails.[Material Desc from SO line], tblInstallSummary.SOI, FROM (tblDetails INNER JOIN tblHeader ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number]) LEFT JOIN tblInstallSummary ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1 WHERE[tblHeader.Transaction Number]=182842 ORDER BY tblDetails.[Item Number]

And here's my strCriteria code:

'Create criteria to filter the Transaction Number
strCriteria = "[Installation Order Number]='" & Forms!frmInstallSPList!frmInstallSPListSubform.Form![Installation Order Number] & "'"

Do you see anything incorrect?

 

[pbaldy]

Yes, and I already pointed it out.

 

[tmercier]

I'm not following you???? Do you mean add a space after the Where statement? If yes, I did that... not sure why it doesn't show in the code I pasted but I double checked and it is definately there.

 

[pbaldy]

This indicates it's NOT there:

...WHERE[tblHeader.Transaction Number]=182842...

Also, I'm not sure that's the same criteria bit, as it's putting single quotes around the value, which is not appearing in the completed SQL.

 

[tmercier]

Sigh! I just can't find the error :-(

SQL = "SELECT tblHeader.[Transaction Number], tblHeader.BP, tblHeader.[Ship-to Party], tblHeader.[CAM: Street]," _
& "tblHeader.[CAM: District], tblHeader.Region, tblDetails.[Installation Order Number]," _
& "tblDetails.[Item Number], tblDetails.[Order Quantity], tblDetails.Material," _
& "tblDetails.[Material Desc from SO line], tblInstallSummary.SOI," _
& " FROM (tblDetails INNER JOIN tblHeader ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number])," _
& " LEFT JOIN tblInstallSummary ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1," _
& " WHERE " & strCriteria & " ORDER BY tblDetails.[Item Number] "

I did copy the wrong strCriteria... it's:
strCriteria = "[tblHeader.Transaction Number]=" & Forms!frmIP_Planning![Transaction Number]

 

[pbaldy]

This

[tblHeader.Transaction Number]

should be

tblHeader.[Transaction Number]

 

[tmercier]

Thanks for catching that! But it's still not working :-(
This is what I'm getting now but I don't know how to read it to know where to look for the error.

Debug.Print SQL
SELECT tblHeader.[Transaction Number], tblHeader.BP, tblHeader.[Ship-to Party], tblHeader.[CAM: Street],tblHeader.[CAM: District], tblHeader.Region, tblDetails.[Installation Order Number],tblDetails.[Item Number], tblDetails.[Order Quantity], tblDetails.Material,tblDetails.[Material Desc from SO line], tblInstallSummary.SOI, FROM (tblDetails INNER JOIN tblHeader ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number]), LEFT JOIN tblInstallSummary ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1, WHERE tblHeader.[Transaction Number]=182842 ORDER BY tblDetails.[Item Number]

 

[pbaldy]

The comma before WHERE wasn't there before, and is certainly a problem (and so is the one before FROM) . I'm having a brain cramp but I don't think the one before LEFT JOIN is appropriate either. Another debugging tool is to paste that SQL into a new query and try to run it. You'll often get a more descriptive message than you will in VBA.

 

[tmercier]

I can get it to work in the query window... just not in VBA. Here's the code that works in the query window. When I convert it to what VBA likes (adding the & signs and the underscores) I get errors

SELECT
tblHeader.[Transaction Number], tblHeader.BP, tblHeader.[Ship-to Party], tblHeader.[CAM: Street],
tblHeader.[CAM: District], tblHeader.Region, tblDetails.[Installation Order Number],
tblDetails.[Item Number], tblDetails.[Order Quantity], tblDetails.Material,
tblDetails.[Material Desc from SO line], tblInstallSummary.SOI
FROM (tblDetails INNER JOIN tblHeader ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number])
LEFT JOIN tblInstallSummary ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1
WHERE " & strCriteria & " ORDER BY tblDetails.[Item Number];

 

[pbaldy]

That's not going to work in the query window with " & strCriteria & " in there. Post the working SQL from the query window and the Debug.Print from the VBA version and we'll find the difference.

 

[tmercier]

Thanks for your help . I'm going a bit batty right now

Debug.Print code:
SELECT tblHeader.[Transaction Number], tblDetails.[Installation Order Number],tblDetails.[Item Number], tblHeader.BP, tblDetails.[Order Quantity], tblDetails.Material, tblDetails.[Line SOI Date],tblDetails.[Material Desc from SO line], tblHeader.[Ship-to Party],tblHeader.[CAM: Street], tblHeader.[CAM: District], tblHeader.Region FROM tblHeader INNER JOIN tblDetails ON tblHeader.[Transaction Number] = tblDetails.[Transaction Number] WHERE tblHeader.[Transaction Number]=182842 ORDER BY tblDetails.[Item Number]

SQL code from Query:
SELECT tblHeader.[Transaction Number], tblHeader.BP, tblHeader.[Ship-to Party], tblHeader.[CAM: Street], tblHeader.[CAM: District], tblHeader.Region, tblDetails.[Installation Order Number], tblDetails.[Item Number], tblDetails.[Order Quantity], tblDetails.Material, tblDetails.[Material Desc from SO line], tblInstallSummary.SOI
FROM (tblDetails INNER JOIN tblHeader ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number]) LEFT JOIN tblInstallSummary ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1
WHERE (((" & strCriteria & ")<>False))
ORDER BY tblDetails.[Item Number];

 

[pbaldy]

Those are different queries (the second includes another table), but offhand I don't see anything wrong with the first. Can you post a sample db? It would probably be easier to figure out the problem that way.

 

[MicroE]

tmercier - Just a couple of suggestions.

Post all your code for a procedure – seeing only part of it makes it difficult to offer assistance. I have no idea what data type your variables are and when you are setting the strCriteria value. Also, where are you using the SQL variable? We need to see the line of code causing the error.

Second – when creating long SQL strings in VBA, I suggest concatenating the variable + literal using (SQL = SQL & “string”) rather than the continuous line operator (& _). This makes it easier to identify errors and SQL consistency.
http://www.myaccesstips.com/Tips_Code.html

Lastly – I would create a query with criteria that produces the result you want and save it (make sure to run it and verify the data/results are correct). Then copy and paste the working SQL into a text file (Word) and the results of your Debug.Print SQL statement and compare the two to find you error.

Assuming [Transaction Number] is a Numeric data type try:

    Dim SQL As sring
    Dim strCriteria As sring
    
    strCriteria = Forms!frmIP_Planning![Transaction Number]

    SQL = "SELECT tblHeader.[Transaction Number], "
    SQL = SQL & "tblHeader.BP , "
    SQL = SQL & "tblHeader.[Ship-to Party], "
    SQL = SQL & "tblHeader.[CAM: Street], "
    SQL = SQL & "tblHeader.[CAM: District], "
    SQL = SQL & "tblHeader.Region, "
    SQL = SQL & "tblDetails.[Installation Order Number], "
    SQL = SQL & "tblDetails.[Item Number], "
    SQL = SQL & "tblDetails.[Order Quantity], "
    SQL = SQL & "tblDetails.Material, "
    SQL = SQL & "tblDetails.[Material Desc from SO line], "
    SQL = SQL & "tblInstallSummary.SOI "
    SQL = SQL & " FROM (tblDetails INNER JOIN tblHeader "
    SQL = SQL & "ON tblDetails.[Transaction Number] = tblHeader.[Transaction Number]) "
    SQL = SQL & "LEFT JOIN tblInstallSummary "
    SQL = SQL & "ON tblDetails.Concatenate1 = tblInstallSummary.Concatenate1 "
    SQL = SQL & "WHERE tblHeader.[Transaction Number]=" & strCriteria & " "
    SQL = SQL & " ORDER BY tblDetails.[Item Number] "

Again - where is the reference to the SQL varaible now that you gave it a value? What is the next step?

 

[pbaldy]

Your opinion regarding the use of repeated concatenation of the variable vs using line continuation characters is probably a personal preference visually. Personally I find it easier to view code with the line continuation method.

In any case, I think you'll find that method is less efficient. In my testing, Access processes the line continuation method faster than the repeated concatenation method.