dhCountWorkdaysA help

[Guest]

Hi

I am 99% there in implementing the code on
www.mvps.org/access/datetime/date0012.htm but get a 'data type mismatch in
criteria expression' when I specify holiday dates. This is the query I have
built in the query design grid:

SLA_Wrkdays:
dhCountWorkdaysA([SELECTION]![AP_EXT_APPDATE_DDMMYY],[SELECTION]![AP_EXT_DECDATE_DDMMYY],Array(#26/12/2005#,#27/12/2005#,#02/01/2006#))

It works fine when I omit the 'Array(#26/12/2005#,...)' part, but, as a lot
of the data I have relates to the time period around the year-end, I need to
be able to exclude these dates. Any advice would be gratefully accepted. I
wondered if it had anythingto do with the "= Empty" part of "Public Function
dhCountWorkdaysA(ByVal dtmStart As Date, ByVal dtmEnd As Date, Optional
adtmDates As Variant = Empty) As Integer", but removing the "= Empty" didn't
help.

As an aside, I also set up the holiday dates in a table thinking I would
have the query reference the table, but in the end couldn't figure out how to
get that to work.

 

[Douglas J. Steele]

Regardless of what your short date format has been set to, you must use
mm/dd/yyyy format when you provide #-delimited constants.

Access will correctly translate your #26/12/2005# and #27/12/2005# dates
since there aren't months 26 and 27, but it's going to treat #02/01/2006# as
1 Feb, 2006.

Since you're obviously using a different short date format, you might find
it worthwhile to read Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I have in my September 2003
Access Answers column for Pinnacle Publication's "Smart Access" newsletter.
(The column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Guest]

Thanks for your response Doug, but...

I have changed the format of the date constants in the query as you advised,
but still get the 'data type mismatch in criteria expression' error when I
try to execute it. Can you suggest anything else that may be causing this
error based on the code I have posted thus far?

I had a look at Allen Browne's International Dates article (unfortunately
the company firewall doesn't let me download from websites, so was unable to
look at your article). Based on my understanding of the article, I couldn't
see any 'rules' I was obviously breaking (apart from the #mm/dd/yy# format
you pointed out to me), so I tried to reformat all my DD/MM/YYYY data
(1.8million records) to MM/DD/YY using the 'Short Date' Format in the design
view of my source data table to resolve the data type mismatch, but no data
appeared to change, and the query still failed to run with the same error
message. !!!

Thanks in advance for your assistance,

Paul

Regardless of what your short date format has been set to, you must use
mm/dd/yyyy format when you provide #-delimited constants.

Access will correctly translate your #26/12/2005# and #27/12/2005# dates
since there aren't months 26 and 27, but it's going to treat #02/01/2006# as
1 Feb, 2006.

Since you're obviously using a different short date format, you might find
it worthwhile to read Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I have in my September 2003
Access Answers column for Pinnacle Publication's "Smart Access" newsletter.
(The column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi

I am 99% there in implementing the code on
www.mvps.org/access/datetime/date0012.htm but get a 'data type mismatch in
criteria expression' when I specify holiday dates. This is the query I
have
built in the query design grid:

SLA_Wrkdays:
dhCountWorkdaysA([SELECTION]![AP_EXT_APPDATE_DDMMYY],[SELECTION]![AP_EXT_DECDATE_DDMMYY],Array(#26/12/2005#,#27/12/2005#,#02/01/2006#))

It works fine when I omit the 'Array(#26/12/2005#,...)' part, but, as a
lot
of the data I have relates to the time period around the year-end, I need
to
be able to exclude these dates. Any advice would be gratefully accepted.
I
wondered if it had anythingto do with the "= Empty" part of "Public
Function
dhCountWorkdaysA(ByVal dtmStart As Date, ByVal dtmEnd As Date, Optional
adtmDates As Variant = Empty) As Integer", but removing the "= Empty"
didn't
help.

As an aside, I also set up the holiday dates in a table thinking I would
have the query reference the table, but in the end couldn't figure out how
to
get that to work.