Results 1 to 5 of 5

Thread: MS Access Error

  1. #1
    Join Date
    Jul 2012
    Posts
    16

    Unanswered: MS Access Error

    Hi Everyone,

    I am new to this so any help is appreciated.
    I have the below code that need to export the data as a report, when the final report is produced the date formatting is wrong so I get error when trying to use these results to run another report.

    Can anyone help with this? I have already tried changing the formatting from (UsageDate, "mm/dd/yyyy") & "#))to (UsageDate, yyyy/mm/dd") & "#)) but it didnt helo.

    Many thanks,


    Sub LblUsageRptBUpload_Click()
    On Error GoTo Err_ErrMessage

    DoCmd.SetWarnings (False)

    'create a temporary table to hold the core data
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim tbl As TableDef
    Dim sql, sql1 As String

    '################################################# #########

    Dim UsageYr As String
    UsageYr = Me.CmbUsageSlctYr.Value

    Dim UsageMonth As String
    UsageMonth = Left(Me.CmbUsageSlctMonth.Value, 2)

    Dim UsageDate As Date
    UsageDate = "01/" & UsageMonth & "/" & UsageYr

    '*****************Check if the Report B Data for the Selected month already exist in the table and if the user wants to reload the data. **********

    Dim SqlRptBUsageDateChk As String
    SqlRptBUsageDateChk = " SELECT TblUsageReportB.SaleDate " & _
    " FROM TblUsageReportB " & _
    " GROUP BY TblUsageReportB.SaleDate " & _
    " HAVING (((TblUsageReportB.SaleDate)=#" & Format(UsageDate, "mm/dd/yyyy") & "#));"
    '" HAVING (((TblUsageReportB.SaleDate)=#" & Format(UsageDate, "mm/dd/yyyy") & "#));"

    Dim RsRptBUsageDateChk As Recordset
    Set RsRptBUsageDateChk = db.OpenRecordset(SqlRptBUsageDateChk)

    If RsRptBUsageDateChk.RecordCount > 0 Then

    Dim Msg, Style, Title, Help, Ctxt, Response, ReloadRptB

    Msg = "Usage ReportB Data for " & UsageDate & " Already Exist." & vbNewLine & "Do You Want to delete the data and reload it???"
    Style = vbYesNo + vbCritical + vbDefaultButton2
    Title = "Usage ReportB Data Check"
    Help = "DEMO.HLP"
    Ctxt = 1000
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)


    If Response = vbYes Then ' User chooses Yes then delete the existing data for the selected month from the table and rekoad the data.
    DoCmd.SetWarnings (False)
    Dim DelRptBData As String
    SqlDelRptBData = "DELETE TblUsageReportB.SaleDate, TblUsageReportB.* " & _
    " FROM TblUsageReportB " & _
    " WHERE (((TblUsageReportB.SaleDate)=#" & Format(UsageDate, "mm/dd/yyyy") & "#));"
    DoCmd****nSQL SqlDelRptBData

    DoCmd.SetWarnings (True)
    Else ' User chooses No then exit sub.

    Exit Sub
    End If


    End If



    Dim mypath As String
    mypath = .....
    Dim xlsApp As Object
    Set xlsApp = CreateObject("Excel.Application")
    xlsApp.Application.ScreenUpdating = True
    xlsApp.Application.DisplayAlerts = False

    xlsApp.Workbooks.Open (mypath)
    xlsApp.Visible = True
    'xlsApp.Visible = True
    With xlsApp
    '************************************************* ***********************************

    'check if the sheet exist in the workbook
    'Dim sh As Worksheet

    For Each sh In xlsApp.Worksheets
    If sh.Name = UsageYr & UsageMonth & "ReportB" Then

    With xlsApp
    .Sheets(sh.Name).Select
    .Sheets(sh.Name).Delete

    End With

    End If
    Next

    .Sheets("CUSTOMER").Select
    .Range("A8").Select
    .Selection.RemoveSubtotal
    .Selection.AutoFilter
    I = .ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell ).Row - 13 ' COUNT THE NUMBER OF ROWS IN THE SHEET

    .Range("$A$8:$X" & I & "").AutoFilter Field:=2, Criteria1:="WEST"
    .ActiveSheet.Range("$A$8:$X" & I & "").AutoFilter Field:=11, Criteria1:= _
    "=Europe EMEA", Operator:=xlOr, Criteria2:="=Sweet & Maxwell"

    ' .ActiveSheet.Range("$A$8:$X$" & i & "").AutoFilter Field:=UsageMonth + 11, Criteria1:="<>"

    .Range("A8").Select
    .Range(.Selection, .Selection.End(xlToRight)).Select
    .Range(.Selection, .Selection.End(xlDown)).Select
    .Selection.Copy
    .Sheets.Add
    .ActiveSheet.Name = UsageYr & UsageMonth & "ReportB"
    .Range("A1").Select
    .ActiveSheet.Paste
    '
    .Range("L1").Select
    Do Until ActiveCell.Value = UsageYr & "-" & UsageMonth

    .Columns(12).Select
    .Application.CutCopyMode = False
    .Selection.Delete Shift:=xlToLeft

    Loop
    If ActiveCell.Value = UsageYr & "-" & UsageMonth Then
    .ActiveCell.FormulaR1C1 = "CurrentMonthValue"
    .Columns("L:L").Select
    .Selection.NumberFormat = "0.00"

    End If
    .Columns("M:M").Select
    .Selection.Delete Shift:=xlToLeft


    .Range("K1").Select
    .Selection.AutoFilter
    K = .ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell ).Row
    .ActiveSheet.Range("$A$1:$M" & K & "").AutoFilter Field:=12, Criteria1:="="
    .Rows("2:2").Select
    .Range(.Selection, .Selection.End(xlDown)).Select
    .Selection.Delete Shift:=xlUp

    .Range("A1").Select
    .Selection.AutoFilter
    '
    .ActiveWorkbook.Save

    .Range("A1").Select
    I = .ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell ).Row




    .Range("M1").Select
    .ActiveCell.FormulaR1C1 = "SaleDate"

    .Range("M2").Select
    .ActiveCell.FormulaR1C1 = Format(UsageDate, "yyyy/mm/dd")


    .Selection.AutoFill Destination:=.Range("M2:M" & I & ""), Type:=xlFillCopy


    .ActiveWorkbook.Save
    .ActiveWorkbook.Close
    .Quit
    End With
    xlsApp.Application.DisplayAlerts = True

    xlsApp.Application.ScreenUpdating = True
    Set xlsApp = Nothing

    '************************************************* ************


    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "TblUsageReportB", mypath, True, SheetName & "!A1:M" & I & ""

    Set RsRptBUsageDateChk = Nothing
    Me.Refresh
    db.Close
    Set dbs = Nothing

    DoCmd.SetWarnings (True)

    MsgBox "Usage ReportB Upload is Completed."

    Exit_ErrMessage:

    Exit Sub

    Err_ErrMessage:
    MsgBox Err.Description
    Resume Exit_ErrMessage
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what error do you get
    what is the value the string SqlDelRptBData
    what datatype is usagedate, if its already a date then there is no point converting it to s atring
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Dim UsageDate As Date
    UsageDate = "01/" & UsageMonth & "/" & UsageYr
    ...
    " HAVING (((TblUsageReportB.SaleDate)=#" & Format(UsageDate, "mm/dd/yyyy") & "#));"
    As healdem says, get rid of the formatting. Just have the line
    Code:
    " HAVING (((TblUsageReportB.SaleDate)=#" & UsageDate & "#));"
    I have a different question, though. Why are you limiting the report to one day? That's what you're doing by adding the "01" to UsageDate. If you need a month worth of data, you should use
    Code:
    WHERE (Month(TblUsageReportB.SaleDate) = UsageMonth) And (Year(TblUsageReportB.SaleDate) = UsageYr)
    If you decide to do that, you need to have numeric values for both UsageMonth and UsageYr, not string values.

  4. #4
    Join Date
    Jul 2012
    Posts
    16
    Thank you. it did the trick

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You're very welcome.

    Sam

Posting Permissions

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