Hello,

Would appreciate any advice on a date function question. Using Access 97, I need to enter timeslips, beginning with job date (could be any day of the week). Week runs Sunday to Saturday. Is there a way to have Access auto-fill the field week ending based on the date I enter under job date. Week ending will always be a Saturday. Job date could be a Saturday also if they work overtime, in which case job date and week ending would be same date.

Thanks for any advice. I checked the prior posts and found a sample database for date functions, but don't know how to get just that one function to work in my database.

Appreciate your help.

Lily

In the After Update event of your job date text box,

Me.txtWeekEnding = DateAdd("d", 7 - DatePart("w", txtJobDate), txtJobDate)


This calculation will always return Saturday's date of the job date week.

Rich,

Thanks very much for your help. I did put this into my After Update event of the job date text box, however, if I go to my form and type in a date it returns a "compile error, syntax error" message.

My first field is Job Date and second field is W/E. I typed in Me.txtWeekEnding = DateAdd("d",7 - DatePart("w",txtJobDate), txtJobDate)

Do I need to delete W/E field and make it an unbound text box maybe? Both fields are time/date format with input masks so that if I type 05/03/04 it will fill in 03-MAY-04.

Thanks again, sincerely, for any advice.

Best regards,

Lily

If you want the week ending calculation stored in your table you would want to keep your text box bound to the W/E field. The date formatting is not a problem either.

A syntax error message would indicate that you may have typed something wrong in the code. Double check it and make sure that your 2 text boxes are named "txtJobDate" and "txtWeekEnding".

Rich,

Thank you!! It works beautifully. I did not realize and had not changed the two text boxes so that they were named "txtJobDate" and "txtWeekEnding". Did as you suggested, renamed them both, and it worked like a charm. Thanks a million.

Best regards,

Lily