Choosing correct Week Commencing...

[mrf1xa]

Hi

I am building a time- recording sheet within an exisiting db. This form enables each person (resource) to enter, each day, how much time they spend on each project.

I also have a table of 'week commencing' dates.

The issue is, on my form (frmTimesheet) how can I make it so that when the date field (Workday)is updated, the field for week commencing (WComm) is updated to show the correct week commencing date. e.g. If my user entered 14/08/2002 in Wokday, WComm should be updated to show 12/08/2002.

Thanks in advance

Nigel Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[danvlas]

Paste this into a module and use it as a function:

Function DtCommWeek(mydate As Date)
DtCommWeek = DateAdd("d", -1 * WeekDay(mydate, vbMonday) + 1, mydate)
End Function

or, in your query, add a calculated field:

YourFieldName = DateAdd("d", -1 * WeekDay(YourDateField, vbMonday) + 1, YourDateField)

HTH,

Dan

 

[mrf1xa]

Dan, apologies for delay, been away for a few days.

your answer works perfectly so thanks a million.

Nigel Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....