Klatuu,
I have the function written in, now I just need some assistance on getting
it displayed on my report. I made a text box with the control source =
[calcworkdays]. Is this right? I tried it like this but it displays
"#error" on the report.
:
No, something else must be wrong. You should get the dialog that gives you
the same three options you have in a form. What, exactly are you clicking
on? Text boxes don't have events. Only sections (report header, detail
header, group header, and footers have events)
:
I'll use the fuction below. And thank you again for your response.
Although, i do have another question. It's not giving me the option to click
on "code builder" when I click the "..." button. It just takes me to the
build function. Is it because I'm in a report and not a form?
:
Tell you boss I want to talk to him
You would still use the function I gave you as a separate call. The number
of working days returned is exaclty the same as the current day's number. It
will return exactly what you want.
To get the number of days in the month (for example August 2005:
=CalcWorkDays(#8/1/2005#, #8/31/2005#) will return 23 (get your calendar out
and check it)
To get what workday of the month it is:
=CalcWorkDays(#8/1/2005#, #8/22/2005#) will return 16 (get your boss to
check his calendar, 8/22/2005 is the 16th working day of the month of August,
2005
:
I totally agree, but my boss doesn't. He'd like to pull the whole month and
then tell what today's selling day is.
:
Good suggestion, Douglas. I think that would be the easiest way to do that.
:
Why not use the function Klatuu gave you to determine the number of working
days between August 1st and August 22nd?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Thank you for your response. If I want to know what working day today is
during the month or date range, could I just create another text box with
the
function
=date() - [calcworkdays]. Say I run the report for August 1st thru August
31, but today's date is August 22nd. The below function lets me know how
many working days are from August 1st thru August 31st, but is there a way
to
let me know that August 22nd is the 16th working day?
:
It may not be necessary to create a table with every day in it. If
selling
days are all weekdays and perhaps excluding holidays, then here is a
function
that will give you the total number of working days for a range of dates.
It
references a table named holidays and the date field holidate. It will
exclude all Saturdays, Sundays and days in the table. My table has only
two
fields, holidate and description.
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'D Hargis
'Calculated the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days if they are non holiday
week days or in the holidate table
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total
days
'Add one to
include
First Day
dtmToday = dtmStart 'Initiate compare
date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday
or
Sunday
intTotalDays = intTotalDays - 1 'Take one day
away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day
away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for
next
compare
Loop 'Until dtmToday > dtmEnd All days have
been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function
:
Okay, here's one for the MVP's.
I have built a report that gives me the sum of my teams sales for the
month.
What I would like to do is determine the number of selling days for
the
month, and then write a formula that calculates the percentage of the
employee's quota they've met based on how many selling days they've
sold.
I'm guessing I'll have to build a table for each month listing what
selling
day it is for each day of the week. (weekends excluded). Then do a
lookup
on today's date against that table to find what selling day it is for
the
month. From there, I don't know how I can right the formula to
calculate the
percentage.
