Calculation help Please!!!

[Cam]

Hello,

I got a query that I try to calculate the number of days at several
operation, but not sure how to go about calculating in query. In the query I
got the field for order#, workcenter, oper#, finishdate and other. I would
like to calculate number of work days exluding holiday at each workcenter.
sample data:

order# oper workcenter finishdate
0110 5 ABC 1/1/08
0110 10 ABC 1/3/08
0110 20 ABC 1/4/08
0110 30 ZZZ 1/6/08
0110 40 DVD 1/7/08
0110 50 DVD 1/9/08
0110 70 VHR 1/14/08

Results would like to acheive:
workcenter oper #ofdays
ABC 5, 10, 20 4 (1/1/08 to 1/4/08)
ZZZ 30 1 (1/4/08 to 1/6/08: 1/5 & 6 weekend)
DVD 40, 50 3 (1/6/08 to 1/9/08)
VHR 70 3 (1/9/08 to 1/14/08: 1/12 & 13 weekend)

To calculate the flow days, I used the following formula in Excel, but not
sure how to translate that into Access.
=NETWORKDAYS($O7,$O8,Holiday!$A$2:$A$24)

Thanks so much for any help.

 

[Klatuu]

Here is a function that will return the number of work days between two dates.
If you do not already have a holiday table, you will need one.
To call it from a query, use a calculated field.
'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

 

[Cam]

Klatuu,

Thank for your help, but I am not sure where to put this code? in query/
form etc..

Here is a function that will return the number of work days between two dates.
If you do not already have a holiday table, you will need one.
To call it from a query, use a calculated field.
'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP

Hello,

I got a query that I try to calculate the number of days at several
operation, but not sure how to go about calculating in query. In the query I
got the field for order#, workcenter, oper#, finishdate and other. I would
like to calculate number of work days exluding holiday at each workcenter.
sample data:

order# oper workcenter finishdate
0110 5 ABC 1/1/08
0110 10 ABC 1/3/08
0110 20 ABC 1/4/08
0110 30 ZZZ 1/6/08
0110 40 DVD 1/7/08
0110 50 DVD 1/9/08
0110 70 VHR 1/14/08

Results would like to acheive:
workcenter oper #ofdays
ABC 5, 10, 20 4 (1/1/08 to 1/4/08)
ZZZ 30 1 (1/4/08 to 1/6/08: 1/5 & 6 weekend)
DVD 40, 50 3 (1/6/08 to 1/9/08)
VHR 70 3 (1/9/08 to 1/14/08: 1/12 & 13 weekend)

To calculate the flow days, I used the following formula in Excel, but not
sure how to translate that into Access.
=NETWORKDAYS($O7,$O8,Holiday!$A$2:$A$24)

Thanks so much for any help.

 

[Klatuu]

It goes in a standard module.
How do you intend to use it?
--
Dave Hargis, Microsoft Access MVP

Klatuu,

Thank for your help, but I am not sure where to put this code? in query/
form etc..

Here is a function that will return the number of work days between two dates.
If you do not already have a holiday table, you will need one.
To call it from a query, use a calculated field.
'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP

Hello,

I got a query that I try to calculate the number of days at several
operation, but not sure how to go about calculating in query. In the query I
got the field for order#, workcenter, oper#, finishdate and other. I would
like to calculate number of work days exluding holiday at each workcenter.
sample data:

order# oper workcenter finishdate
0110 5 ABC 1/1/08
0110 10 ABC 1/3/08
0110 20 ABC 1/4/08
0110 30 ZZZ 1/6/08
0110 40 DVD 1/7/08
0110 50 DVD 1/9/08
0110 70 VHR 1/14/08

Results would like to acheive:
workcenter oper #ofdays
ABC 5, 10, 20 4 (1/1/08 to 1/4/08)
ZZZ 30 1 (1/4/08 to 1/6/08: 1/5 & 6 weekend)
DVD 40, 50 3 (1/6/08 to 1/9/08)
VHR 70 3 (1/9/08 to 1/14/08: 1/12 & 13 weekend)

To calculate the flow days, I used the following formula in Excel, but not
sure how to translate that into Access.
=NETWORKDAYS($O7,$O8,Holiday!$A$2:$A$24)

Thanks so much for any help.

 

[Cam]

Hi again,

I want the calculated number of days to be input in the same query so the
results in the query has the following fields.

workcenter oper #ofdays
ABC 5, 10, 20 4
ZZZ 30 1
DVD 40, 50 3
VHR 70 3

Note: the oper field can shows all operations like above or just the last
operation. Basically what I want to calculate is what is the lead time (# of
days) for each workcenter. Some workcenter have one operation or multiple
operations.

The caculation would look like this if I do it in spreadsheet.

workcenter #ofdays (how the date is calculated)
ABC 4 1/4/08 minus 1/1/08 (exclude holiday & weekend)
ZZZ 1 1/6/08 minus 1/4/08 "
DVD 3 1/9/08 minus 1/6/08 "
VHR 3 1/14/08 minus 1/9/08 "

It goes in a standard module.
How do you intend to use it?
--
Dave Hargis, Microsoft Access MVP

Klatuu,

Thank for your help, but I am not sure where to put this code? in query/
form etc..

Here is a function that will return the number of work days between two dates.
If you do not already have a holiday table, you will need one.
To call it from a query, use a calculated field.
'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


:

Hello,

I got a query that I try to calculate the number of days at several
operation, but not sure how to go about calculating in query. In the query I
got the field for order#, workcenter, oper#, finishdate and other. I would
like to calculate number of work days exluding holiday at each workcenter.
sample data:

order# oper workcenter finishdate
0110 5 ABC 1/1/08
0110 10 ABC 1/3/08
0110 20 ABC 1/4/08
0110 30 ZZZ 1/6/08
0110 40 DVD 1/7/08
0110 50 DVD 1/9/08
0110 70 VHR 1/14/08

Results would like to acheive:
workcenter oper #ofdays
ABC 5, 10, 20 4 (1/1/08 to 1/4/08)
ZZZ 30 1 (1/4/08 to 1/6/08: 1/5 & 6 weekend)
DVD 40, 50 3 (1/6/08 to 1/9/08)
VHR 70 3 (1/9/08 to 1/14/08: 1/12 & 13 weekend)

To calculate the flow days, I used the following formula in Excel, but not
sure how to translate that into Access.
=NETWORKDAYS($O7,$O8,Holiday!$A$2:$A$24)

Thanks so much for any help.

 

[Klatuu]

Then you use a calculated field in your query to return the number of days:
NbrOfDays: CalcWorkDays([StartDate], [EndDate])
--
Dave Hargis, Microsoft Access MVP

Hi again,

I want the calculated number of days to be input in the same query so the
results in the query has the following fields.

workcenter oper #ofdays
ABC 5, 10, 20 4
ZZZ 30 1
DVD 40, 50 3
VHR 70 3

Note: the oper field can shows all operations like above or just the last
operation. Basically what I want to calculate is what is the lead time (# of
days) for each workcenter. Some workcenter have one operation or multiple
operations.

The caculation would look like this if I do it in spreadsheet.

workcenter #ofdays (how the date is calculated)
ABC 4 1/4/08 minus 1/1/08 (exclude holiday & weekend)
ZZZ 1 1/6/08 minus 1/4/08 "
DVD 3 1/9/08 minus 1/6/08 "
VHR 3 1/14/08 minus 1/9/08 "

It goes in a standard module.
How do you intend to use it?
--
Dave Hargis, Microsoft Access MVP

Klatuu,

Thank for your help, but I am not sure where to put this code? in query/
form etc..

:

Here is a function that will return the number of work days between two dates.
If you do not already have a holiday table, you will need one.
To call it from a query, use a calculated field.
'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


:

Hello,

I got a query that I try to calculate the number of days at several
operation, but not sure how to go about calculating in query. In the query I
got the field for order#, workcenter, oper#, finishdate and other. I would
like to calculate number of work days exluding holiday at each workcenter.
sample data:

order# oper workcenter finishdate
0110 5 ABC 1/1/08
0110 10 ABC 1/3/08
0110 20 ABC 1/4/08
0110 30 ZZZ 1/6/08
0110 40 DVD 1/7/08
0110 50 DVD 1/9/08
0110 70 VHR 1/14/08

Results would like to acheive:
workcenter oper #ofdays
ABC 5, 10, 20 4 (1/1/08 to 1/4/08)
ZZZ 30 1 (1/4/08 to 1/6/08: 1/5 & 6 weekend)
DVD 40, 50 3 (1/6/08 to 1/9/08)
VHR 70 3 (1/9/08 to 1/14/08: 1/12 & 13 weekend)

To calculate the flow days, I used the following formula in Excel, but not
sure how to translate that into Access.
=NETWORKDAYS($O7,$O8,Holiday!$A$2:$A$24)

Thanks so much for any help.

 

[Cam]

Klatuu,

My problem is I don't have a startdate and enddate in two different column
(field) in my query. If you look at my query data sample, there is only one
date field (finishdate). I am trying to calculate the difference in days from
the next date from the previous date.

I hope I explained my problem. Thanks

Then you use a calculated field in your query to return the number of days:
NbrOfDays: CalcWorkDays([StartDate], [EndDate])
--
Dave Hargis, Microsoft Access MVP

Hi again,

I want the calculated number of days to be input in the same query so the
results in the query has the following fields.

workcenter oper #ofdays
ABC 5, 10, 20 4
ZZZ 30 1
DVD 40, 50 3
VHR 70 3

Note: the oper field can shows all operations like above or just the last
operation. Basically what I want to calculate is what is the lead time (# of
days) for each workcenter. Some workcenter have one operation or multiple
operations.

The caculation would look like this if I do it in spreadsheet.

workcenter #ofdays (how the date is calculated)
ABC 4 1/4/08 minus 1/1/08 (exclude holiday & weekend)
ZZZ 1 1/6/08 minus 1/4/08 "
DVD 3 1/9/08 minus 1/6/08 "
VHR 3 1/14/08 minus 1/9/08 "

It goes in a standard module.
How do you intend to use it?
--
Dave Hargis, Microsoft Access MVP


:

Klatuu,

Thank for your help, but I am not sure where to put this code? in query/
form etc..

:

Here is a function that will return the number of work days between two dates.
If you do not already have a holiday table, you will need one.
To call it from a query, use a calculated field.
'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


:

Hello,

I got a query that I try to calculate the number of days at several
operation, but not sure how to go about calculating in query. In the query I
got the field for order#, workcenter, oper#, finishdate and other. I would
like to calculate number of work days exluding holiday at each workcenter.
sample data:

order# oper workcenter finishdate
0110 5 ABC 1/1/08
0110 10 ABC 1/3/08
0110 20 ABC 1/4/08
0110 30 ZZZ 1/6/08
0110 40 DVD 1/7/08
0110 50 DVD 1/9/08
0110 70 VHR 1/14/08

Results would like to acheive:
workcenter oper #ofdays
ABC 5, 10, 20 4 (1/1/08 to 1/4/08)
ZZZ 30 1 (1/4/08 to 1/6/08: 1/5 & 6 weekend)
DVD 40, 50 3 (1/6/08 to 1/9/08)
VHR 70 3 (1/9/08 to 1/14/08: 1/12 & 13 weekend)

To calculate the flow days, I used the following formula in Excel, but not
sure how to translate that into Access.
=NETWORKDAYS($O7,$O8,Holiday!$A$2:$A$24)

Thanks so much for any help.

 

[Klatuu]

Then a query is not going to do it. You will need to do recordset processing.
--
Dave Hargis, Microsoft Access MVP

Klatuu,

My problem is I don't have a startdate and enddate in two different column
(field) in my query. If you look at my query data sample, there is only one
date field (finishdate). I am trying to calculate the difference in days from
the next date from the previous date.

I hope I explained my problem. Thanks

Then you use a calculated field in your query to return the number of days:
NbrOfDays: CalcWorkDays([StartDate], [EndDate])
--
Dave Hargis, Microsoft Access MVP

Hi again,

I want the calculated number of days to be input in the same query so the
results in the query has the following fields.

workcenter oper #ofdays
ABC 5, 10, 20 4
ZZZ 30 1
DVD 40, 50 3
VHR 70 3

Note: the oper field can shows all operations like above or just the last
operation. Basically what I want to calculate is what is the lead time (# of
days) for each workcenter. Some workcenter have one operation or multiple
operations.

The caculation would look like this if I do it in spreadsheet.

workcenter #ofdays (how the date is calculated)
ABC 4 1/4/08 minus 1/1/08 (exclude holiday & weekend)
ZZZ 1 1/6/08 minus 1/4/08 "
DVD 3 1/9/08 minus 1/6/08 "
VHR 3 1/14/08 minus 1/9/08 "


:

It goes in a standard module.
How do you intend to use it?
--
Dave Hargis, Microsoft Access MVP


:

Klatuu,

Thank for your help, but I am not sure where to put this code? in query/
form etc..

:

Here is a function that will return the number of work days between two dates.
If you do not already have a holiday table, you will need one.
To call it from a query, use a calculated field.
'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


:

Hello,

I got a query that I try to calculate the number of days at several
operation, but not sure how to go about calculating in query. In the query I
got the field for order#, workcenter, oper#, finishdate and other. I would
like to calculate number of work days exluding holiday at each workcenter.
sample data:

order# oper workcenter finishdate
0110 5 ABC 1/1/08
0110 10 ABC 1/3/08
0110 20 ABC 1/4/08
0110 30 ZZZ 1/6/08
0110 40 DVD 1/7/08
0110 50 DVD 1/9/08
0110 70 VHR 1/14/08

Results would like to acheive:
workcenter oper #ofdays
ABC 5, 10, 20 4 (1/1/08 to 1/4/08)
ZZZ 30 1 (1/4/08 to 1/6/08: 1/5 & 6 weekend)
DVD 40, 50 3 (1/6/08 to 1/9/08)
VHR 70 3 (1/9/08 to 1/14/08: 1/12 & 13 weekend)

To calculate the flow days, I used the following formula in Excel, but not
sure how to translate that into Access.
=NETWORKDAYS($O7,$O8,Holiday!$A$2:$A$24)

Thanks so much for any help.