Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Criteria has changed (2K)

    Hi,

    I have had a database set up for a year or so now which is basically a data imputting database. Little or no queries are done.
    It is mainly used for exporting files over to an excel template.

    This is the rather long code:
    <hr>
    Private Sub cmdExport_Click()
    ' Modify as needed
    'Workbooks.Add (Template = "P:TRIALS UNITWAVESWAVESdatabase1812.xlt")



    Const strExcelFile = "P:TRIALS UNITWAVESWAVESdatabase1812.xlt"

    Dim strSQL As String
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlSht As Excel.Worksheet
    Dim blnStartExcel As Boolean
    Dim lngRow As Long
    Dim lngRows As Long

    On Error GoTo ErrHandler

    If IsNull(Me.cboYear) Then
    MsgBox "Please select a year.", vbExclamation
    Me.cboYear.SetFocus
    Exit Sub
    End If

    If IsNull(Me.cboQuarter) Then
    MsgBox "Please select a quarter.", vbExclamation
    Me.cboQuarter.SetFocus
    Exit Sub
    End If

    If MsgBox("This process may take several minutes. Still proceed?", vbQuestion + vbYesNo, "Please confirm") = vbNo Then
    Exit Sub
    End If
    MsgBox "After this process has been completed, you will be prompted to save the exported data to a new excel document.", vbInformation, "Please note"
    MsgBox "This database will now be minimised. After the process has been completed, the database will Maximise.", vbOKOnly & vbExclamation

    AccessWindow SW_Minimize

    DoCmd.Hourglass True

    strSQL = "SELECT TblMain.LCJBArea, TblMain.Division, TblMain.[Sub-division], " & _
    "TblMain.URN, TblMain.CaseOutcome, TblMain.DateOutcome, TblOffences.Offence, " & _
    "TblMain.DateOffence, TblMain.CourtType, TblWitness.VWStatus, TblWitness.Title, " & _
    "TblWitness.Forename, TblWitness.Surname, TblWitness.Gender, TblWitness.Ethnicity, " & _
    "TblWitness.DOB, TblWitness.Telephone1, TblWitness.Telephone2, TblWitness.Telephone3, " & _
    "TblWitness.Address1, TblWitness.Address2, TblWitness.Address3, TblWitness.[Town/City], " & _
    "TblWitness.County, TblWitness.Postcode, Format(TblWitness.Evidence,'Yes/No') " & _
    "FROM (TblMain LEFT JOIN TblOffences ON TblMain.Offence = TblOffences.OffenceID) " & _
    "LEFT JOIN TblWitness ON TblMain.URNID = TblWitness.URNID " & _
    "WHERE Year(DateAdd('m',2,[DateOutcome]))=" & Me.cboYear & _
    " AND Format(DateAdd('m',-3,[DateOutcome]),'q')='" & Me.cboQuarter & "'"

    Set cnn = CurrentProject.Connection
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    If rst.RecordCount = 0 Then
    MsgBox "No records for export.", vbInformation
    GoTo ExitHandler
    End If

    On Error Resume Next

    Set xlApp = GetObject("Excel.Application")
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
    MsgBox "Can't run Excel.", vbExclamation
    GoTo ExitHandler
    End If
    blnStartExcel = True
    End If

    On Error GoTo ErrHandler
    Set xlWbk = xlApp.Workbooks.Add(Template:=strExcelFile)


    Set xlSht = xlWbk.Worksheets("VICTIM & WITNESS DETAILS")
    lngRow = xlSht.Cells(65536, 1).End(xlUp).Row + 1
    lngRows = xlSht.Cells(lngRow, 1).CopyFromRecordset(rst)
    MsgBox lngRows & " row(s) exported.", vbInformation

    xlWbk.Close SaveChanges:=True


    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    xlWbk.Close SaveChanges:=False
    Set xlWbk = Nothing
    If blnStartExcel Then
    xlApp.Quit
    End If
    Set xlApp = Nothing
    DoCmd.Hourglass False
    DoCmd.Close acForm, Me.Name
    AccessWindow SW_Maximize

    Exit Sub



    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    <hr>

    The user would export data by selecting values from 2 comboboxes. CboYear & CboQuarter.
    They would then click on CmdExport for which the above coding is for.

    Each quarter has now changed to run alongside the financial Year. So Quarter 1 is April to June, Q2 is July to September and so on.

    I seem to have a problem with quarter 4, which now, would be quarter 4 in 2005.

    Of course when I select this by choosing Quarter 4, year 2005, it exports data for quarter 4, 2004. Otherwise January to March 2005.

    Can't work out how to remedy this.....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria has changed (2K)

    What happens if you use

    ...
    "WHERE Year(DateAdd('m',-3,[DateOutcome]))=" & Me.cboYear & _
    " AND Format(DateAdd('m',-3,[DateOutcome]),'q')='" & Me.cboQuarter & "'"
    ...

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria has changed (2K)

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Doh!
    Nearly had it. Again many thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •