Date Function Help

[Lily]

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

 

[RichO]

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.

 

[Lily]

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

 

[RichO]

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".

 

[Lily]

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