First and Last Working Days of Month

[BLFOSTER]

Hi. I want to count the number of entries in a field between the first working day of the month and the last working day of the month. I found a couple of functions seen below but just get #error# in my textbox.



Example:

April 2014 would be March 31st to May 2nd:

=DCount("*","MyQuery","[MyDate] between FirstWorkday(Month()) and LastWorkday(Month())")

Any ideas?

Thanks!

[June7]

The Month() function has a required argument. Do you want the criteria to be the current month? Do the functions return date value or string? If the function returns a string will probably need # delimiters.

=DCount("*","MyQuery","[MyDate] between #FirstWorkday(Month(Date()))# and # LastWorkday(Month(Date()))#")

Might have to concatenate calls to custom functions.

=DCount("*","MyQuery","[MyDate] between #" & FirstWorkday(Month(Date())) & "# and #" & LastWorkday(Month(Date())) & "#")

[BLFOSTER]

Neither of the above examples work, but I think that is due to the functions. Upon further inspection, the functions I found are not what I need - it appears their purpose is to exclude holidays and week-ends in the month. I guess the criteria would have to be the previous month....we are in May, but I want to return a count for April (which includes May 1 and 2 and March 31). I actually have no idea how to get where I need to go with this..........

[June7]

What do you mean April includes May 1 and 2 and March 31? You want beginning and ending of week? Never seen that for a monthly aggregation and does complicate.

[BLFOSTER]

Yes, the beginning of the first week and the end of the last week in the month.....regardless of the actual date.

[June7]

1. take first day date and return date of first day of that week

2. take last day date and return date of last day of that week

Review http://www.tek-tips.com/faqs.cfm?fid=3103