Results 1 to 3 of 3

I KNOW I am doing it wrong

  1. #1
    lonesoac0 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Aug 2014
    Posts
    29

    I KNOW I am doing it wrong


    Hello all,

    I know I am doing something wrong in my Do While loop in the steps. While this code is functional, I would like to make it more elegant. I feel like I am lobotomizing my date value in Step 2, any ideas? The format of my Date field in Access is yyyymm and it is stored as a string.

    Code:
    Public Function ImportMonthlyCallSummary()
    Dim MaxDateInMonthly_Reports_Queue_Call_Summary As Date
    Dim TodaysDate As Date
    
    'This is how I get last month's date every time.
    YesterdaysDate = DatePart("M", (Date)) & "/1/" & DatePart("yyyy", (Date))
    YesterdaysDate = DateAdd("D", -1, (YesterdaysDate))
    '========
    
    'This is how this script gets the last date value in the table.
    MaxDateInMonthly_Reports_Queue_Call_Summary = CDate(Format(DLast("Date", "Monthly_Reports_Queue_Call_Summary"), "####/##"))
    '========
    
    ' This whole Do Loop is how the data is loaded.
    ' The MaxDateInMontyhly_Reports* is the last value in the table.  YeserdaysDate is last months last date.
    Do While MaxDateInMonthly_Reports_Queue_Call_Summary < YesterdaysDate
    '   This adds one month to the MaxDateInMontyhly_Reports* variable.
        MsgBox ("Step 1")
        MsgBox Format(MaxDateInMonthly_Reports_Queue_Call_Summary, "yyyymmdd")
    '   This adds two months to the max date value. So, if the MaxValue Variable is 20160901 then it is 20161101.
        MaxDateInMonthly_Reports_Queue_Call_Summary = DateAdd("M", 2, MaxDateInMonthly_Reports_Queue_Call_Summary)
        MsgBox ("Step 2")
        MsgBox Format(MaxDateInMonthly_Reports_Queue_Call_Summary, "yyyymmdd")
    '   This lowers the playing field for the end of month date values.  Some are 31, 30, or 29 etc.  This ensures all dates are the 1st for every month.
        MaxDateInMonthly_Reports_Queue_Call_Summary = DatePart("M", (MaxDateInMonthly_Reports_Queue_Call_Summary)) & "/1/" & DatePart("yyyy", (MaxDateInMonthly_Reports_Queue_Call_Summary))
        MsgBox ("Step 3")
        MsgBox Format(MaxDateInMonthly_Reports_Queue_Call_Summary, "yyyymmdd")
    '   This subtracts one day from the "leveled" date value from the above step.
        MaxDateInMonthly_Reports_Queue_Call_Summary = DateAdd("D", -1, (MaxDateInMonthly_Reports_Queue_Call_Summary))
        MsgBox ("Step 4")
        MsgBox Format(MaxDateInMonthly_Reports_Queue_Call_Summary, "yyyymmdd")
        DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="Monthly_Reports_QUEUE_CALL_SUMMARY_Import_Specific", TableName:="Monthly_Reports_Queue_Call_Summary", FileName:="\\NetworkLocation\QUEUE_CALL_SUMMARY_" & Format(MaxDateInMonthly_Reports_Queue_Call_Summary, "yyyymmdd") & ".csv", hasfieldnames:=True
    Loop
    
    For Each t In CurrentDb.TableDefs
        If t.Name Like "*ImportErrors*" Then DoCmd.RunSQL ("DROP TABLE " & t.Name)
    Next
    
    End Function

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,732
    If you could explain what the goal is - to import csv files (or only one?) with a date in the file name. What exactly is that date value and it's format? Yes I am sure this can be done more "elegantly"!

    This is a worry - please don't say that you have a field on your table called "Date"? This is a reserved word in Access and is a huge no-no.
    DLast("Date", "Monthly_Reports_Queue_Call_Summary")

  3. #3
    Micron is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    2,542
    Agreed, especially about the field named Date. Plus, don't understand the goal with the dates. You say 'this is how I get last month's date', but last month has anywhere from 28 to 31 dates, so what are you trying to do? Get the date of the first day of last month and/or the date of the last day of last month? I think it is only the last date of the previous month and you want to query between that date and yesterday, whenever that is. However, you also have this
    "This lowers the playing field for the end of month date values. Some are 31, 30, or 29 etc. This ensures all dates are the 1st for every month."

    FWIW, this
    Date-DatePart("d",date)
    will return the last date of the previous month regardless of how many days were in that month or the one you're in, so that is simpler, which I guess is the whole point of your question.
    I don't get this format ####/## for a date that's stored as a string either.

    Also, you have
    Code:
    '   This adds one month to the MaxDateInMontyhly_Reports* variable.
        MsgBox ("Step 1")
        MsgBox Format(MaxDateInMonthly_Reports_Queue_Call_Summary, "yyyymmdd")
    but I don't see that happening. Where's the added month part? It seems you'll get one transfer, then the next is 2 months later, and each additional loop will add 2 months - unless somewhere in the following code you're stepping back one month from some value. Afraid I can't decipher that - makes my head spin. Lastly for now, would you not want to know which imports didn't work rather than just dumping the error table? Here's a version (probably screwed up because I don't understand the process) that might provide clues on how to consolidate your code.

    Code:
    Public Function ImportMonthlyCallSummary()
    Dim dteMaxDate as Date
    Dim dteToday as Date, dteLastMonth as Date
    Private Const specNme = "Monthly_Reports_QUEUE_CALL_SUMMARY_Import_Specific"
    Private Const tblNme = "Monthly_Reports_Queue_Call_Summary"
    Private Const fileNme = "\\NetworkLocation\QUEUE_CALL_SUMMARY_" & Format
    
    'I don't get the idea behind the date format, even if the table field contains a date in the form of 42,721.00
    'if it needs formatting, the # signs don't make sense to me.
    'dteMaxDate = CDate(Format(DLast("Date", "Monthly_Reports_Queue_Call_Summary"), "####/##"))
    'seems to me it should be 
    dteMaxDate = CDate(DLast("Date", "Monthly_Reports_Queue_Call_Summary"))
    dteLastMonth = Date()-DatePart("d",Date())
    
     Do While dteMaxDate < dteLastMonth 
     ' This adds one month to the MaxDateInMontyhly_Reports* variable.
        dteMaxDate = DateAdd("M", 1)
        DoCmd.TransferText acImportDelim, SpecNme, tblNme, FileNme & Format (dteMaxDate, "yyyymmdd") & ".csv", True
     Loop
    
     For Each t In CurrentDb.TableDefs
         If t.Name Like "*ImportErrors*" Then DoCmd.RunSQL ("DROP TABLE " & t.Name)
     Next
    
     End Function
    - "doesn't work" is no help.Error #s/text do. State what's happening.
    - Use code tags for code/sql; show where errors occur
    Make all suggested changes in copies of your database or to its objects

Please reply to this thread with any new information or opinions.

Similar Threads

  1. what is wrong ?
    By dino in forum Access
    Replies: 19
    Last Post: 07-29-2015, 10:13 AM
  2. What am I doing wrong?
    By Access_noob_ in forum Programming
    Replies: 3
    Last Post: 11-03-2014, 09:47 AM
  3. what is wrong
    By Dinoshop in forum Access
    Replies: 1
    Last Post: 07-11-2014, 05:22 AM
  4. What's wrong!
    By khalid in forum Programming
    Replies: 15
    Last Post: 06-27-2011, 05:38 AM
  5. What am I doing wrong?
    By brandon in forum Access
    Replies: 2
    Last Post: 08-03-2008, 09:26 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums