monday of the third week from order date

A

Angi

The ShipWeek field is supposed to be the week of... I need to know how
to get the value of the Monday of that week. The field value is
currently the OrderDate plus 21 days using DateAdd.

ie: OrderDate is 4/20/05, ShipWeek should be 5/9/05 (Monday) not
5/11/05 (Wednesday). Is there an easy way to do this or do I need to
check what day the order date is and do case selects?

Thanks in advance!
Angi
 
G

Graham Mandeno

Hi Angi

The following function will give you the first day of the week containing a
given date:

Public Function StartOfWeek(GivenDate As Variant, _
Optional FirstDayOfWeek As VbDayOfWeek = vbUseSystemDayOfWeek _
) As Variant
If IsDate(GivenDate) Then
StartOfWeek = DateValue(GivenDate) - Weekday(GivenDate, FirstDayOfWeek) +
1
End If
End Function

If you specify the FirstDayOfWeek as vbMonday (or 2, if you are using it
from SQL) and pass OrderDate+21 as your GivenDate, then the result should be
what you require:
ShipDate: StartOfWeek(OrderDate+21, 2)
 
A

Angi

Graham,
Thank you for that!! That's gonna save a lot of time! Just one little
problem...I'm not doing something right.

I keep getting the error:
Compile Error:

Expected variable or procedure, not module

I have the following code in my Form_Current event:
Private Sub Form_Current()
Dim wkdate As Date

wkdate = StartofWeek([OrderDate] + 21, 2)
Me.ShipWeek = wkdate
End Sub

I've tried using the default value property of ShipWeek but it's not
working, not even the DateAdd code I was using. The only thing I can
think of there is the orderdate's default value is set to Date() so
it's not being updated until the form is open (???).

Can you please tell me what I'm doing wrong with this function??
Thanks!
 
G

Graham Mandeno

Hi Angi

You didn't, by any chance, put my function in a new module and save the
module as "StartOfWeek", did you?

You cannot have a module and a procedure with the same name. To fix it,
simply rename the module - say "mdlStartOfWeek".

Also, as you are calling it from VBA, use the built-in constant:
Me.ShipWeek = StartofWeek(Me.OrderDate + 21, vbMonday)

That way it's easier to read what's happening.
 
A

Angi

Graham,
Well, of course that's what I did! Duh!!! I knew that! See what lack
of sleep does to ya??

Thanks!
Ang
 
Top