Working days calc error

[Apples76]

I am using the Calculate working days function provided by Ken Getz and i
keep on getting data mismatch error when trying to run it.

both of the scource dates are in the medium format (all though i did need to
change on filed from the long format)

does anyone have any ideas?

 

[Allen Browne]

Presumably you are talking about this code:
http://www.mvps.org/access/datetime/date0012.htm

Which function are you using?

Where are you using this? Query? Form? Report?

What are you passing to the function? Text boxes? Date/Time fields?
Calculated expressions?

 

[Apples76]

Hi Allen,

yes you presume correct.

i am trying to use "dhCountWorkdaysA" function and have copied the required
SkipHolidays etc.

I am using Access 2000

the fields are in the same query as the expression and both fields are dates
fileds(medium format) i am not using the Array function for holidays as the
business does not officially recognise them.

 

[Allen Browne]

Okay, dhCountWorkdaysA() accepts 2 days (ignoring the possible list of
holidays.)

If you are getting a type mismatch, Access is not understanding the fields
as dates, or perhaps they are null.

The Format is not relevant. Internally, Access handles date/time values as
numbers (days, and a fraction of a day for the time.) If your field does use
the Format() function, it is a Text field, and that would be the reason for
the type mismatch.

If the field you are passing is a Date/Time field in a table, it will be
fine (unless it's Null.) If it is a calculated query field, you need to
typecast it by wrapping CDate() around the expression. If it is a parameter,
you need to declare it (Parameters on Query menu, in query design.) If it is
a text box, set the control's Format property to General Date.

Here's some information about handling the error:
http://allenbrowne.com/ser-36.html#DataType

If you are still stuck, switch your query to SQL View (View menu in query
design). Copy the SQL statement, and post it here.

 

[Apples76]

Allen,

how do i handle nulls?

Okay, dhCountWorkdaysA() accepts 2 days (ignoring the possible list of
holidays.)

If you are getting a type mismatch, Access is not understanding the fields
as dates, or perhaps they are null.

The Format is not relevant. Internally, Access handles date/time values as
numbers (days, and a fraction of a day for the time.) If your field does use
the Format() function, it is a Text field, and that would be the reason for
the type mismatch.

If the field you are passing is a Date/Time field in a table, it will be
fine (unless it's Null.) If it is a calculated query field, you need to
typecast it by wrapping CDate() around the expression. If it is a parameter,
you need to declare it (Parameters on Query menu, in query design.) If it is
a text box, set the control's Format property to General Date.

Here's some information about handling the error:
http://allenbrowne.com/ser-36.html#DataType

If you are still stuck, switch your query to SQL View (View menu in query
design). Copy the SQL statement, and post it here.

 

[Allen Browne]

If some fields are blank, you will need to either add criteria to your query
so they are excluded before being passed to the function.

If that is not practical, you will need to modify the function so it accepts
Variant instead of Date arguments. Then use IsDate() to test the arguments
before operating on them.

 

[Apples76]

Allen,

once again your advice has proved very valuble.

Many thanks.