replacing date values for last Friday in month

[bimmer5]

Please find attached a sample query...

Is it possible to create the following logic in query:

"If there is a "SHIFT2" or "SHIFT3" value on the last Friday in month,
then change the date values for these entries to the next date (Saturday)"

In the attached sample, there is a number of such values within the "Adv Track Shift" field dated on Friday 04/25/08 in the "shift_date" field. The logic should replace the applicable dates to Saturday 04/26/08. This should work for any month, regardless if it finds either of the two shifts or both of them.

Any suggestion would be highly appreciated.
Thank you,
Peter

 

[EMP]

Your database may have a normalization issue as your table looks more like a spreadsheet than a database table.


I can't find the "Adv Track Shift" field in your table. Nor is there any field containing the words "SHIFT2" or "SHIFT3".

Anyhow, you can use the following as the criteria for an update query to add 1 day to the date field.

[Adv Track Shift] in ("SHIFT2","SHIFT3") AND DatePart("w",[Shift_Date])=6 and DateSerial(year([Shift_Date]),month([Shift_Date])+1,0)-[Shift_Date]<7

^

 

[bimmer5]

I was referring to the "Adv Track Shift", "Shift2" and "Shift3" as calculated fields in Query, not in the database itself. The table is part of MS Access database that is part our time and attendance software. Does that make sense to you?
Now, I tried pasting it your criteria in the Query but somehow it didn't work. I wasn't certain in which field's criteria to post it. When I tried posting it under the "Adv Track Shift" criteria it asks me for Enter Parameter Value for Adv Track Shift.
Then I tried to paste it in the SQL View. It created a new expression field. It returned zero for SHIFT1 - that is fine, however it returned (-1) values for SHIFT2 and SHIFT3. Shouldn't be opposite, so that I can increase the date from 04/25 to 04/26? Anyway, it looks like we are getting there. The last question that I wanted to ask... could we integrate this expression into the "shift_date" field (within Query) so that I do not have to create another calculated date field? That would save me lots of work modifying the Crystal Report that I built based on this Query and the date itself.
EMP, Thank you very much for your time and interest in helping me with this challenge. Looking forward to hear back from you.
Peter

 

[EMP]

I'm sorry. I overlooked your query.

See the field "AdjustedShiftDate" in my query, which is based on your query.


If you want to incorporate my expression in your query, you need to replace [Adv Track Shift] with its calculated expression.

Correction:
My expression can be used directly in your query. There is no need to replace [Adv Track Shift] with its calculated expression.

^

 

[bimmer5]

EMP,
just wanted to thank you one more time, though I didn't have chance to work on it today, it looks good from what I see.
All the best to you,
TX!!!