Workday Function - Holidays

L

LadyShoe

In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria expression"
What am I doing wrong?
 
D

Douglas J. Steele

Are any of the StartDate values Null? dhAddWorkDaysA will only accept valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value, or you could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date
 
L

LadyShoe

Thanks for replying!!

There are no Nulls. I'll try changing the function.

Again, THANKS
--
Keep the pedal down!


Douglas J. Steele said:
Are any of the StartDate values Null? dhAddWorkDaysA will only accept valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value, or you could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LadyShoe said:
In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria expression"
What am I doing wrong?
Click to expand...
Click to expand...
 
L

LadyShoe

I changed the declaration and still get the same error message. Any other
suggestions?
--
Keep the pedal down!


Douglas J. Steele said:
Are any of the StartDate values Null? dhAddWorkDaysA will only accept valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value, or you could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LadyShoe said:
In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria expression"
What am I doing wrong?
Click to expand...
Click to expand...
 
D

Douglas J. Steele

How are you using the function in your query? Post your SQL. (If you're not
familiar with SQL, open the query in Design view, then select View | SQL
View from the menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LadyShoe said:
I changed the declaration and still get the same error message. Any other
suggestions?
--
Keep the pedal down!


Douglas J. Steele said:
Are any of the StartDate values Null? dhAddWorkDaysA will only accept
valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value, or you
could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LadyShoe said:
In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria expression"
What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
L

LadyShoe

Here's the SQL. Since the holiday array was not working, I have not included
it as part of the workday functions.

SELECT [T Lot Data].[Lot ID], [T Lot Data].[Release Number], [T Lot
Data].[Lot Number], [T Const Schedules].ConstSchedule, [T Const Planned Start
Dates].[Start Date], dhAddWorkDaysA([T Const Schedules]![StakeTrench],[Start
Date]) AS ST, dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS PF,
dhAddWorkDaysA([T Const Schedules]!PourSlab,[PF]) AS PS, dhAddWorkDaysA([T
Const Schedules]![Frame Start],[PS]) AS FS, dhAddWorkDaysA([T Const
Schedules]!FrameComplete,[FS]) AS FC, dhAddWorkDaysA([T Const
Schedules]!ExteriorDryin,[FC]) AS ED, dhAddWorkDaysA([T Const
Schedules]!FrameInspection,[ED]) AS FI, dhAddWorkDaysA([T Const
Schedules]!PreDrywallOrientation,[FI]) AS PDO, dhAddWorkDaysA([T Const
Schedules]!TextureComplete,[PDO]) AS TC, dhAddWorkDaysA([T Const
Schedules]!CabinetsComplete,[TC]) AS CC1, dhAddWorkDaysA([T Const
Schedules]!CarpetComplete,[CC1]) AS CC2, dhAddWorkDaysA([T Const
Schedules]!FinalInspection,[CC2]) AS FI2, dhAddWorkDaysA([T Const
Schedules]!QualityAssurance,[FI2]) AS QuAs, dhAddWorkDaysA([T Const
Schedules]!HomeownerOrientation,[QuAs]) AS HOO, dhAddWorkDaysA([T Const
Schedules]!ReviewandAcceptance,[HOO]) AS RvAc, dhAddWorkDaysA([T Const
Schedules]!ConstCOE,[RvAc]) AS CCOE
FROM [T Const Schedules], [T Const Planned Start Dates] INNER JOIN [T Lot
Data] ON [T Const Planned Start Dates].[Lot ID] = [T Lot Data].[Lot ID]
ORDER BY [T Const Planned Start Dates].[Start Date];

--
Keep the pedal down!


Douglas J. Steele said:
How are you using the function in your query? Post your SQL. (If you're not
familiar with SQL, open the query in Design view, then select View | SQL
View from the menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LadyShoe said:
I changed the declaration and still get the same error message. Any other
suggestions?
--
Keep the pedal down!


Douglas J. Steele said:
Are any of the StartDate values Null? dhAddWorkDaysA will only accept
valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value, or you
could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria expression"
What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What are the data types of the various fields you're using in the function?
Are PourFootings, PourSlab, [Frame Start] and so on Dates, or Long Integers?
What about [ST], [PF] ,[PS], [FS] and so on?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LadyShoe said:
Here's the SQL. Since the holiday array was not working, I have not
included
it as part of the workday functions.

SELECT [T Lot Data].[Lot ID], [T Lot Data].[Release Number], [T Lot
Data].[Lot Number], [T Const Schedules].ConstSchedule, [T Const Planned
Start
Dates].[Start Date], dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start
Date]) AS ST, dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS PF,
dhAddWorkDaysA([T Const Schedules]!PourSlab,[PF]) AS PS, dhAddWorkDaysA([T
Const Schedules]![Frame Start],[PS]) AS FS, dhAddWorkDaysA([T Const
Schedules]!FrameComplete,[FS]) AS FC, dhAddWorkDaysA([T Const
Schedules]!ExteriorDryin,[FC]) AS ED, dhAddWorkDaysA([T Const
Schedules]!FrameInspection,[ED]) AS FI, dhAddWorkDaysA([T Const
Schedules]!PreDrywallOrientation,[FI]) AS PDO, dhAddWorkDaysA([T Const
Schedules]!TextureComplete,[PDO]) AS TC, dhAddWorkDaysA([T Const
Schedules]!CabinetsComplete,[TC]) AS CC1, dhAddWorkDaysA([T Const
Schedules]!CarpetComplete,[CC1]) AS CC2, dhAddWorkDaysA([T Const
Schedules]!FinalInspection,[CC2]) AS FI2, dhAddWorkDaysA([T Const
Schedules]!QualityAssurance,[FI2]) AS QuAs, dhAddWorkDaysA([T Const
Schedules]!HomeownerOrientation,[QuAs]) AS HOO, dhAddWorkDaysA([T Const
Schedules]!ReviewandAcceptance,[HOO]) AS RvAc, dhAddWorkDaysA([T Const
Schedules]!ConstCOE,[RvAc]) AS CCOE
FROM [T Const Schedules], [T Const Planned Start Dates] INNER JOIN [T Lot
Data] ON [T Const Planned Start Dates].[Lot ID] = [T Lot Data].[Lot ID]
ORDER BY [T Const Planned Start Dates].[Start Date];

--
Keep the pedal down!


Douglas J. Steele said:
How are you using the function in your query? Post your SQL. (If you're
not
familiar with SQL, open the query in Design view, then select View | SQL
View from the menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LadyShoe said:
I changed the declaration and still get the same error message. Any
other
suggestions?
--
Keep the pedal down!


:

Are any of the StartDate values Null? dhAddWorkDaysA will only accept
valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value, or you
could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria expression"
What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
L

LadyShoe

The data types in the [T Const Schedules] table fields are Long Integers and
represent the number of days to complete the task. The [T Const Planned
Start Dates]!Start Date is a date field. The ST, PF, PS, FS, etc. are the
expression names. The first expression titled ST takes the start date from
the T Const Planned Start Dates table and adds the number of days to complete
the task from the T Const Schedule table to get the StakeTrench complete
date. The next expression titled PF takes the ST date and adds the number of
days from the [T Const Schedules]![PourFootings] table to get the planned
Pour Footings complete date...and so on. All this works fine until I try to
include the holidays in the expression.

THANKS FOR YOUR HELP!!!!
--
Keep the pedal down!


Douglas J. Steele said:
What are the data types of the various fields you're using in the function?
Are PourFootings, PourSlab, [Frame Start] and so on Dates, or Long Integers?
What about [ST], [PF] ,[PS], [FS] and so on?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LadyShoe said:
Here's the SQL. Since the holiday array was not working, I have not
included
it as part of the workday functions.

SELECT [T Lot Data].[Lot ID], [T Lot Data].[Release Number], [T Lot
Data].[Lot Number], [T Const Schedules].ConstSchedule, [T Const Planned
Start
Dates].[Start Date], dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start
Date]) AS ST, dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS PF,
dhAddWorkDaysA([T Const Schedules]!PourSlab,[PF]) AS PS, dhAddWorkDaysA([T
Const Schedules]![Frame Start],[PS]) AS FS, dhAddWorkDaysA([T Const
Schedules]!FrameComplete,[FS]) AS FC, dhAddWorkDaysA([T Const
Schedules]!ExteriorDryin,[FC]) AS ED, dhAddWorkDaysA([T Const
Schedules]!FrameInspection,[ED]) AS FI, dhAddWorkDaysA([T Const
Schedules]!PreDrywallOrientation,[FI]) AS PDO, dhAddWorkDaysA([T Const
Schedules]!TextureComplete,[PDO]) AS TC, dhAddWorkDaysA([T Const
Schedules]!CabinetsComplete,[TC]) AS CC1, dhAddWorkDaysA([T Const
Schedules]!CarpetComplete,[CC1]) AS CC2, dhAddWorkDaysA([T Const
Schedules]!FinalInspection,[CC2]) AS FI2, dhAddWorkDaysA([T Const
Schedules]!QualityAssurance,[FI2]) AS QuAs, dhAddWorkDaysA([T Const
Schedules]!HomeownerOrientation,[QuAs]) AS HOO, dhAddWorkDaysA([T Const
Schedules]!ReviewandAcceptance,[HOO]) AS RvAc, dhAddWorkDaysA([T Const
Schedules]!ConstCOE,[RvAc]) AS CCOE
FROM [T Const Schedules], [T Const Planned Start Dates] INNER JOIN [T Lot
Data] ON [T Const Planned Start Dates].[Lot ID] = [T Lot Data].[Lot ID]
ORDER BY [T Const Planned Start Dates].[Start Date];

--
Keep the pedal down!


Douglas J. Steele said:
How are you using the function in your query? Post your SQL. (If you're
not
familiar with SQL, open the query in Design view, then select View | SQL
View from the menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I changed the declaration and still get the same error message. Any
other
suggestions?
--
Keep the pedal down!


:

Are any of the StartDate values Null? dhAddWorkDaysA will only accept
valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value, or you
could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria expression"
What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You cannot refer to aliased field names like that.

Rather than:

dhAddWorkDaysA([T Const Schedules]![StakeTrench],[Start Date]) AS ST,
dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS PF,

you need to use

dhAddWorkDaysA([T Const Schedules]![StakeTrench],[Start Date]) AS ST,
dhAddWorkDaysA([T Const Schedules]!PourFootings,dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start Date])) AS PF,

And yes, that may well make the SQL too long (or the query too complicated)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LadyShoe said:
The data types in the [T Const Schedules] table fields are Long Integers
and
represent the number of days to complete the task. The [T Const Planned
Start Dates]!Start Date is a date field. The ST, PF, PS, FS, etc. are the
expression names. The first expression titled ST takes the start date from
the T Const Planned Start Dates table and adds the number of days to
complete
the task from the T Const Schedule table to get the StakeTrench complete
date. The next expression titled PF takes the ST date and adds the number
of
days from the [T Const Schedules]![PourFootings] table to get the planned
Pour Footings complete date...and so on. All this works fine until I try
to
include the holidays in the expression.

THANKS FOR YOUR HELP!!!!
--
Keep the pedal down!


Douglas J. Steele said:
What are the data types of the various fields you're using in the
function?
Are PourFootings, PourSlab, [Frame Start] and so on Dates, or Long
Integers?
What about [ST], [PF] ,[PS], [FS] and so on?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LadyShoe said:
Here's the SQL. Since the holiday array was not working, I have not
included
it as part of the workday functions.

SELECT [T Lot Data].[Lot ID], [T Lot Data].[Release Number], [T Lot
Data].[Lot Number], [T Const Schedules].ConstSchedule, [T Const Planned
Start
Dates].[Start Date], dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start
Date]) AS ST, dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS
PF,
dhAddWorkDaysA([T Const Schedules]!PourSlab,[PF]) AS PS,
dhAddWorkDaysA([T
Const Schedules]![Frame Start],[PS]) AS FS, dhAddWorkDaysA([T Const
Schedules]!FrameComplete,[FS]) AS FC, dhAddWorkDaysA([T Const
Schedules]!ExteriorDryin,[FC]) AS ED, dhAddWorkDaysA([T Const
Schedules]!FrameInspection,[ED]) AS FI, dhAddWorkDaysA([T Const
Schedules]!PreDrywallOrientation,[FI]) AS PDO, dhAddWorkDaysA([T Const
Schedules]!TextureComplete,[PDO]) AS TC, dhAddWorkDaysA([T Const
Schedules]!CabinetsComplete,[TC]) AS CC1, dhAddWorkDaysA([T Const
Schedules]!CarpetComplete,[CC1]) AS CC2, dhAddWorkDaysA([T Const
Schedules]!FinalInspection,[CC2]) AS FI2, dhAddWorkDaysA([T Const
Schedules]!QualityAssurance,[FI2]) AS QuAs, dhAddWorkDaysA([T Const
Schedules]!HomeownerOrientation,[QuAs]) AS HOO, dhAddWorkDaysA([T Const
Schedules]!ReviewandAcceptance,[HOO]) AS RvAc, dhAddWorkDaysA([T Const
Schedules]!ConstCOE,[RvAc]) AS CCOE
FROM [T Const Schedules], [T Const Planned Start Dates] INNER JOIN [T
Lot
Data] ON [T Const Planned Start Dates].[Lot ID] = [T Lot Data].[Lot ID]
ORDER BY [T Const Planned Start Dates].[Start Date];

--
Keep the pedal down!


:

How are you using the function in your query? Post your SQL. (If
you're
not
familiar with SQL, open the query in Design view, then select View |
SQL
View from the menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I changed the declaration and still get the same error message. Any
other
suggestions?
--
Keep the pedal down!


:

Are any of the StartDate values Null? dhAddWorkDaysA will only
accept
valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value, or
you
could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria
expression"
What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
L

LadyShoe

I'll give it a try! Thank you!
--
Keep the pedal down!


Douglas J. Steele said:
You cannot refer to aliased field names like that.

Rather than:

dhAddWorkDaysA([T Const Schedules]![StakeTrench],[Start Date]) AS ST,
dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS PF,

you need to use

dhAddWorkDaysA([T Const Schedules]![StakeTrench],[Start Date]) AS ST,
dhAddWorkDaysA([T Const Schedules]!PourFootings,dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start Date])) AS PF,

And yes, that may well make the SQL too long (or the query too complicated)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LadyShoe said:
The data types in the [T Const Schedules] table fields are Long Integers
and
represent the number of days to complete the task. The [T Const Planned
Start Dates]!Start Date is a date field. The ST, PF, PS, FS, etc. are the
expression names. The first expression titled ST takes the start date from
the T Const Planned Start Dates table and adds the number of days to
complete
the task from the T Const Schedule table to get the StakeTrench complete
date. The next expression titled PF takes the ST date and adds the number
of
days from the [T Const Schedules]![PourFootings] table to get the planned
Pour Footings complete date...and so on. All this works fine until I try
to
include the holidays in the expression.

THANKS FOR YOUR HELP!!!!
--
Keep the pedal down!


Douglas J. Steele said:
What are the data types of the various fields you're using in the
function?
Are PourFootings, PourSlab, [Frame Start] and so on Dates, or Long
Integers?
What about [ST], [PF] ,[PS], [FS] and so on?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Here's the SQL. Since the holiday array was not working, I have not
included
it as part of the workday functions.

SELECT [T Lot Data].[Lot ID], [T Lot Data].[Release Number], [T Lot
Data].[Lot Number], [T Const Schedules].ConstSchedule, [T Const Planned
Start
Dates].[Start Date], dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start
Date]) AS ST, dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS
PF,
dhAddWorkDaysA([T Const Schedules]!PourSlab,[PF]) AS PS,
dhAddWorkDaysA([T
Const Schedules]![Frame Start],[PS]) AS FS, dhAddWorkDaysA([T Const
Schedules]!FrameComplete,[FS]) AS FC, dhAddWorkDaysA([T Const
Schedules]!ExteriorDryin,[FC]) AS ED, dhAddWorkDaysA([T Const
Schedules]!FrameInspection,[ED]) AS FI, dhAddWorkDaysA([T Const
Schedules]!PreDrywallOrientation,[FI]) AS PDO, dhAddWorkDaysA([T Const
Schedules]!TextureComplete,[PDO]) AS TC, dhAddWorkDaysA([T Const
Schedules]!CabinetsComplete,[TC]) AS CC1, dhAddWorkDaysA([T Const
Schedules]!CarpetComplete,[CC1]) AS CC2, dhAddWorkDaysA([T Const
Schedules]!FinalInspection,[CC2]) AS FI2, dhAddWorkDaysA([T Const
Schedules]!QualityAssurance,[FI2]) AS QuAs, dhAddWorkDaysA([T Const
Schedules]!HomeownerOrientation,[QuAs]) AS HOO, dhAddWorkDaysA([T Const
Schedules]!ReviewandAcceptance,[HOO]) AS RvAc, dhAddWorkDaysA([T Const
Schedules]!ConstCOE,[RvAc]) AS CCOE
FROM [T Const Schedules], [T Const Planned Start Dates] INNER JOIN [T
Lot
Data] ON [T Const Planned Start Dates].[Lot ID] = [T Lot Data].[Lot ID]
ORDER BY [T Const Planned Start Dates].[Start Date];

--
Keep the pedal down!


:

How are you using the function in your query? Post your SQL. (If
you're
not
familiar with SQL, open the query in Design view, then select View |
SQL
View from the menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I changed the declaration and still get the same error message. Any
other
suggestions?
--
Keep the pedal down!


:

Are any of the StartDate values Null? dhAddWorkDaysA will only
accept
valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value, or
you
could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria
expression"
What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
L

LadyShoe

I tried the following:
PF: dhAddWorkDaysA([T Const Schedules]![PourFootings],dhAddWorkDaysA([T
Const Schedules]![StakeTrench],[Start
Date],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#)))
and I get the same error message "Data type mismatch in criteria
expression". As soon as I take out the "Array(#5/28/2007.....)" the query
works.
--
Keep the pedal down!


Douglas J. Steele said:
What are the data types of the various fields you're using in the function?
Are PourFootings, PourSlab, [Frame Start] and so on Dates, or Long Integers?
What about [ST], [PF] ,[PS], [FS] and so on?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LadyShoe said:
Here's the SQL. Since the holiday array was not working, I have not
included
it as part of the workday functions.

SELECT [T Lot Data].[Lot ID], [T Lot Data].[Release Number], [T Lot
Data].[Lot Number], [T Const Schedules].ConstSchedule, [T Const Planned
Start
Dates].[Start Date], dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start
Date]) AS ST, dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS PF,
dhAddWorkDaysA([T Const Schedules]!PourSlab,[PF]) AS PS, dhAddWorkDaysA([T
Const Schedules]![Frame Start],[PS]) AS FS, dhAddWorkDaysA([T Const
Schedules]!FrameComplete,[FS]) AS FC, dhAddWorkDaysA([T Const
Schedules]!ExteriorDryin,[FC]) AS ED, dhAddWorkDaysA([T Const
Schedules]!FrameInspection,[ED]) AS FI, dhAddWorkDaysA([T Const
Schedules]!PreDrywallOrientation,[FI]) AS PDO, dhAddWorkDaysA([T Const
Schedules]!TextureComplete,[PDO]) AS TC, dhAddWorkDaysA([T Const
Schedules]!CabinetsComplete,[TC]) AS CC1, dhAddWorkDaysA([T Const
Schedules]!CarpetComplete,[CC1]) AS CC2, dhAddWorkDaysA([T Const
Schedules]!FinalInspection,[CC2]) AS FI2, dhAddWorkDaysA([T Const
Schedules]!QualityAssurance,[FI2]) AS QuAs, dhAddWorkDaysA([T Const
Schedules]!HomeownerOrientation,[QuAs]) AS HOO, dhAddWorkDaysA([T Const
Schedules]!ReviewandAcceptance,[HOO]) AS RvAc, dhAddWorkDaysA([T Const
Schedules]!ConstCOE,[RvAc]) AS CCOE
FROM [T Const Schedules], [T Const Planned Start Dates] INNER JOIN [T Lot
Data] ON [T Const Planned Start Dates].[Lot ID] = [T Lot Data].[Lot ID]
ORDER BY [T Const Planned Start Dates].[Start Date];

--
Keep the pedal down!


Douglas J. Steele said:
How are you using the function in your query? Post your SQL. (If you're
not
familiar with SQL, open the query in Design view, then select View | SQL
View from the menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I changed the declaration and still get the same error message. Any
other
suggestions?
--
Keep the pedal down!


:

Are any of the StartDate values Null? dhAddWorkDaysA will only accept
valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value, or you
could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria expression"
What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
L

LadyShoe

Some additional info:

Here's a portion of the query:

Start
Date ST PF PS FS
9/26/05 9/26/05 10/7/05 10/13/05 10/26/05

To Start date adding 0 workdays, to ST adding 9 workdays, to PF adding 4
workdays, to PS adding 9 workdays

And this is all working the way I wrote the expressions until I add the
holidays to the expression(function). Again, I tried writing the expressions
as you suggested in your last reply and I get the same error message.

I've also set up a table that has two fields - one containing the date (data
type=short date) and the other field is a description (data type=text). This
table lists 21 holidays. When I add it to the query, instead of 143 records
I get 3003 (21 schedules for each lot). However, I don't get the error
message.

--
Keep the pedal down!


Douglas J. Steele said:
You cannot refer to aliased field names like that.

Rather than:

dhAddWorkDaysA([T Const Schedules]![StakeTrench],[Start Date]) AS ST,
dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS PF,

you need to use

dhAddWorkDaysA([T Const Schedules]![StakeTrench],[Start Date]) AS ST,
dhAddWorkDaysA([T Const Schedules]!PourFootings,dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start Date])) AS PF,

And yes, that may well make the SQL too long (or the query too complicated)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LadyShoe said:
The data types in the [T Const Schedules] table fields are Long Integers
and
represent the number of days to complete the task. The [T Const Planned
Start Dates]!Start Date is a date field. The ST, PF, PS, FS, etc. are the
expression names. The first expression titled ST takes the start date from
the T Const Planned Start Dates table and adds the number of days to
complete
the task from the T Const Schedule table to get the StakeTrench complete
date. The next expression titled PF takes the ST date and adds the number
of
days from the [T Const Schedules]![PourFootings] table to get the planned
Pour Footings complete date...and so on. All this works fine until I try
to
include the holidays in the expression.

THANKS FOR YOUR HELP!!!!
--
Keep the pedal down!


Douglas J. Steele said:
What are the data types of the various fields you're using in the
function?
Are PourFootings, PourSlab, [Frame Start] and so on Dates, or Long
Integers?
What about [ST], [PF] ,[PS], [FS] and so on?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Here's the SQL. Since the holiday array was not working, I have not
included
it as part of the workday functions.

SELECT [T Lot Data].[Lot ID], [T Lot Data].[Release Number], [T Lot
Data].[Lot Number], [T Const Schedules].ConstSchedule, [T Const Planned
Start
Dates].[Start Date], dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start
Date]) AS ST, dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS
PF,
dhAddWorkDaysA([T Const Schedules]!PourSlab,[PF]) AS PS,
dhAddWorkDaysA([T
Const Schedules]![Frame Start],[PS]) AS FS, dhAddWorkDaysA([T Const
Schedules]!FrameComplete,[FS]) AS FC, dhAddWorkDaysA([T Const
Schedules]!ExteriorDryin,[FC]) AS ED, dhAddWorkDaysA([T Const
Schedules]!FrameInspection,[ED]) AS FI, dhAddWorkDaysA([T Const
Schedules]!PreDrywallOrientation,[FI]) AS PDO, dhAddWorkDaysA([T Const
Schedules]!TextureComplete,[PDO]) AS TC, dhAddWorkDaysA([T Const
Schedules]!CabinetsComplete,[TC]) AS CC1, dhAddWorkDaysA([T Const
Schedules]!CarpetComplete,[CC1]) AS CC2, dhAddWorkDaysA([T Const
Schedules]!FinalInspection,[CC2]) AS FI2, dhAddWorkDaysA([T Const
Schedules]!QualityAssurance,[FI2]) AS QuAs, dhAddWorkDaysA([T Const
Schedules]!HomeownerOrientation,[QuAs]) AS HOO, dhAddWorkDaysA([T Const
Schedules]!ReviewandAcceptance,[HOO]) AS RvAc, dhAddWorkDaysA([T Const
Schedules]!ConstCOE,[RvAc]) AS CCOE
FROM [T Const Schedules], [T Const Planned Start Dates] INNER JOIN [T
Lot
Data] ON [T Const Planned Start Dates].[Lot ID] = [T Lot Data].[Lot ID]
ORDER BY [T Const Planned Start Dates].[Start Date];

--
Keep the pedal down!


:

How are you using the function in your query? Post your SQL. (If
you're
not
familiar with SQL, open the query in Design view, then select View |
SQL
View from the menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I changed the declaration and still get the same error message. Any
other
suggestions?
--
Keep the pedal down!


:

Are any of the StartDate values Null? dhAddWorkDaysA will only
accept
valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value, or
you
could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria
expression"
What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You might try modifying the code to read the table, rather than including
the table in the query.

It's not that difficult to do, but it's a bit more typing than I can do in a
newsgroup post: you'd have to remove the reference to the array of holidays
everywhere, and change the SkipHolidaysA function to read your table instead
of the array.

Take a look at my September, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access" for how I used a table, rather than passing an
array. You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LadyShoe said:
Some additional info:

Here's a portion of the query:

Start
Date ST PF PS FS
9/26/05 9/26/05 10/7/05 10/13/05 10/26/05

To Start date adding 0 workdays, to ST adding 9 workdays, to PF adding 4
workdays, to PS adding 9 workdays

And this is all working the way I wrote the expressions until I add the
holidays to the expression(function). Again, I tried writing the
expressions
as you suggested in your last reply and I get the same error message.

I've also set up a table that has two fields - one containing the date
(data
type=short date) and the other field is a description (data type=text).
This
table lists 21 holidays. When I add it to the query, instead of 143
records
I get 3003 (21 schedules for each lot). However, I don't get the error
message.

--
Keep the pedal down!


Douglas J. Steele said:
You cannot refer to aliased field names like that.

Rather than:

dhAddWorkDaysA([T Const Schedules]![StakeTrench],[Start Date]) AS ST,
dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS PF,

you need to use

dhAddWorkDaysA([T Const Schedules]![StakeTrench],[Start Date]) AS ST,
dhAddWorkDaysA([T Const Schedules]!PourFootings,dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start Date])) AS PF,

And yes, that may well make the SQL too long (or the query too
complicated)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LadyShoe said:
The data types in the [T Const Schedules] table fields are Long
Integers
and
represent the number of days to complete the task. The [T Const
Planned
Start Dates]!Start Date is a date field. The ST, PF, PS, FS, etc. are
the
expression names. The first expression titled ST takes the start date
from
the T Const Planned Start Dates table and adds the number of days to
complete
the task from the T Const Schedule table to get the StakeTrench
complete
date. The next expression titled PF takes the ST date and adds the
number
of
days from the [T Const Schedules]![PourFootings] table to get the
planned
Pour Footings complete date...and so on. All this works fine until I
try
to
include the holidays in the expression.

THANKS FOR YOUR HELP!!!!
--
Keep the pedal down!


:

What are the data types of the various fields you're using in the
function?
Are PourFootings, PourSlab, [Frame Start] and so on Dates, or Long
Integers?
What about [ST], [PF] ,[PS], [FS] and so on?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Here's the SQL. Since the holiday array was not working, I have not
included
it as part of the workday functions.

SELECT [T Lot Data].[Lot ID], [T Lot Data].[Release Number], [T Lot
Data].[Lot Number], [T Const Schedules].ConstSchedule, [T Const
Planned
Start
Dates].[Start Date], dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start
Date]) AS ST, dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST])
AS
PF,
dhAddWorkDaysA([T Const Schedules]!PourSlab,[PF]) AS PS,
dhAddWorkDaysA([T
Const Schedules]![Frame Start],[PS]) AS FS, dhAddWorkDaysA([T Const
Schedules]!FrameComplete,[FS]) AS FC, dhAddWorkDaysA([T Const
Schedules]!ExteriorDryin,[FC]) AS ED, dhAddWorkDaysA([T Const
Schedules]!FrameInspection,[ED]) AS FI, dhAddWorkDaysA([T Const
Schedules]!PreDrywallOrientation,[FI]) AS PDO, dhAddWorkDaysA([T
Const
Schedules]!TextureComplete,[PDO]) AS TC, dhAddWorkDaysA([T Const
Schedules]!CabinetsComplete,[TC]) AS CC1, dhAddWorkDaysA([T Const
Schedules]!CarpetComplete,[CC1]) AS CC2, dhAddWorkDaysA([T Const
Schedules]!FinalInspection,[CC2]) AS FI2, dhAddWorkDaysA([T Const
Schedules]!QualityAssurance,[FI2]) AS QuAs, dhAddWorkDaysA([T Const
Schedules]!HomeownerOrientation,[QuAs]) AS HOO, dhAddWorkDaysA([T
Const
Schedules]!ReviewandAcceptance,[HOO]) AS RvAc, dhAddWorkDaysA([T
Const
Schedules]!ConstCOE,[RvAc]) AS CCOE
FROM [T Const Schedules], [T Const Planned Start Dates] INNER JOIN
[T
Lot
Data] ON [T Const Planned Start Dates].[Lot ID] = [T Lot Data].[Lot
ID]
ORDER BY [T Const Planned Start Dates].[Start Date];

--
Keep the pedal down!


:

How are you using the function in your query? Post your SQL. (If
you're
not
familiar with SQL, open the query in Design view, then select View
|
SQL
View from the menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I changed the declaration and still get the same error message.
Any
other
suggestions?
--
Keep the pedal down!


:

Are any of the StartDate values Null? dhAddWorkDaysA will only
accept
valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value,
or
you
could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria
expression"
What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
L

LadyShoe

Thanks. I'll print the article and give it a try.
--
Keep the pedal down!


Douglas J. Steele said:
You might try modifying the code to read the table, rather than including
the table in the query.

It's not that difficult to do, but it's a bit more typing than I can do in a
newsgroup post: you'd have to remove the reference to the array of holidays
everywhere, and change the SkipHolidaysA function to read your table instead
of the array.

Take a look at my September, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access" for how I used a table, rather than passing an
array. You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LadyShoe said:
Some additional info:

Here's a portion of the query:

Start
Date ST PF PS FS
9/26/05 9/26/05 10/7/05 10/13/05 10/26/05

To Start date adding 0 workdays, to ST adding 9 workdays, to PF adding 4
workdays, to PS adding 9 workdays

And this is all working the way I wrote the expressions until I add the
holidays to the expression(function). Again, I tried writing the
expressions
as you suggested in your last reply and I get the same error message.

I've also set up a table that has two fields - one containing the date
(data
type=short date) and the other field is a description (data type=text).
This
table lists 21 holidays. When I add it to the query, instead of 143
records
I get 3003 (21 schedules for each lot). However, I don't get the error
message.

--
Keep the pedal down!


Douglas J. Steele said:
You cannot refer to aliased field names like that.

Rather than:

dhAddWorkDaysA([T Const Schedules]![StakeTrench],[Start Date]) AS ST,
dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST]) AS PF,

you need to use

dhAddWorkDaysA([T Const Schedules]![StakeTrench],[Start Date]) AS ST,
dhAddWorkDaysA([T Const Schedules]!PourFootings,dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start Date])) AS PF,

And yes, that may well make the SQL too long (or the query too
complicated)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The data types in the [T Const Schedules] table fields are Long
Integers
and
represent the number of days to complete the task. The [T Const
Planned
Start Dates]!Start Date is a date field. The ST, PF, PS, FS, etc. are
the
expression names. The first expression titled ST takes the start date
from
the T Const Planned Start Dates table and adds the number of days to
complete
the task from the T Const Schedule table to get the StakeTrench
complete
date. The next expression titled PF takes the ST date and adds the
number
of
days from the [T Const Schedules]![PourFootings] table to get the
planned
Pour Footings complete date...and so on. All this works fine until I
try
to
include the holidays in the expression.

THANKS FOR YOUR HELP!!!!
--
Keep the pedal down!


:

What are the data types of the various fields you're using in the
function?
Are PourFootings, PourSlab, [Frame Start] and so on Dates, or Long
Integers?
What about [ST], [PF] ,[PS], [FS] and so on?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Here's the SQL. Since the holiday array was not working, I have not
included
it as part of the workday functions.

SELECT [T Lot Data].[Lot ID], [T Lot Data].[Release Number], [T Lot
Data].[Lot Number], [T Const Schedules].ConstSchedule, [T Const
Planned
Start
Dates].[Start Date], dhAddWorkDaysA([T Const
Schedules]![StakeTrench],[Start
Date]) AS ST, dhAddWorkDaysA([T Const Schedules]!PourFootings,[ST])
AS
PF,
dhAddWorkDaysA([T Const Schedules]!PourSlab,[PF]) AS PS,
dhAddWorkDaysA([T
Const Schedules]![Frame Start],[PS]) AS FS, dhAddWorkDaysA([T Const
Schedules]!FrameComplete,[FS]) AS FC, dhAddWorkDaysA([T Const
Schedules]!ExteriorDryin,[FC]) AS ED, dhAddWorkDaysA([T Const
Schedules]!FrameInspection,[ED]) AS FI, dhAddWorkDaysA([T Const
Schedules]!PreDrywallOrientation,[FI]) AS PDO, dhAddWorkDaysA([T
Const
Schedules]!TextureComplete,[PDO]) AS TC, dhAddWorkDaysA([T Const
Schedules]!CabinetsComplete,[TC]) AS CC1, dhAddWorkDaysA([T Const
Schedules]!CarpetComplete,[CC1]) AS CC2, dhAddWorkDaysA([T Const
Schedules]!FinalInspection,[CC2]) AS FI2, dhAddWorkDaysA([T Const
Schedules]!QualityAssurance,[FI2]) AS QuAs, dhAddWorkDaysA([T Const
Schedules]!HomeownerOrientation,[QuAs]) AS HOO, dhAddWorkDaysA([T
Const
Schedules]!ReviewandAcceptance,[HOO]) AS RvAc, dhAddWorkDaysA([T
Const
Schedules]!ConstCOE,[RvAc]) AS CCOE
FROM [T Const Schedules], [T Const Planned Start Dates] INNER JOIN
[T
Lot
Data] ON [T Const Planned Start Dates].[Lot ID] = [T Lot Data].[Lot
ID]
ORDER BY [T Const Planned Start Dates].[Start Date];

--
Keep the pedal down!


:

How are you using the function in your query? Post your SQL. (If
you're
not
familiar with SQL, open the query in Design view, then select View
|
SQL
View from the menu)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I changed the declaration and still get the same error message.
Any
other
suggestions?
--
Keep the pedal down!


:

Are any of the StartDate values Null? dhAddWorkDaysA will only
accept
valid
dates, not Nulls.

You could use Nz to change the nulls fo some valid date value,
or
you
could
try changing the declaration to

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In a query when I use the following expression
dhAddWorkDaysA(12,[StartDate],Array(#5/28/2007#,#7/4/2007#,#9/3/2007#))
I get an error message "Data Type mismatch in criteria
expression"
What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Workday problems 7
Looking for a way to Force Successor Task to next workday IF 'x' amount of time left in workday 0
Problem with Add Workday function 3
Page numbering with Merged Document and IF statements 1
Paragraph numbering in Word 1
Working Days function 3
Adding days to the WORKDAY function 2
Create Range by joining multiple Ranges - passing multiple holidayranges to Workday function 6

Top