Workday problems

[Supple]

I have set up the addworkday module from
http://www.mvps.org/access/datetime/date0012.htm

And it works great if I don't add the optional array for the holidays.

However, I do want to include the holidays and when I add the array to the
query:

CreationDeadline:
dhAddWorkDaysA(3,[TblQCCompletedDates]![CreationReceived],Array(#7/4/2007#,#5/28/2007#))

I receive the following error:

Datatype Mismatch in criteria expression. I have everything set to "short
date" for my formats. What can I do to make this work? Do I need to do
something with the input masks?

Thanks in advance for your help.

 

[Douglas J. Steele]

Are you sure there's a value in all of the CreationReceived fields? If any
are Null, you're going to run into a problem.

Try:

IIf(IsNull([TblQCCompletedDates]![CreationReceived]), 0,
dhAddWorkDaysA(3,[TblQCCompletedDates]![CreationReceived],Array(#7/4/2007#,#5/28/2007#)))

 

[Supple]

I entered this in the query and it still comes up with a mismatch error. I do
have null values in this field.

Are you sure there's a value in all of the CreationReceived fields? If any
are Null, you're going to run into a problem.

Try:

IIf(IsNull([TblQCCompletedDates]![CreationReceived]), 0,
dhAddWorkDaysA(3,[TblQCCompletedDates]![CreationReceived],Array(#7/4/2007#,#5/28/2007#)))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have set up the addworkday module from
http://www.mvps.org/access/datetime/date0012.htm

And it works great if I don't add the optional array for the holidays.

However, I do want to include the holidays and when I add the array to the
query:

CreationDeadline:
dhAddWorkDaysA(3,[TblQCCompletedDates]![CreationReceived],Array(#7/4/2007#,#5/28/2007#))

I receive the following error:

Datatype Mismatch in criteria expression. I have everything set to "short
date" for my formats. What can I do to make this work? Do I need to do
something with the input masks?

Thanks in advance for your help.

 

[Michael Gramelspacher]

I have set up the addworkday module from
http://www.mvps.org/access/datetime/date0012.htm

And it works great if I don't add the optional array for the holidays.

However, I do want to include the holidays and when I add the array to the
query:

CreationDeadline:
dhAddWorkDaysA(3,[TblQCCompletedDates]![CreationReceived],Array(#7/4/2007#,#5/28/2007#))

I receive the following error:

Datatype Mismatch in criteria expression. I have everything set to "short
date" for my formats. What can I do to make this work? Do I need to do
something with the input masks?

Thanks in advance for your help.

If you would consisder using a calendar table, then maybe look at this
http://www.psci.net/gramelsp/temp/calendars.zip

 

[Douglas J. Steele]

Is CreationReceived a Date/Time fields, or are you storing dates in a text
field? Try

IIf(IsNull([TblQCCompletedDates]![CreationReceived]), 0,
dhAddWorkDaysA(3,CDate([TblQCCompletedDates]![CreationReceived]),Array(#7/4/2007#,#5/28/2007#)))--Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no private e-mails, please)"Supple" <[email protected]> wrote in messagenews[email protected]...>I entered this in the query and it still comes up with a mismatch error. Ido> have null values in this field.>> "Douglas J. Steele" wrote:>>> Are you sure there's a value in all of the CreationReceived fields? Ifany>> are Null, you're going to run into a problem.>>>> Try:>>>> IIf(IsNull([TblQCCompletedDates]![CreationReceived]), 0,>>dhAddWorkDaysA(3,[TblQCCompletedDates]![CreationReceived],Array(#7/4/2007#,#5/28/2007#)))>>>>>> -->> Doug Steele, Microsoft Access MVP>> http://I.Am/DougSteele>> (no private e-mails, please)>>>>>> "Supple" <[email protected]> wrote in message>> news:[email protected]...>> >I have set up the addworkday module from>> > http://www.mvps.org/access/datetime/date0012.htm>> >>> > And it works great if I don't add the optional array for the holidays.>> >>> > However, I do want to include the holidays and when I add the array tothe>> > query:>> >>> > CreationDeadline:>> >dhAddWorkDaysA(3,[TblQCCompletedDates]![CreationReceived],Array(#7/4/2007#,#5/28/2007#))>> >>> > I receive the following error:>> >>> > Datatype Mismatch in criteria expression. I have everything set to"short>> > date" for my formats. What can I do to make this work? Do I need to do>> > something with the input masks?>> >>> > Thanks in advance for your help.>> >>>>>>>

 

[Supple]

I think I am going to go the way to create a table like the one you included.
I have been having a bit of difficulty trying to create a table to do this.
Could you provide the code to do this?

Thanks from a newbie.

I have set up the addworkday module from
http://www.mvps.org/access/datetime/date0012.htm

And it works great if I don't add the optional array for the holidays.

However, I do want to include the holidays and when I add the array to the
query:

CreationDeadline:
dhAddWorkDaysA(3,[TblQCCompletedDates]![CreationReceived],Array(#7/4/2007#,#5/28/2007#))

I receive the following error:

Datatype Mismatch in criteria expression. I have everything set to "short
date" for my formats. What can I do to make this work? Do I need to do
something with the input masks?

Thanks in advance for your help.

If you would consisder using a calendar table, then maybe look at this
http://www.psci.net/gramelsp/temp/calendars.zip

 

[Michael Gramelspacher]

I think I am going to go the way to create a table like the one you included.
I have been having a bit of difficulty trying to create a table to do this.
Could you provide the code to do this?

Thanks from a newbie.

I have set up the addworkday module from
http://www.mvps.org/access/datetime/date0012.htm

And it works great if I don't add the optional array for the holidays.

However, I do want to include the holidays and when I add the array to the
query:

CreationDeadline:
dhAddWorkDaysA(3,[TblQCCompletedDates]![CreationReceived],Array(#7/4/2007#,#5/28/2007#))

I receive the following error:

Datatype Mismatch in criteria expression. I have everything set to "short
date" for my formats. What can I do to make this work? Do I need to do
something with the input masks?

Thanks in advance for your help.

If you would consisder using a calendar table, then maybe look at this
http://www.psci.net/gramelsp/temp/calendars.zip

I assume you downloaded Calendars.zip and unzipped it. In the database window
of your database click the Modules tab to highlight it. Using the top menu bar
select File/Get External Data/Import and then browse to Calendar.mdb and import
the lone module. Now the code is in your database.

Press Crtl-G and then in the Immediate Window type call CreateCalendar and
press return. You now have an empty calendar table.

Still in the Immediate Window type
call FillCalendar(#1/1/2007#,#12/31/2010#) or whatever dates you need
and press return. You just filled the calendar table with dates.

Now the work begins. Edit the module UpdateCalendarHolidays and
enter holidays for your country for 2007 or whatever year you need.
Then type call UpdateCalendarHolidays and press return.
Do this for each year in you calendar.

After you are finished entering holidays, type call NumberWorkdays
and press return. (You can skip this for now.)

 

[Supple]

Thank you so very much. I spent most of my morning trying to get anything to
work... I didn't know anything about the Immediate Window - learned something
today! I will test this out tomorrow to make sure I did it right, but from
the looks of it, it is working! You definately made my afternoon!! Thanks
again! )

I think I am going to go the way to create a table like the one you included.
I have been having a bit of difficulty trying to create a table to do this.
Could you provide the code to do this?

Thanks from a newbie.

I have set up the addworkday module from
http://www.mvps.org/access/datetime/date0012.htm

And it works great if I don't add the optional array for the holidays.

However, I do want to include the holidays and when I add the array to the
query:

CreationDeadline:
dhAddWorkDaysA(3,[TblQCCompletedDates]![CreationReceived],Array(#7/4/2007#,#5/28/2007#))

I receive the following error:

Datatype Mismatch in criteria expression. I have everything set to "short
date" for my formats. What can I do to make this work? Do I need to do
something with the input masks?

Thanks in advance for your help.


If you would consisder using a calendar table, then maybe look at this
http://www.psci.net/gramelsp/temp/calendars.zip

I assume you downloaded Calendars.zip and unzipped it. In the database window
of your database click the Modules tab to highlight it. Using the top menu bar
select File/Get External Data/Import and then browse to Calendar.mdb and import
the lone module. Now the code is in your database.

Press Crtl-G and then in the Immediate Window type call CreateCalendar and
press return. You now have an empty calendar table.

Still in the Immediate Window type
call FillCalendar(#1/1/2007#,#12/31/2010#) or whatever dates you need
and press return. You just filled the calendar table with dates.

Now the work begins. Edit the module UpdateCalendarHolidays and
enter holidays for your country for 2007 or whatever year you need.
Then type call UpdateCalendarHolidays and press return.
Do this for each year in you calendar.

After you are finished entering holidays, type call NumberWorkdays
and press return. (You can skip this for now.)