Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA - Calculating a date that is a weekday and not a holiday

Posted on 2012-12-31
2
Medium Priority
?
511 Views
Last Modified: 2012-12-31
Hi Guys and Gals

I'm looking to create a custom function that I pass a date to and an integer. I want to add x days onto a date and the result date is not to be a holiday or a weekend. I've got so far myself but for some reason my function lookup is returning a 'false' when its testing if its a holiday, but only on some entries.

The holidays table is a simple 3 column table with ID, Description and Holiday (This is a date/time field).

In my test, I put Xmas day in as the start date and put holidays in for xmas, boxing day, and 1st - 4th Jan. Yet my output date comes back as 4th Jan which is a holiday.... I can't figure out why it won't recognise this is a holiday.

Attached is the database - what am I missing?

James
Trackers-VBA.accdb
0
Comment
Question by:Delerium1978
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 38732941
I don't know where you are located but for non-US operation you must use...

nHolidays = DCount(Expr:="[Holiday]", Domain:="[Holidays]", Criteria:="[Holiday] =#" & Format(InputDate, "yyyy-mm-dd") & "#")

 ...to get a a valid date in the holidays function.
0
 

Author Closing Comment

by:Delerium1978
ID: 38733064
Hi Peter,

I'm from the UK. Used that format line code and worked a charm. I had a hunch it was something format related but there's no clear documentation that states that you need to do this anywhere (that I found anyway).

Nice work anyway. Thanks :)

James
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question