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
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
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
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
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
