none
VBA last business day RRS feed

  • Question

  • Hi,

    Below is my current VBA module. I am needing to have it pull from the last business day and not todays date. I have read several forums, but am still in need of assistance.

    Private Sub Command0_Click()


    Dim DATE1 As String
    Dim PATH As String

    DATE1 = Format(Date, "yyyymmdd")
    PATH = "N:\CFS\Aging_LCD\Access VBA to Import a File\CAL_Aging_" & DATE1 & ".csv"


    DoCmd.RunSQL "Delete * from Aging"

    On Error GoTo Bad
    DoCmd.TransferText acImportDelim, "CAL_Aging_specification", "Aging", PATH, True

    Exit Sub

    Bad:
    MsgBox "The File for the date " & DATE1 & " cannot be found in the directory " & PATH & " OR the file is currently opened by another user"
    End Sub

    Monday, July 30, 2018 3:18 PM

All replies

  • For example:

        Dim d As Date
        ' Last day of current month
        d = DateSerial(Year(Date), Month(Date) + 1, 0)
        Select Case Weekday(d)
            Case 1 ' Sunday
                d = d - 2
            Case 7 ' Saturday
                d = d - 1
        End Select
        DATE1 = Format(d, "yyyymmdd")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, July 30, 2018 3:23 PM
  • It's still pulling todays date 

    Monday, July 30, 2018 3:28 PM
  • You must have done something wrong. Today (currently the 30th of July 2018), the value of DATE1 using the code that I posted is "20180731".

    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Monday, July 30, 2018 6:12 PM
  • An efficient approach is to create an auxiliary BusinessDaysCalendar table of business days only.  For means of creating such a calendar take a look at Calendar.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file enables you to create a calendar for any working week, and to exclude public and concessionary holidays from the table.  To return the latest business day on or before the current date is then a simple DMax function call:

    DMax("calDate","BusinessDaysCalendar","calDate<= #" & Format(Date(),"yyyy-mm-dd") & "#")

    For the last business day of the current month:

    DMax("calDate","BusinessDaysCalendar","calDate<= #" & Format(DateSerial(Year(Date()),Month(Date())+1,0),"yyyy-mm-dd") & "#")

    Note the use of the ISO Standard for date formatting of YYYY-MM-DD when building the date literal, to provide international unambiguity. The US format of MM/DD/YYYY can also be used.


    Ken Sheridan, Stafford, England

    Monday, July 30, 2018 10:58 PM
  • Hello Syd5186369,

    I think what you want to get is yesterday's date and if the date is a weekend date, you want to continue to go back until a weekday. 

    If so, please try to check if below code modified with Hans' code could work for you. If not, please try to use some date example to detail us your need. Thanks for understanding.

      Dim d As Date
        d = DateAdd("d", -1, Date)
        Select Case Weekday(d, vbSunday)
            Case 7 ' Sunday
                d = d - 2
            Case 6 ' Saturday
                d = d - 1
        End Select
        date1 = Format(d, "yyyymmdd")
        Debug.Print date1

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 31, 2018 6:04 AM