compile error end of statement trying to concatenate two variables together 3

[vba317]

I have tried to get this code to work in a string SQL atatement with no luck. I am trying to concatenate two variables together within the SQL statement. Any help would be greatly appreciated.

Tom

Private Sub cmdXL_Click()
Dim sUCI As String
Dim sMon As String
Dim sCY As String
Dim sFileLoc As String
Dim sFile As String
Dim sSheet As String
Dim sFileType As String
Dim iRw As Integer
Dim sSQL As String
Dim rst As DAO.Recordset
Dim I As Integer


'MSP_Assists
sUCI = "MSP"
Call CurMonYrL(sUCI, sMon, sCY)
sFileLoc = "\\salmfilesvr1\public\Client Services\AutoRpts\_RptSets\OTHER\MSP\Tools\"
sFile = "MSP_Assists"
sSheet = "Assists_All"
sFileType = ".xlt"
Call xlOpen(sFileLoc, sUCI, sFile, sSheet, sFileType)
Range("A2").Value = sMon & " " & sCY
iRw = 5
'Data Assists
'Original code

'SELECT DICT_Dpt.rptdpt AS Department, z_ProcessPds.monstr AS BillMonth, DICT_Prov.rptprov AS Provider, [insdesc] & " (" & [insmne] & ")" AS Insurance, Sum(DATA_AssistData.Proc) AS Assists, Sum(DATA_AssistData.ChgAmt) AS Chgs, Sum(DATA_AssistData.WorkRVU) AS RVU
'FROM (((DATA_AssistData INNER JOIN z_ProcessPds ON DATA_AssistData.rptpd = z_ProcessPds.rptpd) INNER JOIN DICT_Dpt ON DATA_AssistData.Dpt = DICT_Dpt.dptid) INNER JOIN DICT_Prov ON DATA_AssistData.Prov = DICT_Prov.provid) INNER JOIN DICT_Ins ON DATA_AssistData.PrimInsMne = DICT_Ins.insmne
'WHERE (((z_ProcessPds.rptpddiff) = 1))
'GROUP BY DICT_Dpt.rptdpt, z_ProcessPds.monstr, DICT_Prov.rptprov, [insdesc] & " (" & [insmne] & ")"
'ORDER BY DICT_Dpt.rptdpt, DICT_Prov.rptprov, [insdesc] & " (" & [insmne] & ")";


     sSQL = "SELECT DICT_Dpt.rptdpt AS Department, z_ProcessPds.mons AS BillMonth, DICT_Prov.rptprov AS Provider, [DICT_Ins.insdesc] & [DICT_Ins.insmne] AS Insurance, " & _
            "Sum(DATA_AssistData.Proc) AS Assists, Sum(DATA_AssistData.ChgAmt) AS Chgs, Sum(DATA_AssistData.WorkRVU) AS RVU " & _
            "FROM DATA_AssistData " & _
            "INNER JOIN z_ProcessPds ON DATA_AssistData.rptpd = z_ProcessPds.rptpd " & _
            "INNER JOIN DICT_Dpt ON DATA_AssistData.Dpt = DICT_Dpt.dptid " & _
            "INNER JOIN DICT_Prov ON DATA_AssistData.Prov = DICT_Prov.provid " & _
            "INNER JOIN DICT_Ins ON DATA_AssistData.PrimInsMne = DICT_Ins.insmne " & _
            "WHERE z_ProcessPds.rptpddiff = 1) " & _
            "GROUP BY DICT_Dpt.rptdpt, z_ProcessPds.mons, DICT_Prov.rptprov,  [DICT_Ins.insdesc] &  [DICT_Ins.insmne] AS Insurance & _
            "ORDER BY DICT_Dpt.rptdpt, DICT_Prov.rptprov," & ""'" & [DICT_Ins.insdesc] & ""'" & [DICT_Ins.insmne] AS Insurance  & ";""""
Debug.Print sSQL
 Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
    If Not rst.EOF Then
        With rst
            .MoveLast
            .MoveFirst
        End With
    End If
    For I = 1 To rst.RecordCount
    With goXl.ActiveSheet
         .Cells(iRw, 1).Value = (rst![ProvName])
         .Cells(iRw + 1, 2).Value = (rst![Proc])
         .Cells(iRw + 1, 3).Value = (rst![ChgAmt])
         .Cells(iRw + 1, 4).Value = (rst![WorkRVU])
    End With
    iRw = iRw + 1
    rst.MoveNext
    Next I
    
    MsgBox "WHOO! Source files are created.", , "Completed."

End Sub

 

[dhookom]

You have:

Debug.Print sSQL

but don't provide the results to us. Can you help us up by providing the sSQL?

Can you explain the ORDER BY clause?

"ORDER BY DICT_Dpt.rptdpt, DICT_Prov.rptprov," & ""'" & [DICT_Ins.insdesc] & ""'" & [DICT_Ins.insmne] AS Insurance  & ";""""

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

With your original SQL code:

strSQL = "SELECT DICT_Dpt.rptdpt AS Department, z_ProcessPds.monstr AS BillMonth, DICT_Prov.rptprov AS Provider, [insdesc] & "" ("" & [insmne] & "")"" AS Insurance, Sum(DATA_AssistData.Proc) AS Assists, Sum(DATA_AssistData.ChgAmt) AS Chgs, Sum(DATA_AssistData.WorkRVU) AS RVU" _
       & " FROM (((DATA_AssistData INNER JOIN z_ProcessPds ON DATA_AssistData.rptpd = z_ProcessPds.rptpd) INNER JOIN DICT_Dpt ON DATA_AssistData.Dpt = DICT_Dpt.dptid) INNER JOIN DICT_Prov ON DATA_AssistData.Prov = DICT_Prov.provid) INNER JOIN DICT_Ins ON DATA_AssistData.PrimInsMne = DICT_Ins.insmne" _
       & " WHERE (((z_ProcessPds.rptpddiff) = 1))" _
       & " GROUP BY DICT_Dpt.rptdpt, z_ProcessPds.monstr, DICT_Prov.rptprov, [insdesc] & "" ("" & [insmne] & "")""" _
       & " ORDER BY DICT_Dpt.rptdpt, DICT_Prov.rptprov, [insdesc] & "" ("" & [insmne] & "")"";"

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

 

[dhookom]

Does this code provide the results in need?

"ORDER BY DICT_Dpt.rptdpt, DICT_Prov.rptprov, DICT_Ins.insdesc & DICT_Ins.insmne"

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

And now, a porre readable way:

strSQL = "SELECT D.rptdpt AS Department, z.monstr AS BillMonth, P.rptprov AS Provider, I.insdesc & ' (' & I.insmne & ')' AS Insurance" _
       & ", Sum(A.Proc) AS Assists, Sum(A.ChgAmt) AS Chgs, Sum(A.WorkRVU) AS RVU" _
       & "  FROM (((DATA_AssistData A INNER JOIN z_ProcessPds z ON A.rptpd=z.rptpd) INNER JOIN DICT_Dpt D ON A.Dpt=D.dptid)" _
       & " INNER JOIN DICT_Prov P ON A.Prov=P.provid) INNER JOIN DICT_Ins I ON A.PrimInsMne=I.insmne" _
       & " WHERE z.rptpddiff=1" _
       & " GROUP BY D.rptdpt, z.monstr, P.rptprov, I.insdesc, I.insmne" _
       & " ORDER BY D.rptdpt, z.monstr, P.rptprov, I.insdesc, I.insmne"

FYI, in your VBA code the GROUP BY and ORDER BY clauses are bad written.

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

 

[Andrzejek]

It would be nice to know which "two variables" you have in mind?
If you mean: [tt][DICT_Ins.insdesc] & [DICT_Ins.insmne][/tt], those look like fields, not variables.

And how come you have:[tt]
GROUP BY ... [red]AS Insurance[/red]
ORDER BY ... [red]AS Insurance[/red][/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[vba317]

Thank you both PHV and Duane for your help. I can get past the sSQL statement now, now I get a runtime error 3265 Item not found in this collection the error point is highlighted in Blue.
I checked the table DATA_AssistData and it has information in all the fields.
I added the aliases that PHV did to the recordset properties.
I checked the INNER JOIN DICT_Prov P ON A.Prov=P.provid) and this look correct.
Any thought?

Tom

Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
    If Not rst.EOF Then
        With rst
            .MoveLast
            .MoveFirst
        End With
    End If
    For I = 1 To rst.RecordCount
    With goXl.ActiveSheet
  [Blue] .Cells(iRw, 1).Value = (rst![P.rptprov]) [/Blue]
         .Cells(iRw + 1, 2).Value = (rst![A.Proc])
         .Cells(iRw + 1, 3).Value = (rst![A.ChgAmt])
         .Cells(iRw + 1, 4).Value = (rst![A.WorkRVU])
    End With
    iRw = iRw + 1
    rst.MoveNext

 

[vba317]

Here is the results of the sSQL statement:


?sSQL
SELECT D.rptdpt AS Department, z.monstr AS BillMonth, P.rptprov AS Provider, I.insdesc & ' (' & I.insmne & ')' AS Insurance, Sum(A.Proc) AS Assists, Sum(A.ChgAmt) AS Chgs, Sum(A.WorkRVU) AS RVU FROM (((DATA_AssistData A INNER JOIN z_ProcessPds z ON A.rptpd=z.rptpd) INNER JOIN DICT_Dpt D ON A.Dpt=D.dptid) INNER JOIN DICT_Prov P ON A.Prov=P.provid) INNER JOIN DICT_Ins I ON A.PrimInsMne=I.insmne WHERE z.rptpddiff=1 GROUP BY D.rptdpt, z.monstr, P.rptprov, I.insdesc, I.insmne ORDER BY D.rptdpt, z.monstr, P.rptprov, I.insdesc, I.insmne

 

[PHV]

You have to use the aliases in the SELECT list.
So replace this:
.Cells(iRw, 1).Value = (rst![P.rptprov])
with this:
.Cells(iRw, 1).Value = (rst![Provider])
and so on.

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

 

[Andrzejek]

You are going to have the same problem with :

    With goXl.ActiveSheet
   .Cells(iRw, 1).Value = (rst![Provider]) 
         .Cells(iRw + 1, 2).Value = (rst![red][A.Proc])[/red]
         .Cells(iRw + 1, 3).Value = (rst![red][A.ChgAmt])[/red]
         .Cells(iRw + 1, 4).Value = (rst![red][A.WorkRVU])[/red]
    End With

Use:

    With goXl.ActiveSheet
   .Cells(iRw, 1).Value = (rst!Provider.Value) 
         .Cells(iRw + 1, 2).Value = (rst![red]Assists[/red].Value)
         .Cells(iRw + 1, 3).Value = (rst![red]Chgs[/red].Value)
         .Cells(iRw + 1, 4).Value = (rst![red]RVU[/red].Value)
    End With

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[vba317]

Thanks Everyone this part of the program is working now. Now I just have to write 25 more queries and figure out how to append those sheets to existing reports. Thanks for all your help.

Tom