Using BETWEEN AND with DatePart

[dlmaley]

In MS Access 97, I am trying to design an update query that will update a
field (SICKTIME) to 0 if another field (STARTDATE) is between 1/2 (January
2nd) and 5/1 (May 1st) in the same year as another field (ASOFDATE). The user
enters the ASOFDATE in another part of the process so that field is already
populated. I am trying to do this with BETWEEN AND and DatePart and can’t get
it to work. This is what I have now as the criteria for updating the field.

Between (DatePart("m",[STARTDATE])=1 And DatePart("d",[STARTDATE])=2 And
DatePart("yyyy",[STARTDATE])=DatePart("yyyy",[ASOFDATE]))
And
(DatePart("m",[STARTDATE])=5 And DatePart("d",[STARTDATE])=1 And
DatePart("yyyy",[STARTDATE])=DatePart("yyyy",[ASOFDATE]))

The query runs but does not update the records it should update. Any help
would be appreciated. Thanks!

 

[Jeff Boyce]

Are you saying that if you treat the query as a "select" query, it returns
the proper rows? If so, how are you converting that to an "update" query?
Where's the "update to" portion?

Regards

Jeff Boyce
Microsoft Office/Access MVP