Solved Business Days VBA (2 Viewers)

[sathsaj]

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

 

[Gasman]

? 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

calculate last working date vba - Google Search

 

[theDBguy]

Maybe something like?

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.

 

[Gasman]

Maybe something like?

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

? 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.

 

[sathsaj]

nothing worked, it still gives me 2101

 

[Gasman]

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.

 

[sathsaj]

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

 

[Pat Hartman]

It's in the sample databases thread.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

 

[Gasman]

It's in the sample databases thread.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

Never thought to look there

 

[Pat Hartman]

Too obvious for you 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.

 

[Gasman]

Too obvious for you

Sadly Yes. I was looking through your posts for it.
Perhaps I will remember next time.

 

[Pat Hartman]

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.