Trying to convert crosstab query to VBA 2

[vba317]

I am getting an Error 3141 select statement includes a reserved word or an argument name.
I am trying to rewrite an crosstab query into an strSQL statement this is my first attempt doing this. I keep on getting this error, I have tried changing the order of the statements with no luck. Any help is appreciated. When I run the query it outputs the following data:Col 1 - uci, Col 2 provname, Col3 -16 are the billpd.
The error is highlighted in Red
Original crosstab query

TRANSFORM Sum(PROC_ReadingProvider.proc) AS SumOfproc
SELECT PROC_ReadingProvider.uci, PROC_ReadingProvider.readingprovname
FROM PROC_ReadingProvider
WHERE (((PROC_ReadingProvider.billpd)>357))
GROUP BY PROC_ReadingProvider.uci, PROC_ReadingProvider.readingprovname
ORDER BY PROC_ReadingProvider.readingprovname, PROC_ReadingProvider.billpd
PIVOT PROC_ReadingProvider.billpd;

Converted code

Public Function GetData()
Dim strSQL As String
Dim Z As Integer
Dim rst As Recordset
Dim iRow As Integer


strSQL = "SELECT tbl_RegPr.uci as 1, tbl_RegPr.readingprovname as 2 ,tbl_RegPr.billpd as 3,tbl_RegPR.billpd as 4" & _
        "tbl_RegPr.billpd as 5, tbl_RegPr.billpd as 6, tbl_RegPr.billpd as 7, tbl_RegPr.billpd as 8, tbl_RegPr.billpd as 9," & _
        "tbl_RegPr.billpd as 10, tbl_RegPr.billpd as 11, tbl_RegPr.billpd as 12, tbl_RegPr.billpd as 13, tbl_RegPr.billpd as 14, " & _
        "FROM PROC_ReadingProvider tbl_RegPr  " & _
        "GROUP BY tbl_RegPr.uci, tbl_RegPr.readingprovname,tbl_RegPr.billpd " & _
        "ORDER BY tbl_RegPr.readingprovname, tbl_RegPr.billpd; "

[Red]  Set rst = CurrentDb.OpenRecordset(strSQL, dbopensnapshot)[/red]
    'Verify the recordcount counter is set at record 1
    If (rst.RecordCount > 0) Then
        With rst
            .MoveLast
            .MoveFirst
        End With
iRow = 5
 'Counter to loop through all records
        For Z = 1 To rst.RecordCount
            With goXL.ActiveSheet
                    .Cells(iRow, 1) = rst![1]
                    .Cells(iRow, 2) = rst![2]
                    .Cells(iRow, 3) = rst![3]
                    .Cells(iRow, 4) = rst![4]
                    .Cells(iRow, 5) = rst![5]
                    .Cells(iRow, 6) = rst![6]
                    .Cells(iRow, 7) = rst![7]
                    .Cells(iRow, 8) = rst![8]
                    .Cells(iRow, 9) = rst![9]
                    .Cells(iRow, 10) = rst![10]
                    .Cells(iRow, 11) = rst![11]
                    .Cells(iRow, 12) = rst![12]
                    .Cells(iRow, 13) = rst![13]
                    .Cells(iRow, 14) = rst![14]
                    .Cells(iRow, 15) = rst![15]
                    .Cells(iRow, 16) = rst![16]                   
                End With
            iRow = iRow + 1
            rst.MoveNext
            
        Next
        
  End If
End Function

 

[dhookom]

I assume goXL is a global reference to an Excel workbook.

I'm not sure your coded SQL statement has any relation to the crosstab SQL. However you are missing a comma after the 4 and have an extra comma after the 14.

strSQL = "SELECT tbl_RegPr.uci as 1, tbl_RegPr.readingprovname as 2 ,tbl_RegPr.billpd as 3,tbl_RegPR.billpd as 4[red][b][highlight #FCE94F],[/highlight] [/b][/red]" & _
        "tbl_RegPr.billpd as 5, tbl_RegPr.billpd as 6, tbl_RegPr.billpd as 7, tbl_RegPr.billpd as 8, tbl_RegPr.billpd as 9," & _
        "tbl_RegPr.billpd as 10, tbl_RegPr.billpd as 11, tbl_RegPr.billpd as 12, tbl_RegPr.billpd as 13, tbl_RegPr.billpd as 14[b][s][red][highlight #FCE94F],[/highlight][/red][/s] [/b]" & _
        "FROM PROC_ReadingProvider tbl_RegPr  " & _
        "GROUP BY tbl_RegPr.uci, tbl_RegPr.readingprovname,tbl_RegPr.billpd " & _
        "ORDER BY tbl_RegPr.readingprovname, tbl_RegPr.billpd; "

Why not something like:

strSQL = "TRANSFORM Sum(proc) AS SumOfproc " & _
    "SELECT uci, readingprovname " & _
    "FROM PROC_ReadingProvider " & _
    "WHERE billpd>357 " & _
    "GROUP BY uci, readingprovname " & _
    "ORDER BY readingprovname, billpd " & _
    "PIVOT billpd; "

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

Duane,
Yes goXl is as follows:
Public goXL As Excel.Application ' The Excel Object variable

I thought that I had to remove transform and pivot. I'm glad I don't. I have inserted your code and now I get a runtime error 3265 Item not in collection. The error occurs here so progress is being made.

.Cells(iRow, 1) = rst![1]

Any suggestions?

Tom

 

[Golom]

You are missing a comma and a space after [blue]billpd as 4[/blue] and you have a spurious comma after [blue]billpd as 14[/blue]

Would this be easier?

Public Sub GetData()
Dim strSQL As String
Dim db     As DAO.Database
Dim rst    As DAO.Recordset
Dim iRow   As Integer
Dim iCol   As Integer

Set db = CurrentDb

iRow = 5
strSQL = "SELECT uci, readingprovname as [rp], billpd " & _
         "FROM PROC_ReadingProvider " & _
         "GROUP BY uci, readingprovname, billpd " & _
         "ORDER BY readingprovname, billpd; "

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Loop through all records
Do Until rst.EOF
    With goXL.ActiveSheet
        .Cells(iRow, 1) = rst![uci]
        .Cells(iRow, 2) = rst![rp]
        For iCol = 3 to 16
            .Cells(iRow, iCol) = rst![billpd]
        Next i
    End With
    iRow = iRow + 1
    rst.MoveNext
Loop

Set rst = Nothing
Set db = Nothing
        
End Sub

 

[vba317]

Golom,
Your code is simpler and easier to understand. I also don't get any errors. But I am not getting the correct data. The first two columns uci and readingprovname are correct. In Columns 3-16 I am getting the billpd in the data fields instead of the column headings. Any suggestions?

Tom

 

[dhookom]

If you have "WHERE billpd>357" and then pivot on the billpd column, you will never have rst![1] since 1 is less than 357. If you use a crosstab, you should also provide the Column Headings property and possibly prefix with a alpha character like:

strSQL = "TRANSFORM Sum(proc) AS SumOfproc " & _
    "SELECT uci, readingprovname " & _
    "FROM PROC_ReadingProvider " & _
    "WHERE billpd>357 " & _
    "GROUP BY uci, readingprovname " & _
    "ORDER BY readingprovname, billpd " & _
    "PIVOT 'P' & billpd IN ('P358','P359', etc ) "

Golom's solution seems to be missing the Sum(Proc) which is the value you are most interested in.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd replace this:
For Z = 1 To rst.RecordCount
With goXL.ActiveSheet
.Cells(iRow, 1) = rst![1]
...
.Cells(iRow, 16) = rst![16]
End With
iRow = iRow + 1
rst.MoveNext
Next
with simply this:
goXL.ActiveSheet.Range("A5").CopyFromRecordset rst

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

 

[vba317]

Duane,
I have tried to implement your solution but now I am getting an error 3143 Syntax error in the transform statement.

iCurMon = 370
        
strSQL = "TRANSFORM Sum(proc) AS SumOfproc " & _
    "SELECT uci, readingprovname " & _
    "FROM PROC_ReadingProvider " & _
    "WHERE billpd>357 " & _
    "GROUP BY uci, readingprovname " & _
    "ORDER BY readingprovname, billpd " & _
    "PIVOT 'P' & billpd IN (iCurMon-12,iCurMon-11,iCurMon-10,iCurMon-9,)" & _
    "(iCurMon-8,iCurMon-7,iCurMon-6,iCurMon-5,)" & _
    "(iCurMon-4,iCurMon-3, iCurMon-2,iCurMon-1); "

 

[PHV]

Again, why not simply this ?

Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = "TRANSFORM Sum(proc) AS SumOfproc " & _
         "SELECT uci, readingprovname " & _
         "FROM PROC_ReadingProvider " & _
         "WHERE billpd>357 " & _
         "GROUP BY uci, readingprovname " & _
         "PIVOT billpd"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If Not (rst.BOF Or rst.Edit) Then
    goXL.ActiveSheet.Range("A5").CopyFromRecordset rst
End If
rst.Close

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

 

[vba317]

PHV I have tried your solution I get a compile error at rst.Edit. Any suggestions?

Tom

 

[dhookom]

vba317,
No, you didn't implement or understand my solution.
Your code doesn't include the 'P' inside the IN () like mine did. There should be only one set of ()s with all the column heading values between. You've also introduced iCurMon which we don't have a clue as to the value.

  "PIVOT 'P' & billpd IN (iCurMon-12,iCurMon-11,iCurMon-10,iCurMon-9,)" & _
    "(iCurMon-8,iCurMon-7,iCurMon-6,iCurMon-5,)" & _
    "(iCurMon-4,iCurMon-3, iCurMon-2,iCurMon-1); "

Why don't you attempt the much simpler solution PH has provided? If you did try it then at least extend the courtesy of telling us the results.


Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

rst.Edit?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

PH's code should be:

If Not (rst.BOF Or rst.EOF) Then

He has used up his one mistake for the year ;-)

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

Thanks everyone for your help. I did get confused trying everyone's solutions and trying to fix them. The query works and is really fast.

Tom

 

[SkipVought]

remove the parentheses CHUNKS! Only ONE parenthesesd pare for an IN statement

strSQL = "TRANSFORM Sum(proc) AS SumOfproc " & _
    "SELECT uci, readingprovname " & _
    "FROM PROC_ReadingProvider " & _
    "WHERE billpd>357 " & _
    "GROUP BY uci, readingprovname " & _
    "ORDER BY readingprovname, billpd " & _
    "PIVOT 'P' & billpd IN (iCurMon-12,iCurMon-11,iCurMon-10,iCurMon-9," & _
    "iCurMon-8,iCurMon-7,iCurMon-6,iCurMon-5," & _
    "iCurMon-4,iCurMon-3, iCurMon-2,iCurMon-1); "

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]