Exporting Access Query to Excel 3

[SHAWTY721]

I am trying to send the results of a query that runs when a button is clicked on a form. Originally I used teh DoCmd.TransferSpreadsheet to export the information but I was informed that by using the DoCmd.TransferSpreadsheet method you are unable to dictate which fields records should go to so I created a Public Function. I receive this error when I click the button: 'Run-time error ‘3061’:
To few parameters. Expected 4.'

This is what my function looks like:
Public Function ExportQuery() As String
On Error GoTo err_Handler

'Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim IRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabOne As Byte = 1
Const cTabTwo As Byte = 2
Const cStartRow As Byte = 3
Const cStartColumn As Byte = 1

DoCmd.Hourglass True

'Set to break on all errors
Application.SetOption "Error Trapping", 0

'Start with clean file built from template file
sTemplate = CurrentProject.Path & "\JournalEntryTest.xls"
sOutput = CurrentProject.Path & "\JournalEntryFormTest.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput

'Create the Excel Application, Workbook and Worksheet and Database object
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open(sOutput)

sSQL = "SELECT * FROM qryJEtest"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
If Not rst.BOF Then
rst.MoveFirst
'For this template, the data must be placed in the appropriate cells of the spreadsheet
Do While Not rst.EOF
With wbk
.Sheets("JournalEntry").Range("G3") = rst.Fields("Branch Number")
.Sheets("JournalEntry").Range("K15") = rst.Fields("Account")
.Sheets("JournalEntry").Range("L15") = rst.Fields("Sub Account")
.Sheets("JournalEntry").Range("O15") = rst.Fields("SUMOfGROSS")
.Sheets("JournalEntry").Range("Q15") = rst.Fields("Account Description")
.Sheets("JournalEntry").Range("G3,K15,L15,O15,Q15").Columns.AutoFit
.SaveAs CurrentProject.Path & "\" & rst.Fields("Branch Number&""&Description") & " .xls"
End With
rst.MoveNext
Loop
rst.Close

ExportQuery = "Total of " & IRecords & " rows processed."

exit_Here:
'Cleanup all objects (resume next on errors)
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

err_Handler:
ExportQuery = Err.Description
Resume exit_Here
End If
End Function

 

[JerryKlmns]

Inside to Do ... Loop add
.Cells(lRecords + 15, newColumnNumber).Value = lRecords + 1
before . MoveNext

 

[SHAWTY721]

When I use this line of code I get this error message "Compile Error: Invalid or unqualified reference"
On this line of code:
.Cells(IRecords + 15, newColumnNumber).Value = IRecords + 1

 

[JerryKlmns]

newColumnNumber should be the number of the column where you want to add "... the number of entries that are produced by the query

 

[SHAWTY721]

The query that creates these results produces this output and I want to be able to get the sum of a field based on the Account & Subaccount matching. Here is what the query result looks like.
Account Subaccount AcctDesc SumOfGross Check Date
60110 0100 ...... 4160 9/7/2007
60110 0100 ...... 4160 9/21/2007
60810 0900 ...... 842 9/21/2007
60810 0900 ...... 843.5 9/7/2007

This is the results that appear on the excel spreadsheet:
Account Subaccount AcctDesc SumOfGross
60110 0100 ...... 8320
60810 0900 ...... 1684
60810 0900 ...... 1687

I would like to have the result look like this in my spreadsheet
Account Subaccount AcctDesc SumOfGross
60110 0100 ...... 8320
60810 0900 ...... 1685.5

 

[lameid]

FYI ALL: thread701-1424254

sSQL = "Select A.GL_Acct, A.GL_Subacct, " & _
          "A.AccountDescription, Sum(TheGross) As TheGross" & _
          "From " & _
           "(SELECT E.GLDEPT, " & _
                  "GL.GL_Acct, " & _
                  "GL.GL_Subacct, " & _
                  "GL.GL_Dept, " & _
                  "GL.AccountDescription , " & _
                  "ADP.BranchNumber, " & _
                  "Sum(E.GROSS) As TheGross " & _
            "FROM tblAllADPCoCodes As ADP, " & _
                 "tblGLAllCodes As GL INNER JOIN " & _
                 "tblAllPerPayPeriodEarnings As E ON " & _
                 "GL.Dept = E.GLDEPT " & _
            "GROUP BY E.GLDEPT, " & _
                     "GL.GL_Acct, " & _
                     "GL.GL_Subacct, " & _
                     "GL.GL_Dept, " & _
                     "GL.AccountDescription, " & _
                     "E.PG, " & _
                     "E.[LOCATION#], " & _
                     "ADP.BranchNumber, " & _
                     "E.CHECK_DT " & _
            "HAVING PG = '" & strADPCompany & "' AND " & _
                   "E.[LOCATION#] = '" & strLocationNo & "' AND " & _
                   "ADP.BranchNumber = " & lBranchNo & " AND " & _
                   "E.CHECK_DT Between #" & dtFrom & "# AND " & _ 
             "#" & dtTo & "#) As A "  & _
             "Group By A.GL_Acct, A.GL_Subacct, " & _
             "A.AccountDescription"

I didn't see checkdate in the code glancing over it. If you are writing it to the spreadsheet, you also need to take it out.

 

[SHAWTY721]

So by incorporating this code I will be able to get the appropriate sum value that I need in the spreadsheet. I guess I am a little confused on what this code will do.

 

[lameid]

It Groups and sums the original query using the original query as a sub query.

 

[SHAWTY721]

Okay here my new SQL statement that I am using:

Now since I have removed the CHECK_DT from the GROUP BY section. The query looks like it is summing the Sum values of the query. Also one of the results is still showing twice one as being 1.5 over and the other being 1.5 under the actual number. I'm not sure what is going on with this. Here is what the new query looks like:

sSQL = "SELECT DISTINCT tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription , tblAllADPCoCodes.BranchNumber, Sum(tblAllPerPayPeriodEarnings.GROSS) As GROSS FROM tblAllADPCoCodes, tblGLAllCodes INNER JOIN tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept = tblAllPerPayPeriodEarnings.GLDEPT WHERE CHECK_DT Between #" & Forms("frmJE").Controls("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "# GROUP BY tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription, tblAllPerPayPeriodEarnings.PG, tblAllPerPayPeriodEarnings.[LOCATION#], tblAllADPCoCodes.BranchNumber HAVING [PG] = '" & [Forms]![frmJE]![cboADPCompany] & "' AND [LOCATION#] = '" & [Forms]![frmJE]![cboLocationNo] & "' AND BranchNumber = " & Forms("frmJE").Controls("txtBranchNo").Value & ";"[\Code]


Now the result looks like this
This is the results that appear on the excel spreadsheet:
Account     Subaccount    AcctDesc     SumOfGross
60110       0100          ......       16640   
60810       0900          ......       3371    

Where it look like it is adding the values that were created by the previous query. 

The results of this query should add up to be:
Account     Subaccount    AcctDesc     SumOfGross
60110       0100          ......       8320  
60810       0900          ......       1685.5

Does anyone know why I am not getting this result based on the query that is being used.

 

[lameid]

Did you by chance try the code that I posted last?

 

[SHAWTY721]

I receive this error message on the SQL Statement that I am working with and I can't figure out what is missing from the query expression {Run-time error ‘3075’ Syntax error (missing operator) in query expression ‘tblAllADPCoCodes.BranchNumber WHERE PG = ‘RYU’ AND [LOCATION#] = ‘63’ AND BranchNumber = 778 AND CHECK_DT Between #9/1/2007# AND #9/30/2007#’.}

Here is the SQL Statement
sSQL = "SELECT DISTINCT tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription , tblAllADPCoCodes.BranchNumber, Sum(tblAllPerPayPeriodEarnings.GROSS) As GROSS FROM tblAllADPCoCodes, tblGLAllCodes INNER JOIN tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept = tblAllPerPayPeriodEarnings.GLDEPT GROUP BY tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription, tblAllPerPayPeriodEarnings.PG, tblAllPerPayPeriodEarnings.[LOCATION#], tblAllADPCoCodes.BranchNumber WHERE PG = '" & Forms("frmJE").Controls("cboADPCompany").Value & "' AND [LOCATION#] = '" & Forms("frmJE").Controls("cboLocationNo").Value & "' AND BranchNumber = " & Forms("frmJE").Controls("txtBranchNo").Value & " AND CHECK_DT Between #" & Forms("frmJE").Controls("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "#" & ";"

 

[lameid]

The order of SQL clauses in a select statement:

Select
From
Where
Group By
Having
Order By

Your where clause should either be a having clause or be before the Group by.

However, as you may have noticed in me commenting in other posts, you are using a table without a join. Taking out criteria will give you a large number of results and in appropriately.

 

[SHAWTY721]

Is there any particular reason the results of the 'Sum(tblAllPerPayPeriodEarnings.GROSS) As Gross' are being doubled.


sSQL = "SELECT tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription , tblAllADPCoCodes.BranchNumber, Sum(tblAllPerPayPeriodEarnings.GROSS) As GROSS FROM tblAllADPCoCodes, tblGLAllCodes INNER JOIN tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept = tblAllPerPayPeriodEarnings.GLDEPT WHERE PG = '" & Forms("frmJE").Controls("cboADPCompany").Value & "' AND [LOCATION#] = '" & Forms("frmJE").Controls("cboLocationNo").Value & "' AND BranchNumber = " & Forms("frmJE").Controls("txtBranchNo").Value & " AND CHECK_DT Between #" & Forms("frmJE").Controls("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "# GROUP BY tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription, tblAllPerPayPeriodEarnings.PG, tblAllPerPayPeriodEarnings.[LOCATION#], tblAllADPCoCodes.BranchNumber ORDER BY tblGLAllCodes.GL_Acct;"

 

[PHV]

How many rows in tblAllADPCoCodes ?

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

 

[SHAWTY721]

I am trying to add a join for tblAllADPCoCodes to this code but I am receiving this error message.
{Compile error: Expected end of statement}
WHERE is being highlighted when this message appears.
I'm not real sure how to fix this issue.

sSQL = "SELECT tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription , tblAllADPCoCodes.BranchNumber, Sum(tblAllPerPayPeriodEarnings.GROSS) As GROSS FROM tblAllADPCoCodes, tblGLAllCodes INNER JOIN tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept = tblAllPerPayPeriodEarnings.GLDEPT, tblAllADPCoCodes.ADPCompany = tblAllPerPayPeriodEarnings.PG, tblAllADPCoCodes.[LOCATION#] = tblAllPerPayPeriodEarnings.[LOCATION#], _
WHERE PG = '" & Forms("frmJE").Controls("cboADPCompany").Value & "' AND [LOCATION#] = '" & Forms("frmJE").Controls("cboLocationNo").Value & "' AND BranchNumber = " & Forms("frmJE").Controls("txtBranchNo").Value & " AND CHECK_DT Between #" & Forms("frmJE").Controls("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "# GROUP BY tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription, _
tblAllPerPayPeriodEarnings.PG, tblAllPerPayPeriodEarnings.[LOCATION#], tblAllADPCoCodes.BranchNumber ORDER BY tblGLAllCodes.GL_Acct;

 

[lameid]

You can't use the line continuation underscore (_) to continue a string mid value. You have to close it and concatenate it together...

For example

strA = "Line 1 " & _
      "Line 2"

 

[SHAWTY721]

Okay now I am getting Compile Error: Expected: Expression by the ' after PG = line in the code.

 

[lameid]

Did you add your beginning double quote on "Line 2"?

 

[SHAWTY721]

I added the double quotes in front of the second line and now I am getting this error message:
{Run-time error '3131': Syntax error in FROM clause}.

sSQL = "SELECT tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription, tblAllADPCoCodes.BranchNumber, Sum(tblAllPerPayPeriodEarnings.GROSS) As GROSS FROM tblAllADPCoCodes, tblGLAllCodes INNER JOIN tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept = tblAllPerPayPeriodEarnings.GLDEPT, tblAllADPCoCodes.ADPCompany = tblAllPerPayPeriodEarnings.PG, tblAllADPCoCodes.LOCATION# = tblAllPerPayPeriodEarnings.LOCATION# " & _
"WHERE PG = '" & Forms("frmJE").Controls("cboADPCompany").Value & "' AND [LOCATION#] = '" & Forms("frmJE").Controls("cboLocationNo").Value & "' AND BranchNumber = " & Forms("frmJE").Controls("txtBranchNo").Value & " AND CHECK_DT Between #" & Forms("frmJE").Controls("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "# GROUP BY tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct " & _
"ORDER BY tblGLAllCodes.GL_Acct;

 

[SHAWTY721]

Now I am receiving 'Run-time error '3135': Syntax error in JOIN operation. After I have changed some things in the query I get this message. Here is what the updated SQL statement looks like.

sSQL = "SELECT tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct, tblGLAllCodes.GL_Dept, tblGLAllCodes.AccountDescription , tblAllADPCoCodes.BranchNumber, Sum(tblAllPerPayPeriodEarnings.GROSS) As GROSS FROM tblAllADPCoCodes, tblGLAllCodes INNER JOIN (tblAllPerPayPeriodEarnings ON tblGLAllCodes.Dept = tblAllPerPayPeriodEarnings.GLDEPT) INNER JOIN (tblAllPerPayPeriodEarnings ON tblAllADPCoCodes.ADPCompany = tblAllPerPayPeriodEarnings.PG) INNER JOIN (tblAllPerPayPeriodEarnings ON tblAllADPCoCodes.LocationNumber = tblAllPerPayPeriodEarnings.[LOCATION#]) WHERE  PG = '" & Forms("frmJE").Controls("cboADPCompany").Value & "' AND [LOCATION#] = '" & Forms("frmJE").Controls("cboLocationNo").Value & "' AND BranchNumber = " & Forms("frmJE").Controls("txtBranchNo").Value & " AND CHECK_DT Between #" & Forms("frmJE").Controls("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "# " & _
"GROUP BY tblAllPerPayPeriodEarnings.GLDEPT, tblGLAllCodes.GL_Acct, tblGLAllCodes.GL_Subacct ORDER BY tblGLAllCodes.GL_Acct;"
[\Code]

 

[lameid]

You have parenthesis in incorrect places in your from clause. You might use them in the ON piece or around a table name but never around the table name and on clause. It does not look like you need any parenthesis in your from clause, so just take them all out.