Updating field based Between Dates

[Guest]

I want to update sick time accrued to 8 hours when an employee's start date
is between January 2nd and May 1st in the same year as a date entered by a
user. For example, if the date entered by a user is 12/31/2005, and the
employee start date is 1/14/2005, sick time accrued would be updated to 8
hours. If the employee start date is 5/2/2005, the field would not be
updated. If the employee start date is 5/2/2006, the field would not be
updated.

I am currently trying to run this from an update query. The query runs, but
it does not update the records that should be updated. The user enters the
"as of date" in a parameter query that updates the asofdate field in the
Accruals table before this update query is run. I will need to build on this
to add multiple date criteria with different accruals. Here is the SQL.

UPDATE tblAccruals SET tblAccruals.SICKACCRUE = 8
WHERE (((tblAccruals.STARTDATEFT) Between (DatePart("m",[STARTDATEFT])=1 And
DatePart("d",[STARTDATEFT])=2 And
DatePart("yyyy",[STARTDATEFT])=DatePart("yyyy",[asofdate])) And
(DatePart("m",[STARTDATEFT])=5 And DatePart("d",[STARTDATEFT])=1 And
DatePart("yyyy",[STARTDATEFT])=DatePart("yyyy",[asofdate]))));

How do I get this to update the correct records? Thanks for any assistance.

Debby

 

[Jeff L]

Try this:

UPDATE tblAccruals SET tblAccruals.SICKACCRUE = 8
WHERE (((tblAccruals.STARTDATEFT) Between "1/2/" & Year([AsOfDate]) And
"5/31/" & Year([AsOfDate])

 

[Guest]

Awesome, this works. Thanks very much Jeff!