Reports output in Excel

[Gasman]

I have double checked that Option Compare Database and Option Explicit are at the start of the module - still nothing.

I am now going to try by replacing the queries with new ones, just in case they are corrupt.

Cannot see them being corrupt, they run from the form fine.

I assume your DB is 2007?

 

[Gasman]

Need to grab something to eat. Be about 30mins

 

[fat controller]

Sorry, do you mean take out the Option Compare Database line at the top of the module, leaving only the Option Explicit?

Oddly enough, I was just having a google about, and arrived at the conclusion that it was a reference problem - it appeared to be Microsoft DAO Objects Library 3.6, but putting that in saw it conflict with another one (Office 14 Database objects I think) - subsequently removing that reference and substituting DAO 3.6 just caused other bits to fall over, so I put it all back.

I have tried a new query, but the result is the same.

I don't know if I have used DAO recordsets before, so the answer to that is probably no.

 

[fat controller]

Need to grab something to eat. Be about 30mins

Cheers - I have just wrapped my face around a meringue that Mrs C made earlier. Really nice - but the sugar crash later is going to be one hell of a thud

And to answer an earlier question, my database is 2010.

 

[Gasman]

No leave them both in.

I have Microsoft DAO Objects Library 3.6 loaded, but not the other, but I am on Access 2003. What is required for what, is out of my league at present.

I believe you can however have both and move them for priority. So you could try 3.6 above Office 14.?

The end result of all this to & fro.

1. With the Option Explicit we have made sure nothing has been mistyped.
2. There is something wrong with the line that opens the record set for the query.
3. We need a more experienced person to assist.

I also think you will have problems saving the file in respect of building the name. It would not work in 2003 and I changed the line to be

xlWBk.SaveAs "C:\Users\Russell\Documents\Payroll Test\Template\FourWeeksEnding_" & Format(Now(), "yyyymmdd") & ".xlsx", 51

This would then give FourWeeksEnding_20150329 in the requested folder.

However one problem at a time. ;-)

I'm sorry I cannot help anymore, but will be keeping tabs on this thread to see the eventual fix.

Sorry, do you mean take out the Option Compare Database line at the top of the module, leaving only the Option Explicit?

Oddly enough, I was just having a google about, and arrived at the conclusion that it was a reference problem - it appeared to be Microsoft DAO Objects Library 3.6, but putting that in saw it conflict with another one (Office 14 Database objects I think) - subsequently removing that reference and substituting DAO 3.6 just caused other bits to fall over, so I put it all back.

I have tried a new query, but the result is the same.

I don't know if I have used DAO recordsets before, so the answer to that is probably no.

 

[fat controller]

Thanks for your help We are nudging ever closer, and no doubt Gina will have the answer.

 

[GinaWhipp]

Well, I see you are getting help so I'll just peek every once in a while to make sure everything goes okay...

 

[fat controller]

Well, I see you are getting help so I'll just peek every once in a while to make sure everything goes okay...

Your timing is perfect

Gasman was able to use your code to get a query out to a spreadsheet, however I cannot get it to run for the life of me :banghead:

I get an error 3601 Too few parameters. Expected1

Am I missing a reference library?

 

[GinaWhipp]

No that is not the problem, does the query require a parameter (criteria) to run?

 

[fat controller]

No that is not the problem, does the query require a parameter (criteria) to run?

Yes, that parameter is supplied by the form that has the command to run the query.

I have tried opening the queries ahead of calling the function, and they both open fine and display the correct records.

I have just tried a quick check to export the query to a blank excel sheet, and that works fine.

The only other parameters in the query are two fields that have the criteria 'Is Not Null'

 

[GinaWhipp]

Right, it will yield that error. In order to use a parameter query you need to create a QueryDef (temporary query) and then export the results.

 

[fat controller]

Right, it will yield that error. In order to use a parameter query you need to create a QueryDef (temporary query) and then export the results.

OK - sounds like I have a bit of Googling to do. Is it a difficult process?

 

[GinaWhipp]

Hmm, not hard, at least to me. If you provide the SQL for the query I can give you a bit of a start...

 

[Gasman]

Gina,

May I jump in with a question please?

Let us say FC has a date parameter called ReqDate in his query and that is compared against Orderdate.

What is the benefit of adding ReqDate to the Parameter list of the query as opposed to just entering in the GUI criteria.?

I've just added a parameter to my test query and at present cannot see the benefit of adding to the Parameter list when it still needs to be entered in the GUI criteria to generate the correct SQL code. What am I missing please.?

TIA

 

[fat controller]

Hmm, not hard, at least to me. If you provide the SQL for the query I can give you a bit of a start...

You are one in a million, thank you

SELECT [4WeeklyPayroll].ID, [4WeeklyPayroll].[4WeeksCommencing], [4WeeklyPayroll].ControllerName, [4WeeklyPayroll].W1AHDays, [4WeeklyPayroll].W1SKDays, [4WeeklyPayroll].W2AHDays, [4WeeklyPayroll].W2SKDays, [4WeeklyPayroll].W3AHDays, [4WeeklyPayroll].W3SKDays, [4WeeklyPayroll].W4AHDays, [4WeeklyPayroll].W4SKDays, [4WeeklyPayroll].EmployeeNumber, [4WeeklyPayroll].W1SatEnhTotal, [4WeeklyPayroll].[W1M-FEnhTotal], [4WeeklyPayroll].W2SatEnhTotal, [4WeeklyPayroll].[W2M-FEnhTotal], [4WeeklyPayroll].W3SatEnhTotal, [4WeeklyPayroll].[W3M-FEnhTotal], [4WeeklyPayroll].W4SatEnhTotal, [4WeeklyPayroll].[W4M-FEnhTotal], [4WeeklyPayroll].W1SatOTHrs, [4WeeklyPayroll].W1SatOTMins, [4WeeklyPayroll].W1MFOTHrs, [4WeeklyPayroll].W1MFOTMins, [4WeeklyPayroll].W2SatOTHrs, [4WeeklyPayroll].W2SatOTMins, [4WeeklyPayroll].W2MFOTHrs, [4WeeklyPayroll].W2MFOTMins, [4WeeklyPayroll].W3SatOTHrs, [4WeeklyPayroll].W3SatOTMins, [4WeeklyPayroll].W3MFOTHrs, [4WeeklyPayroll].W3MFOTMins, [4WeeklyPayroll].W4SatOTHrs, [4WeeklyPayroll].W4SatOTMins, [4WeeklyPayroll].W4MFOTHrs, [4WeeklyPayroll].W4MFOTMins, [4WeeklyPayroll].W1SATWRDNum, [4WeeklyPayroll].W1MFWRDNum, [4WeeklyPayroll].W2SATWRDNum, [4WeeklyPayroll].W2MFWRDNum, [4WeeklyPayroll].W3SATWRDNum, [4WeeklyPayroll].W3MFWRDNum, [4WeeklyPayroll].W4SATWRDNum, [4WeeklyPayroll].W4MFWRDNum, [4WeeklyPayroll].SubmissionDate, [4WeeklyPayroll].[SDM/OMApprovalDate]
FROM 4WeeklyPayroll
WHERE ((([4WeeklyPayroll].[4WeeksCommencing])=[Forms]![PayrollPeriodExport]![txtPerDate]) AND (([4WeeklyPayroll].SubmissionDate) Is Not Null) AND (([4WeeklyPayroll].[SDM/OMApprovalDate]) Is Not Null));

That is the entire SQL for one of the queries - the other query is very similar, and in fact the WHERE criteria are exactly the same.

Cheers

 

[Gasman]

FC,

I have managed to do it in this way, now that Gina has found the error we had last night.

Add a line for the qdf declaration

Dim qdf As DAO.QueryDef

            strPath = "C:\Temp\AccessExport.xls"
            
            [B]Set qdf = CurrentDb.QueryDefs(strTQName)
            qdf![Forms!frmTestExport!txtPerDate] = [Forms]![frmTestExport]![txtPerDate]
        
            Set rst = qdf.OpenRecordset()[/B]
            Set ApXL = CreateObject("Excel.Application")

You need to replace my formname frmTestExport with your PayrollPeriodExport
Notice the format for the left hand side of the =. Changed code is in bold

There might be other formats available, but I've got that one to work.

This way you do not need to amend the query at all.

I must admit I've learnt a fair bit from this post.

 

[GinaWhipp]

@Gasman

It's not a benefit... you cannot Export to Excel using a parameter query, it does not work. You must create a QueryDef on the fly and export the QueryDef and then drop it.

 

[GinaWhipp]

Heh Gasman... doing my job for me!

Glad you could pick my slack...

 

[Gasman]

Heh Gasman... doing my job for me!

Glad you could pick my slack...

Well that is one way, but I'm sure there are others.?

I was wondering how you would go about making this generic so that any query could be used.?

You have parameterised the query name and sheet name and I dare say we could do the same for the template name, but just in general, how would one cope with different parameters in different queries? One might only have one as this one does, but another might have two and be completely different like an age or post code (zip code).

How would you approach that problem?

TIA

 

[GinaWhipp]

Have a look at...

         Dim dbs As DAO.Database
        Dim qryDef As DAO.QueryDef
        Dim strSQL As String
        Dim strWhere As String
        Dim lngLen As Long
        Set dbs = CurrentDb
    
    strSQL = "SELECT tblCoilReceiving.crCoilReceivingNumber, tblCoilReceiving.crCoilReceivedDate, tblCoilReceiving.crStatusID, " & _
              "tblCoilReceiving.crCustomerCoilID, tblCoilReceiving.crSalesOrderNumber, tblCoilReceiving.crCustomerID, tblCoilReceiving.crCoilID, tblCoilReceiving.crCoilListNet, " & _
                "tblCoilReceiving.crTicketNumber, tblCoilReceiving.crCoilLotNumber, tblAlloyDensity.adDensity, tblCoilReceiving.crAlloyID, " & _
                 "tblCoilReceiving.crTemperID, tblCoilReceiving.crCoilListGauge, tblCoilReceiving.crCoilListWidth, " & _
                  "tblCoilReceiving.crComments, IIf([crNewCoilID] Is Null, Null,'Rewound') AS CoilRewound FROM (tblAlloyDensity " & _
                   "RIGHT JOIN tblCoilReceiving ON tblAlloyDensity.adAlloyID = tblCoilReceiving.crAlloyID) " & _
                    "LEFT JOIN qryRewoundCoils ON tblCoilReceiving.crCoilID = qryRewoundCoils.crNewCoilID"
     'Number
    If Not IsNull(Me.cboCustomerID) Then
        strWhere = strWhere & "([crCustomerID] = " & Me.cboCustomerID & ") AND "
    End If
    
    'Date
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([crCoilReceivedDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If
     If Not IsNull(Me.txtEndDate) Then
        strWhere = strWhere & "([crCoilReceivedDate] <= " & Format(Me.txtEndDate, conJetDate) & ") AND "
    End If
    
    lngLen = Len(strWhere) - 5
    
    If lngLen <= 0 Then
        strSQL = strSQL
        Set qryDef = dbs.CreateQueryDef("CoilReceiveCheck", strSQL)
        'DoCmd.OpenQuery qryDef.Name
        qryDef.Close
        Set qryDef = Nothing
        DoEvents
        Call SendToExcel("CoilReceiveCheck", "Sheet1")
        DoEvents
        DoCmd.DeleteObject acQuery, "CoilReceiveCheck"
    Else
        strWhere = Left$(strWhere, lngLen)
    
        strSQL = strSQL & " WHERE " & strWhere
        Set qryDef = dbs.CreateQueryDef("CoilReceiveCheck", strSQL)
        'DoCmd.OpenQuery qryDef.Name
        qryDef.Close
        Set qryDef = Nothing
        DoEvents
        Call SendToExcel("CoilReceiveCheck", "Sheet1")
        DoEvents
        DoCmd.DeleteObject acQuery, "CoilReceiveCheck"
    End If
    
        dbs.Close
        Set dbs = Nothing

You would use the above to create your QueryDef and then then use the previous code to send to Excel. Now you could add as many parameters to your Form as you like OR even add another query to go to another Worksheet.