CODEWITHSUNDEEP
sqlms-accessvbams-access-2007
wlfente
wlfente

Reputation: 153

Access 2007 query date calculation

Query Example

I have the following Access query (see above) to give me the date, X number of weeks out from the actual WorkDte that exists in the table. But, the date these expressions calculate out may not actually exists in the “CIB_Results” table due to a bank holiday etc. Is there a way to generate the same sort of data but say if it’s running the “Wk1” calculation and it calculates out 1/1/2016 (which does not exists due to the new year holiday), instead of doing [WorkDte]-7 it will move on to [WorkDte]-14 and so on, until it finds an actual date that exists in the “CIB_Results” table? I’m wanting to apply the same logic to all the fields in the query…that way they will all self adjust based off of the actual dates that exist in the “CIB_Results” table. Any help with this would be greatly appreciated!

Upvotes: 0

Views: 38

Answers (1)

Gustav
Gustav

Reputation: 55816

Have a function IsHoliday(SomeDate) that looks up SomeDate in your holiday table and returns True if found.

Then create a loop:

Public Function PreviousWorkWeekday(ByVal SomeDate As Date) As Date

    Do
        SomeDate = DateAdd("ww", -1, SomeDate)
    Loop Until Not IsHoliday(SomeDate)
    PreviousWorkWeekday = SomeDate

End Function

Now use function PreviousWorkWeekday in your query.

Upvotes: 0

Related Questions