monday of the third week from order date

  • Thread starter Thread starter Angi
  • Start date Start 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
 
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)
 
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!
 
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.
 
Graham,
Well, of course that's what I did! Duh!!! I knew that! See what lack
of sleep does to ya??

Thanks!
Ang
 
Back
Top