Group by Weeks of the Month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MartyO - 14 May 2008 17:39 GMT

Is it possible to use one of the Date Functions in a query grid to group my
employee attendance records by the Week of the Month? There is a record if
they are absent with their EmpID, Date Absent etc. I need to give them bonus
points (or not) depending on whether they were absent on any given day in a
particular week (M-F) of the month.
I've looked at all of the Date functions, and can't get a handle on which
one will return Week 1, Week 2 etc Grouped by Month.
Does that make sense?

Thanks in advance!!
Marty

Reply to this Message

MartyO - 14 May 2008 18:54 GMT

Okay, I see now where DatePart("ww",[DateABS]) will return the week number of
the year 1-52.... but is there a way to return the week number of the Month,
1-4 or 1-5?

Marty

> Is it possible to use one of the Date Functions in a query grid to group my
> employee attendance records by the Week of the Month? There is a record if
[quoted text clipped - 7 lines]
> Thanks in advance!!
> Marty

Reply to this Message

KARL DEWEY - 14 May 2008 19:06 GMT

Do not know of week of the month. How will you start counting the week if the
first is on a Wednesday?
But you can try this --
Format([Date Absent],"yyyy mm ") & Right("0" & Format([Date Absent],"ww"),2)

Signature

KARL DEWEY
Build a little - Test a little

Reply to this Message

Dale Fye - 14 May 2008 20:01 GMT

Assuming that weeks start on Sunday, and that week 1 could have between 1 and
7 days in it (as could the last week of the month), then the following
function should work:

Public Function WeekOfMonth(SomeDate As Date) As Integer

Dim FirstWeek As Integer

FirstWeek = DatePart("ww", _
DateSerial(Year(SomeDate), _
Month(SomeDate), _
1), _
vbSunday)
WeekOfMonth = DatePart("ww", SomeDate, vbSunday) - FirstWeek + 1

End Function

You can either use the function, or convert this algorithm to a single line
of code

Signature

HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Reply to this Message

MartyO - 18 May 2008 16:56 GMT

Hi Dale,
Thanks so much for responding. I used your example, and it updated the table
and worked perfectly, but only one time. Now I am consistently getting "Type
mismatch" on the "Month(SomeDate)" part of the code.
I've looked at the "Month" field in my tblAttendance and changed it's data
type and even it's name, thinking that the name of the field was interfering
with the function, but still get Type Mismatch Error 13 or I get a "field"
error, like it thinks the Month(SomeDate) is a field in the tblAttendance.
I have two things that I'm trying to accomplish with your code. (1) update
the entire exisiting table, fields Month and Week based on the date absent
field, (2), incorporate the same code to update those fields on the data
entry form when they enter the date absent for any given employee.
This is the code that I used to update the whole table, and it worked great
but only one time.

Dim db As Database
Dim rec As Recordset
Dim FirstWeek As Integer
Dim WeekOfMonth As Integer
Dim SomeDate As Date

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblAttendance", dbOpenDynaset)
With rec
.MoveFirst
Do While Not rec.EOF
SomeDate = rec!DateABS
FirstWeek = DatePart("ww", DateSerial(Year(SomeDate),
Month(SomeDate), 1), vbSunday)
WeekOfMonth = DatePart("ww", SomeDate, vbSunday) - FirstWeek + 1
.Edit
rec!Month = Format(rec!DateABS, "mmm")
rec!WOM = WeekOfMonth
.Update
.MoveNext
Loop

End With
rec.Close
Set rec = Nothing

I'm so close and you've been such a GREAT help. If you have time I'd
appreciate your thoughts.
Thanks!
Marty

> Assuming that weeks start on Sunday, and that week 1 could have between 1 and
> 7 days in it (as could the last week of the month), then the following
[quoted text clipped - 27 lines]
> > Thanks in advance!!
> > Marty

Reply to this Message

Dale Fye - 20 May 2008 00:29 GMT

Marty,

Actually, now that I know what it is you want to do, I would recommend
against storing computed values in your data tables. You can always
generate these values whenever you want (in your form) using the a function
call or by setting the control source to the appropriate function. This
avoids that possiblity that DateABS will get changed but the other two
fields don't. Additionally, it will save disk space.

Month is a reserved word in Access, and should not be used as a field name;
try something like AttMonth. For a all-inclusive (I'm sure Allen is not
100% complete, but I'll bet its over 99% accurate), list of reserved works
that could cause problems, see Allen Browne's site
(http://allenbrowne.com/AppIssueBadWord.html). Allen has some really great
reference material, check it out.

Don't have a clue why this would only work the first time through. It
should work whenever you run it, although I think I would have done it with
a single SQL query. Something like:

Dim strSQL as string

strSQL = "UPDATE tblAttendance " _
& "SET [AttMonth] = Format(DateABS, 'mmm'), " _
& "[WOM] = DatePart('ww', DateABS, vbSunday) - " _
& "DatePart('ww', DateSerial(Year(DateABS), Month(DateABS), " _
& "1), vbSunday) + 1 " _
& "WHERE DateABS IS NOT NULL"
Currentdb.Execute strSQL

Is there a chance that the DateABS could be NULL? If so, that would
probably generate your error.

Query your recordset to count the number of DateABS = NULL. If it is
greater than zero, then you need to add an IF statement to the loop.
Something like:

Do While not rec.EOF
If NOT ISNULL(rec!DateABS) THEN

.... Insert the code here

END IF
Loop

HTH
Dale

> Hi Dale,
> Thanks so much for responding. I used your example, and it updated the
[quoted text clipped - 85 lines]
>> > Thanks in advance!!
>> > Marty

Reply to this Message

MartyO - 20 May 2008 04:19 GMT

Gosh Dale this is driving me crazy. This should work...it's not that
complicated. I changed every occurance of a field called Month to AttMonth,
and tried using your SQL code below.... the AttMonth field is being populated
just fine, but I'm still getting a type conversion failure and it doesn't
write to the [WOM] field.
I used your example:
strSQL = "UPDATE tblAttendance " _
& "SET [AttMonth] = Format([DateABS], 'mmm'), " _
& "[WOM] = DatePart('ww', [DateABS], 'vbSunday', 1) - " _
& "DatePart('ww', DateSerial(Year([DateABS]), Month([DateABS]),
'vbSunday'), 1) + 1 " _
& "WHERE [DateABS] IS NOT NULL"

Even with the change in the Month field to AttMonth, the first piece of code
I was using prior to this one, still gives me the Type Mismatch on the Month
function.
ARGH! Any other ideas? There are no null dates. If I can get this piece
of code to work I'll add the IF statement to check for null dates. Would
there be a reason that it's not recognizing the Month part of this as a date
function? I'll check the knowledgebase for articles.
Thanks!
Marty

> Marty,
>
[quoted text clipped - 133 lines]
> >> > Thanks in advance!!
> >> > Marty

Reply to this Message

John W. Vinson - 20 May 2008 06:07 GMT

>Gosh Dale this is driving me crazy. This should work...it's not that
>complicated. I changed every occurance of a field called Month to AttMonth,
[quoted text clipped - 16 lines]
>there be a reason that it's not recognizing the Month part of this as a date
>function? I'll check the knowledgebase for articles.

Two things to check: you're using both the Format() function - which returns a
string like "Apr" or "May" - and the Month() function which returns an
integer, 4 or 5. You might need to wrap the DatePart calls in CInt() or Val()
function calls.

You don't have table fields or form controls named Month or Year I hope?
That's another possible source of confusion. Maybe you had a form field named
Month at the time you had a field of that name - changing the fieldname won't
change the control name.

Signature

John W. Vinson [MVP]

Reply to this Message

John Spencer - 20 May 2008 12:29 GMT

First (as far as I know) you cannot use vbSunday in an SQL statement as it is
not available in that environment. So you will have to use 1 in place of it.

strSQL = "UPDATE tblAttendance " _
& "SET [AttMonth] = Format([DateABS], 'mmm'), " _
& "[WOM] = DatePart('ww', [DateABS], 1, 1) - " _
& "DatePart('ww',DateSerial(Year([DateABS]),Month([DateABS]), 1), 1)+1 " _
& "WHERE [DateABS] IS NOT NULL"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Gosh Dale this is driving me crazy. This should work...it's not that
> complicated. I changed every occurance of a field called Month to AttMonth,
[quoted text clipped - 156 lines]
>>>>> Thanks in advance!!
>>>>> Marty

Reply to this Message

MartyO - 20 May 2008 14:55 GMT

Ohmygosh John! You and Dale are my heros! It was the vbSunday in the SQL
statement, I changed it to 1 (did that earlier in testing but in the other
code that I was first using), and it works. Still don't understand why I
can't use the Month function in the first code I was trying to use, but
that's okay, at least now I can move on!
Thank you so much.
This is such a valuable resource for me, and I'm sure many others. Thank you
guys for being there.
Regards,
Marty

> First (as far as I know) you cannot use vbSunday in an SQL statement as it is
> not available in that environment. So you will have to use 1 in place of it.
[quoted text clipped - 170 lines]
> >>>>> Thanks in advance!!
> >>>>> Marty

Reply to this Message

Dale Fye - 22 May 2008 01:34 GMT

John,

Thanks for your input; I probably never would have caught that. In my
original post, I provided him with a function. Marty took that and put it
in the SQL string, so I totally forgot that the variable would not be
available in a SQL statement.

Dale

> First (as far as I know) you cannot use vbSunday in an SQL statement as it
> is not available in that environment. So you will have to use 1 in place
[quoted text clipped - 177 lines]
>>>>>> Thanks in advance!!
>>>>>> Marty

Reply to this Message