Solved Business Days VBA

sathsaj

Registered User.
Local time
Yesterday, 17:54
Joined
Sep 19, 2017
Messages
24
Dim outputFileName As String
outputFileName = CurrentProject.Path & "\Constituents " & Format(Date - 1, "ddMM") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Test", outputFileName, True


Above code give me 2101, how can I change this code to get 1901 which Businessdays-1
 
? Format(Date() - 1, "ddMM") & ".xls"
2101.xls
? date-1
21/01/2024

@Pat Hartman uploaded a DB with a bunch of date functions, but I cannot find it now.

So go to Google
 
Maybe something like?
Code:
outputFileName = CurrentProject.Path & "\Constituents " & Format(Date - IIf(Weekday(Date)=7,3,IIf(Weekday(Date)=,2,1)), "ddMM") & ".xls"
(untested)

Edit: Oops, I got the day numbers wrong. Thanks @Gasman for catching that.
 
Last edited:
Maybe something like?
Code:
outputFileName = CurrentProject.Path & "\Constituents " & Format(Date - IIf(Weekday(Date)=7,3,IIf(Weekday(Date)=6,2,1)), "ddMM") & ".xls"
(untested)
If you go on the default starting day of the week then perhaps
Code:
? Format(Date - IIf(Weekday(Date)=2,3,IIf(Weekday(Date)=1,2,1)), "ddMM") & ".xls"
as if Monday (2) then you need to go back 3 days, if Sunday (1) then you go back 2 days,, else just the 1 day?

Do test it all out thoroughly though.
 
nothing worked, it still gives me 2101
Really?
Worked fine for me and I changed my computer date to yesterday and Saturday as well as running it for today.
 
Really?
Worked fine for me and I changed my computer date to yesterday and Saturday as well as running it for today.
? Format(Date - IIf(Weekday(Date)=2,3,IIf(Weekday(Date)=1,2,1)), "ddMM") & ".xls" - this one worked now, I had some wrong code
 
It's in the sample databases thread.

 
It's in the sample databases thread.

Never thought to look there :(
 
Too obvious for you :ROFLMAO: I did ask @Jon to add subfolders to the sample databases to make it possible to "drill down" using logical generic names. The current method is like a bag of marbles. If you're looking for the cat's eye, you have to dump the whole bag on the rug and look through them all. No one has the patience for that so lots of good tools go unnoticed.
 
Too obvious for you
Sadly Yes. I was looking through your posts for it.
Perhaps I will remember next time. :)
 
I keep a file on my desktop with all my "official" samples in the Sample Database folder so I don't have to keep searching for them. If I can't remember what they're called, no reason why you should.
 

Users who are viewing this thread

Back
Top Bottom