VBA to display last business day

[sathom27]

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

[NeoPa]

sathom27:
the last business day

It might help to know what you mean by this.

[zmbd]

sathom27,
I agree with NeoPa - a bit more information would be nice; however, I am going to go out on a limb here looking at your posted code that say if today was #7/24/2018# = Tuesday you want Monday the #7/23/2018# and similarly if the date was that Monday (#7/23/2018#) then you want the prior Friday, #7/20/2018#

There are a lot of little algorithms to count days etc; however, this may be the more straight forward approach:

1) You need a table with the holidays. I'd do this even for the more static and stable holidays because society changes.

2) I'm going to use the standard USA work week of Monday through Friday
>> I'm going to be using US date formats, #mm/dd/yyyy#

3) Working logically:
+ Explicitly setting the start of the week as Sunday
+ For Tuesday through Saturday you only need to back one day to get the next business day
+ For Monday you need to go back 3 days to get the previous Friday
+ For Sunday you need to go back 2 days to get the previous Friday

'So your pseudo-code looks like
Select Case CurrentDate
  Case Sunday
    -2
  Case Monday
    -3
  Case Between(Tuesday through Saturday)
    -1
End Select

We need to get the day of the week for the current date and the function for that is
Weekday(Date,FirstDayOfWeek)
so that we can subtract the correct number of days...

'So your pseudo-code looks like
  lngWDay = WeekdDay(CurrentDate,vbSunday)
'>Notice we're going to override the system setting for the first day of the week
 
  Select Case lngWDay
    Case Sunday
       -2
    Case Monday
      -3
    Case Tuesday to Saturday
      -1
  End Select

And finally DateAdd(Interval, Number, Date)

'So your pseudo-code looks like
  lngWDay = WeekdDay(CurrentDate,vbSunday)
  Select Case lngWDay
    Case Sunday 
      DateAdd("d", -2 , CurrentDate)
    Case Monday
      DateAdd("d", -3 , CurrentDate)
    Case Tuesday to Saturday
      DateAdd("d", -1 , CurrentDate)
  End Select

There's a little bit of a trick we can do with Weekday(Date,FirstDayOfWeek) where we can set the start of the week to a specific day of the week and then find the prior day of the week for that day (maybe an example here). So for Sunday and Monday we want the previous Friday so we'll set the week start day as Friday... MIND YOU THIS WILL FAIL if you try to get the prior Friday from a date that is a Friday (#7/27/2018# will return the same date not #7/20/2018# using the following method):
Weekday(FridayDate,vbFriday)=1 we need to back that up one day and negate the returned weekday so that we can use DateAdd()

So for a test date:
DateAdd("d", (-1 * (Weekday(#7/23/2018#, vbFriday) - 1)), #7/23/2018#)
= #7/20/2018#

With this trick in mind we can combine the Sunday and Monday into one case (at this point one could use a If..Then clause instead of Select..Case - I prefer the select case because the intent is clearer to anyone reading the code later - if then may be faster)

'So your pseudo-code looks like
  lngWDay = WeekdDay(CurrentDate,vbSunday)
  Select Case lngWDay
    Case Sunday, Monday
      DateAdd("d", (-1 * (Weekday(#CurrentDate#, vbFriday) - 1)), #CurrentDate#)
    Case Tuesday to Saturday
      DateAdd("d", -1 , CurrentDate)
  End Select

Finally we need to compare the returned date against the record set of holidays and adjust if needed.

Try your hand at writing the actual code and post back - we'll help you fix any errors.