VbA code to find previous date

[aman]

Hi All
I want VbA code to find a date 1 less than todays date taking into account weekends/Bank Holidays.
Any help will be much appreciated.
Thanks

 

[Minty]

To be able to look up Bank holidays properly you will need to set up a holidays table that has the bank holidays listed specific to the country the database is being used in. You can then check if the date is either a bank holiday or a weekend using a query.

If it's multi country things will get complicated.

 

[aman]

Thanks Minty. I have set up the table for bank holidays. please see attached;

 

[fernando.rosales]

This will remove a day from today and from there you can do whatever you need to do.

 variable= DateAdd("d", -1, Date)
  
 if variable = (bankholiday date) then
 'code here
 else
 'code here
 end if

 

[CJ_London]

suggest take a look at this post which takes into account holidays and weekends

http://www.access-programmers.co.uk/forums/showthread.php?t=275848&highlight=holiday

 

[aman]

I am using the following code:

    strsql = "select count(*) from tblmaster where Date1 = #" & Format(DateAdd("d", -1, CDate(Date)), "mm/dd/yyyy") & "# and Printuser is null"
    
    rs.Open strsql, cn, adOpenKeyset, adLockOptimistic
    j = rs.Fields(0).value

But I also need to check bank holidays and Weekends too in the code. Any help will be much appreciated.

Thanks

 

[CJ_London]

see post #5

 

[aman]

Thanks CJ_London. Dont know how to use that in my code. Can you please help me in this? Many Thanks

 

[CJ_London]

assuming you have the same table and field names as identified in the code, put the function in a module (not a form module) and call it - the value for duedate would be date() and transit days would be 1