Modules in Query

[Guest]

I have been trying to use the code for calculating working days that i placed
in a module but have not had much success. How does one utilize or insert the
module in a query so the function will work? I have two fields in the query ,
one for enter date and another recvd date but i am stumped!

 

[Jeff Boyce]

Your description doesn't indicate what you've already tried and ruled out.

I'll make some assumptions (try posting your actual code/function if my
assumptions are off)...

You have a function that takes two parameters (roughly, StartDate and
EndDate) and calculates the working days between them.

To use that function in a query, you could add a new field
(say, "WorkingDays: =fnYourFunction([YourStartDate],[YourEndDate]")
where you'd enter everything between the quotes (but not the quotes) in a
field "cell" in the query design window, and where you'd substitute the
fields that correspond from your data. This means you'd need to have the
table with those fields selected in the upper half of the query design
window, so Access could see the fields.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Thanks Jeff. I have the code from calculating the business days as follows:

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

In my table, i have the dates fields that i would like to apply this code
but unsure how to intergrate it into a query. Do i paste this code into a
module and somehow associate it to the query?

Your description doesn't indicate what you've already tried and ruled out.

I'll make some assumptions (try posting your actual code/function if my
assumptions are off)...

You have a function that takes two parameters (roughly, StartDate and
EndDate) and calculates the working days between them.

To use that function in a query, you could add a new field
(say, "WorkingDays: =fnYourFunction([YourStartDate],[YourEndDate]")
where you'd enter everything between the quotes (but not the quotes) in a
field "cell" in the query design window, and where you'd substitute the
fields that correspond from your data. This means you'd need to have the
table with those fields selected in the upper half of the query design
window, so Access could see the fields.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have been trying to use the code for calculating working days that i placed
in a module but have not had much success. How does one utilize or insert the
module in a query so the function will work? I have two fields in the query ,
one for enter date and another recvd date but i am stumped!

 

[pietlinden]

Thanks Jeff. I have the code from calculating the business days as follows:

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

In my table, i have the dates fields that i would like to apply this code
but unsure how to intergrate it into a query. Do i paste this code into a
module and somehow associate it to the query?

Your description doesn't indicate what you've already tried and ruled out.

I'll make some assumptions (try posting your actual code/function if my
assumptions are off)...

You have a function that takes two parameters (roughly, StartDate and
EndDate) and calculates the working days between them.

To use that function in a query, you could add a new field
(say, "WorkingDays: =fnYourFunction([YourStartDate],[YourEndDate]")
where you'd enter everything between the quotes (but not the quotes) in a
field "cell" in the query design window, and where you'd substitute the
fields that correspond from your data. This means you'd need to have the
table with those fields selected in the upper half of the query design
window, so Access could see the fields.
Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

You create a query and include your custom function in the query...

Say you have a table called "tblEventDates", with two fields
"BeginningDate" and "EndingDate". Then you could calculate the number
of weekdays between them like this:

SELECT tblEventDates.BeginningDate, tblEventDates.EndingDate,
WorkingDays(tblEventDates.BeginningDate, tblEventDates.EndingDate) As
DaysBetween
FROM tblEventDates;

Simple as that... If you use the QBE, you have to scroll down to the
functions section, then select yours. you might need to make it
public (Can't remember that part) in order to use it in the query.

 

[Jeff Boyce]

Please take another look at my response. The description should cover how
to do this in a query.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks Jeff. I have the code from calculating the business days as follows:

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

In my table, i have the dates fields that i would like to apply this code
but unsure how to intergrate it into a query. Do i paste this code into a
module and somehow associate it to the query?

Your description doesn't indicate what you've already tried and ruled out.

I'll make some assumptions (try posting your actual code/function if my
assumptions are off)...

You have a function that takes two parameters (roughly, StartDate and
EndDate) and calculates the working days between them.

To use that function in a query, you could add a new field
(say, "WorkingDays: =fnYourFunction([YourStartDate],[YourEndDate]")
where you'd enter everything between the quotes (but not the quotes) in a
field "cell" in the query design window, and where you'd substitute the
fields that correspond from your data. This means you'd need to have the
table with those fields selected in the upper half of the query design
window, so Access could see the fields.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have been trying to use the code for calculating working days that i placed
in a module but have not had much success. How does one utilize or

insert
the

module in a query so the function will work? I have two fields in the query ,
one for enter date and another recvd date but i am stumped!

 

[Guest]

FWIW (and after reading replies from several other people), I have several
databases that do all sorts of date-related things, and over time I've found
it useful to have and link to a MasterCal table in its own database. It has
all the calendar dates for the next several years, along with other fields
such as Holiday, FirstWeekOfMonth, etc. (Most of these other fields relate
to my company's approved holiday plan and manufacturing month structure, not
to the standard calendar.)

Using various domain aggregate functions against this table, it's fairly
easy to arrive at the date difference in either true calendar days, or
weekdays only, or WORKdays only, or whatever, between two given dates. I've
found it well worth the effort of creating and maintaining the MasterCal
table; you might want to consider the idea too.

 

[Guest]

Larry:

After reading your recommendation to have a MasterCal Table - Did you create
your own table or were you able to download a table to use?

If you created a table could you tell me what items you found important for
your calulations.

I need to sum various 7 day periods , # fields.

I would like to enter an ending date and receive back twelve 7 day periods
from the ending date.

Debbie
SMS

 

[Guest]

I created my own, since it's fairly specific to the kinds of date stuff I
need to do in my work for Raytheon. Over time it has evolved; at the moment
it starts out with TrueDate, then has corresponding fields for YYYYMMDD,
MMonth (YYYYMM), MWeek (YYYYWW), FirstWeekOfMonth (T/F), LastWeekOfMonth
(T/F), Holiday (T/F), DayOfWeek. As you see, that's almost all pretty
specific to my company, but once it was set up, it made it pretty easy for me
to figure out stuff like "how many manufacturing days between date x and date
y?" or "does date x fall within the first week of its manufacturing month, in
which case I need to do process A rather than process B?" or "if it takes 73
manufacturing days to build this gizmo, when do I have to start in order to
have it ready for the customer on July 23rd?"

A couple of the fields, of course, could be handled with the VBA Format()
function, but I threw them in here anyway for my convenience.

As to your specific need, are you saying you have some value (sales or
whatever) for each day, and you want sums for 12 retroactive 7-day periods,
whether or not they match up to an actual calendar week? If that's correct,
you'd need a VBA loop that
(1) Takes your specified ending date
(2) Counts back 12 weeks (83 days, since you want your ending date to be day
84)
(3) Sums your sales or whatever for >= your start date and <= your start
date + 6
(4) Displays that result as Week 1 (or stores it in a variable or table)
(5) Resets your start date to start date + 7
(6) Repeats steps 3, 4, and 5, giving you Week 2
(7) and keeps repeating until your start date exceeds your ending date

That's not terribly hard to write; if you're not a VBA person, let me know
and I'll write it for you. HOWEVER: If you need to do sexier stuff like not
count holidays, it gets harder, and that's where a MasterCal-type table would
come in.