Issue Extracting Data to Excel from SQL Query

[BayEnder111]

Hi Guys,

I was wondering if you could help please. I'm trying to transfer from a Database Table --> Excel based on an Excel Template.

The data to extract is coming from a query called "qryCodeFix" in which I want to display certain fields based on what is selected on a form, the criteria used is:

Go-Live Date in SMPx Week x

SMP is basically a time period split down into weeks. E.g there would be SMP07 Week 1, 2, 3 & 4.

The qryCodeFix is as follows:

SELECT tblRI.[RI Number], tblRI.[RI Title], tblRI.Application, tblRI.[Start Date/Time], tblRI.[End Date/Time], tblRI.[Go-Live Date], tblRI.[Release Type?], tblRI.[RI Scope - Code Fix]
FROM tblRI
WHERE (((tblRI.[Go-Live Date])>[Week Start] And (tblRI.[Go-Live Date])<[Week End]));

As an example SMP07 Week 1 is between 15/09/2014 and 21/09/2014 however the SQL above will not work for the 15/09/2014 for some reason. When I run the query in Access it will however display info from the rest of the timeframe.

I'm trying to display the results in an Excel Template using the following VBA code:

Private Sub cmdpostrelease_Click()
    Dim xlApp As New Excel.Application
    Dim xlWrkBk As Excel.Workbook
    Dim xlSht As Excel.Worksheet
    Dim slaRec As DAO.Recordset
    Dim rowNo As Integer 'stores the row number to write data to
    rowNo = 2
    Dim DomainStart As String 'stores the start of the domain name
    Dim SMP As String
    Dim app As String
    Dim qdf As QueryDef
    Dim SMPStart As Date
    Dim Col As String
    'Obtain the Query to Use
    Set qdf = CurrentDb.QueryDefs("qryCodeFix")
    
    cmdpostrelease.Caption = "In Progress"
    
    ''''''''''''''''''''''
 'Ensure an SMP/Week is entered
    
    If txtweek.Value = "" Or IsNull(txtweek.Value) Or cmb_SMP.Value = "" Or IsNull(cmb_SMP.Value) Then
        MsgBox "You must enter a Week AND SMP to run the report", vbOKOnly, "Error"
    Else
        
    SMP = cmb_SMP.Column(0)
    
   '''''''''''''''''''''
    ' Get the SMP dates
    '''''''''''''''''''''
    Set smpRec = CurrentDb.OpenRecordset("tblSMP")
     smpRec.MoveFirst
     With smpRec
      Do While Not .EOF
        If CInt(smpRec.Fields("SMP#").Value) = CInt(cmb_SMP.Column(1)) Then
            SMPStart = smpRec.Fields("SMP Start Date") ' Start Date
            Exit Do
        Else
            .MoveNext
        End If
      Loop
      End With
    'calculate start and end dates
    qdf.Parameters("Week Start") = DateAdd("d", (Int(txtweek.Value) - 1) * 7, SMPStart)
    qdf.Parameters("Week End") = DateAdd("d", (Int(txtweek.Value) - 1) * 7, SMPStart) + 7
      'Set Template Location and Sheet names
    Set xlWrkBk = xlApp.Workbooks.Open(Application.CurrentProject.Path & "\Templates\Post Release Problem Cleanup.xlsx")
        Set xlShtCodeFix = xlWrkBk.Worksheets("Code Fix")
        Set xlShtDataRepair = xlWrkBk.Worksheets("Data Repair")
    
    xlApp.Application.DisplayAlerts = False
    xlApp.Application.ScreenUpdating = True
    
     ' Show spreadsheet on screen
    xlApp.Application.Visible = True
    
    'Clear the template fields to ensure no hangover from last report
        xlShtCodeFix.Range("A2", "H20") = ""
        xlShtDataRepair.Range("A2", "H20") = ""
    
    'Set the week number
     xlShtCodeFix.Range("A25").Value = Mid(SMP, 4, 2)
     xlShtCodeFix.Range("B25").Value = txtweek.Value
    'calculate start and end dates
    qdf.Parameters("Week Start") = Format(SMPStart, "dd/mm/yyyy")
    qdf.Parameters("Week End") = Format(SMPEnd, "dd/mm/yyyy")
    
    Set myRec = qdf.OpenRecordset
    If myRec.RecordCount <> 0 Then
         myRec.MoveFirst
         
         With myRec
           Do While Not .EOF
           
    'Update Code Fix Sheet
            xlShtCodeFix.Cells(rowNo, "A") = myRec.Fields("RI Number") ' RI Number
            xlShtCodeFix.Cells(rowNo, "B") = myRec.Fields("RI Title") ' RI Title
            xlShtCodeFix.Cells(rowNo, "C") = myRec.Fields("Application") ' Application
            xlShtCodeFix.Cells(rowNo, "D") = myRec.Fields("Start Date/Time") ' Start Date/Time
            xlShtCodeFix.Cells(rowNo, "E") = myRec.Fields("End Date/Time") ' End Date/Time
            xlShtCodeFix.Cells(rowNo, "F") = myRec.Fields("Go-Live Date") ' RI Status
            xlShtCodeFix.Cells(rowNo, "G") = myRec.Fields("Release Type?") ' Closure Requested?
            xlShtCodeFix.Cells(rowNo, "H") = myRec.Fields("RI Scope - Code Fix") ' Date closure Requested?
            rowNo = rowNo + 1
            .MoveNext
              
           Loop
        End With
    End If
    
    'Save Workbook and close Template
    xlWrkBk.SaveAs Application.CurrentProject.Path & "\Reports\" & "Post Release Problem Cleanup" & "_" & Format(Now, "yymmdd") & ".xlsx", FileFormat:=51
    xlWrkBk.Close SaveChanges:=False
    ' Turn prompting OFF and save the sheet with original name
    xlApp.Application.DisplayAlerts = True
    xlApp.Application.Quit
    
    ' Release objects
    Set xlSht = Nothing
    Set xlWrkBk = Nothing
    Set xlApp = Nothing
End If
End Sub

However when I select SMP07 Week 1 run the code it will not display any information - it doesn't give any errors when running and saves the excel sheet to the relevant folder.

Which makes me think it's not picking up my query somehow therefore won't display any data? Or I've messed up somewhere on the SMP and Week function....

As you can tell I'm a complete novice (but eager to learn!) and any help would be greatly appreciated.

Many thanks

 

[AOB]

Hi BayEnder111

Couple of questions...

You seem to set the query parameters, then reset them again (without using the original values for anything) Why is this? The second pair of settings renders the first pair completely redundant?

qdf.Parameters("Week Start") = DateAdd("d", (Int(txtweek.Value) - 1) * 7, SMPStart)
qdf.Parameters("Week End") = DateAdd("d", (Int(txtweek.Value) - 1) * 7, SMPStart) + 7
......
qdf.Parameters("Week Start") = Format(SMPStart, "dd/mm/yyyy")
qdf.Parameters("Week End") = Format(SMPEnd, "dd/mm/yyyy"

(Piece of advice; don't use spaces in any of your names for tables, fields, parameters etc. - you'll end up having to use square brackets everywhere to stop Access errors. Also, don't use 'special characters' such as ? - these will cause you problems...)

Also, I don't see SMPEnd declared anywhere? What kind of variable is this? SMPStart is declared as a Date; what is SMPEnd? And where is a value assigned to it?

(Another piece of advice; use Option Explicit at the top of every module or class component, it will help you to track issues relating to variables such as these)

If they are both Date variables, you don't need to format them before setting them as parameters (Access knows already that they are dates, which it stores as integers anyway, so there is no ambiguity) You only need to format them if they are Text variables. And even then, Access SQL uses the mm/dd/yyyy format as default for date strings (regardless of region)

qdf.Parameters("Week Start") = SMPStart

Have you debugged this code line by line and set watches on all your variables? Check your tables / run your query manually and look at the results. Then debug your code and set watches to identify where a variable isn't taking on a value that you expect it to.