syntax error 3075 SQL-VBA

[arkadis]

Hey guys i'm trying to write sql query in vba module and i'm getting all the time a syntax error 3075.
What is wrong with my code?

Function rs()

On Error Resume Next
    Dim MM As String
    Dim rstB As Recordset
    Dim Par1 As String
    Par1 = 1360
    Dim Par2 As String
    Par2 = "dbKA"

    
Set rstB = CurrentDb.OpenRecordset("SELECT tSale.KodParty" & _
                                        ", tSale.GoodsID" & _
                                        ", tSale.KodWork" & _
                                        ", tSale.ActualMU" & _
                                        ", tSale.NumberUnits" & _
                                        ", tSale.CostOut" & _
                                        ", tSale.Summ" & _
" (((FROM [tMov_ClientInvoiceDetails] AS tSale" & _
" INNER JOIN [tMov_Parties] AS tParty ON tSale.KodParty = tParty.KodParty)" & _
" INNER JOIN [tPrd_Goods] AS PL ON tSale.GoodsID = PL.GoodsID)" & _
" INNER JOIN [tPrd_GoodsData] AS PN ON tSale.GoodsID = PN.GoodsID)" & _
" INNER JOIN [WB_units_list] AS RS ON tSale.ActualMU = RS.OurID)" & _
" Where (tMov_Parties.KodParty = " & Par2 & ") And (tPrd_GoodsData.langID = " & Par2 & ")")
    

    Do While Not rstB.EOF
        MM = rstB("KodWork")
        Debug.Print MM
    rstB.MoveNext
    Loop
    
End Function

 

[Minty]

You are dimming Par1 as a string and promptly trying to store an integer value in it.

If you remove the ON error it will highlight where your issue is.

EDIT : Also you are then using Par 2 (String) as a where condition without enclosing it in ' ' quotes.

Put your sql string together in a a variable before trying to execute it and the debug.print it and you will see more clearly.

 

[arkadis]

You are dimming Par1 as a string and promptly trying to store an integer value in it.

If you remove the ON error it will highlight where your issue is.

EDIT : Also you are then using Par 2 (String) as a where condition without enclosing it in ' ' quotes.

Put your sql string together in a a variable before trying to execute it and the debug.print it and you will see more clearly.

I changed the Par1 to integer and removed the on error.
I am still receiving the same error with all the query highlighted.
Any advice?

 

[Minty]

Yes -

Dim sSql as String

sSql ="SELECT tSale.KodParty" & _
                                        ", tSale.GoodsID" & _
                                        ", tSale.KodWork" & _
                                        ", tSale.ActualMU" & _
                                        ", tSale.NumberUnits" & _
                                        ", tSale.CostOut" & _
                                        ", tSale.Summ" & _
" (((FROM [tMov_ClientInvoiceDetails] AS tSale" & _
" INNER JOIN [tMov_Parties] AS tParty ON tSale.KodParty = tParty.KodParty)" & _
" INNER JOIN [tPrd_Goods] AS PL ON tSale.GoodsID = PL.GoodsID)" & _
" INNER JOIN [tPrd_GoodsData] AS PN ON tSale.GoodsID = PN.GoodsID)" & _
" INNER JOIN [WB_units_list] AS RS ON tSale.ActualMU = RS.OurID)" & _
" Where (tMov_Parties.KodParty = " & Par2 & ") And (tPrd_GoodsData.langID = " & Par2 & ")")

Debug.Print sSql

Copy and paste the immediate results to a text editor and examine them carefully. Or try pasting them into the query designer and see where it chucks an error.

 

[arkadis]

Yes -

Dim sSql as String

sSql ="SELECT tSale.KodParty" & _
                                        ", tSale.GoodsID" & _
                                        ", tSale.KodWork" & _
                                        ", tSale.ActualMU" & _
                                        ", tSale.NumberUnits" & _
                                        ", tSale.CostOut" & _
                                        ", tSale.Summ" & _
" (((FROM [tMov_ClientInvoiceDetails] AS tSale" & _
" INNER JOIN [tMov_Parties] AS tParty ON tSale.KodParty = tParty.KodParty)" & _
" INNER JOIN [tPrd_Goods] AS PL ON tSale.GoodsID = PL.GoodsID)" & _
" INNER JOIN [tPrd_GoodsData] AS PN ON tSale.GoodsID = PN.GoodsID)" & _
" INNER JOIN [WB_units_list] AS RS ON tSale.ActualMU = RS.OurID)" & _
" Where (tMov_Parties.KodParty = " & Par2 & ") And (tPrd_GoodsData.langID = " & Par2 & ")")

Debug.Print sSql

Copy and paste the immediate results to a text editor and examine them carefully. Or try pasting them into the query designer and see where it chucks an error.

I had more that i need parentheses in my code. :banghead:
I am now getting a 3061 Error Too few parameters. Expected 2.
Any idea what it can be?

Function rs()

    Dim MM As String
    Dim rstB As Recordset
    Dim Par1 As Integer
    Par1 = 1360
    Dim Par2 As String
    Par2 = "dbKA"
    Dim sSql As String

                            sSql = "SELECT tSale.KodParty" & _
                                        ", tSale.GoodsID" & _
                                        ", tSale.KodWork" & _
                                        ", tSale.ActualMU" & _
                                        ", tSale.NumberUnits" & _
                                        ", tSale.CostOut" & _
                                        ", tSale.Summ" & _
" FROM ((([tMov_ClientInvoiceDetails] AS tSale" & _
" INNER JOIN tMov_Parties AS tParty ON tSale.KodParty = tParty.KodParty)" & _
" INNER JOIN tPrd_Goods AS PL ON tSale.GoodsID = PL.GoodsID)" & _
" INNER JOIN tPrd_GoodsData AS PN ON tSale.GoodsID = PN.GoodsID)" & _
" INNER JOIN WB_units_list AS RS ON tSale.ActualMU = RS.OurID" & _
" Where (((tMov_Parties.KodParty) = '" & Par1 & "') And ((tPrd_GoodsData.langID) = '" & Par2 & "'))"
    
Set rstB = CurrentDb.OpenRecordset(sSql)
    

    Do While Not rstB.EOF
        MM = rstB("KodWork")
        Debug.Print MM
    rstB.MoveNext
    Loop
    
End Function

 

[arkadis]

Finally 2 days of nightmare gave me the right result. Here's the code for anyone who has similar problem.

Function rs()

    Dim MM As String
    Dim rstB As Recordset
    Dim Par1 As Integer
    Par1 = 1360
    Dim Par2 As String
    Par2 = "dbKA"
    Dim sSql As String
    Const cQUOTE = """"
                            sSql = "SELECT tSale.[KodParty]" & _
                                        ", tSale.[GoodsID]" & _
                                        ", tSale.[KodWork]" & _
                                        ", tSale.[ActualMU]" & _
                                        ", tSale.[NumberUnits]" & _
                                        ", tSale.[CostOut]" & _
                                        ", tSale.[Summ]" & _
" FROM ((([tMov_ClientInvoiceDetails] AS tSale" & _
" INNER JOIN tMov_Parties AS tParty ON tSale.KodParty = tParty.KodParty)" & _
" INNER JOIN tPrd_Goods AS PL ON tSale.GoodsID = PL.GoodsID)" & _
" INNER JOIN tPrd_GoodsData AS PN ON tSale.GoodsID = PN.GoodsID)" & _
" INNER JOIN WB_units_list AS RS ON tSale.ActualMU = RS.OurID" & _
" WHERE [lang_id] =" & cQUOTE & Par2 & cQUOTE & " And tSale.[KodParty] =" & Par1

Set rstB = CurrentDb.OpenRecordset(sSql)
    

    Do While Not rstB.EOF
        MM = rstB("KodWork")
        Debug.Print MM
    rstB.MoveNext
    Loop
    
End Function