VBA last business day

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

Answer 1

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)

Answer 2

It's still pulling todays date 

Answer 3

Explore how to use the WeekDay function:

https://docs.microsoft.com/en-us/previous-versions/visualstudio/visual-studio-2008/82yfs2zh(v=vs.90) and https://docs.microsoft.com/en-us/previous-versions/visualstudio/visual-studio-2008/t8dc1aee%28v%3dvs.90%29

Answer 4

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)

Answer 5

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

Answer 6

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 [e-mail].