VBA SQL CODE HELP 1

[3239]

Hello everyone,

I have been looking at this code for hours and just can't find the error in it. It is giving me a syntax error in the JOIN operation. The code is below, please help.

Thanks

DoCmd.RunSQL "INSERT INTO TempAll ( ID, SDate, NAME,Screener, PROVIDER," & _
  "MatName, LotID, Quant ) SELECT Dependents.DependentID AS ID, Clients.SDate, Dependents.LNAM & ', ' & Dependents.FNAM & ' ' & Dependents.MNAM AS NAME," & _
  "Screener_cds.Screener, Provider_cds.Provider, Material.MatName, Dependents.LotID, " & _
  "Dependents.Quant FROM Material RIGHT JOIN (((((Clients LEFT JOIN Provider_cds ON Clients.PROVIDER = Provider_cds.ProvCd) " & _
  "LEFT JOIN Screener_cds ON Clients.SCREEN = Screener_cds.ScrnCd) " & _
  "ON Clients.ClientID = Dependents.ProxyClientID) ON Material.MatID = Dependents.MatID;"

 

[PHV]

No Dependents table in the FROM clause ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

A standard programming practice is to assign your SQL code to a string variable, and Debug.Print the variable.

If your code produces no results, just COPY the SQL code in your immediate window and run in the SQL Editor.

I perfer to do this, as it greatly clarifies my code. Immediately, you can see what the problem is, can't you???

    Dim sSQL As String
    
    sSQL = sSQL & "INSERT INTO TempAll"
    sSQL = sSQL & "("
    sSQL = sSQL & "  ID"
    sSQL = sSQL & ", SDate"
    sSQL = sSQL & ", NAME"
    sSQL = sSQL & ",Screener"
    sSQL = sSQL & ", PROVIDER"
    sSQL = sSQL & ",MatName"
    sSQL = sSQL & ", LotID"
    sSQL = sSQL & ", Quant"
    sSQL = sSQL & " )"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  Dependents.DependentID AS ID"
    sSQL = sSQL & ", Clients.SDate"
    sSQL = sSQL & ", Dependents.LNAM & ', ' & Dependents.FNAM & ' ' & Dependents.MNAM AS NAME"
    sSQL = sSQL & ",Screener_cds.Screener"
    sSQL = sSQL & ", Provider_cds.Provider"
    sSQL = sSQL & ", Material.MatName"
    sSQL = sSQL & ", Dependents.LotID"
    sSQL = sSQL & ", Dependents.Quant"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM Material RIGHT JOIN"
    sSQL = sSQL & "(((((Clients LEFT JOIN Provider_cds"
    sSQL = sSQL & " ON Clients.PROVIDER = Provider_cds.ProvCd"
    sSQL = sSQL & "    ) LEFT JOIN Screener_cds"
    sSQL = sSQL & " ON Clients.SCREEN = Screener_cds.ScrnCd"
    sSQL = sSQL & "   )"
    sSQL = sSQL & " ON Clients.ClientID = Dependents.ProxyClientID"
    sSQL = sSQL & "  ) ON Material.MatID = Dependents.MatID;"

    Debug.Print sSQL

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

I'd use this FROM clause:
FROM (((Clients
LEFT JOIN Dependents ON Clients.ClientID = Dependents.ProxyClientID)
LEFT JOIN Material ON Dependents.MatID = Material.MatID)
LEFT JOIN Provider_cds ON Clients.PROVIDER = Provider_cds.ProvCd)
LEFT JOIN Screener_cds ON Clients.SCREEN = Screener_cds.ScrnCd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Notice in your FROM clause, your code has FIVE opening parentheses and only THREE closing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

That's why I could not agree more with Skip - build your SQL in the SQL Editor, make sure it runs, make sure you get what you need, and then - and only then - move your SQL to a variable in your code.

You will save yourself A LOT of headache

Have fun.

---- Andy

 

[SkipVought]

build your SQL in the SQL Editor, make sure it runs, make sure you get what you need, and then - and only then - move your SQL to a variable in your code.

Standard operating procdedure!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[3239]

Thanks for the tip Skip!! Running my code in the SQL Editor will save me from pulling my hair in the future!!