Date calculation question

On a form I have a field named "txtAck_Date".  Also on the form I have a field named "txtTotalLTdays" which is a number field.  Then there is a 3rd field on the form named "txtProdDate".

I need the field "txtProdDate" to be a calculated field.  When the user enters a date in "txtAck_Date" then "txtProdDate" has to be "txtAck_Date" MINUS the days in "txtTotalLTdays" BUT NOT INCLUDING WEEKENDS (Saturdays or Sundays).

For example, using February of this year, if the user enters 2/21/13 in "txtAck_Date" I need to have "txtProdDate" back up by 20 days if "txtTotalLtDays" is 20.  Therefore "txtProdDate" would be automatically calculated to be 1/24/13.

I can't figure out what the afterupdate event code would be for "txtAck_Date".
SteveL13Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
Okay -- I think you'll have to use a custom function.

Try placing this in a separate module:

Function DateDiffNoWeekends(dt As Date, intNumDays As Integer) As Date
    Dim I As Integer
    Dim dtNew As Date
    I = 0
    dtNew = dt
    Do Until I = intNumDays
        dtNew = dtNew - 1
        Do Until Weekday(dtNew) <> 7 And Weekday(dtNew) <> 1
            dtNew = dtNew - 1
        Loop
        I = I + 1
    Loop
    DateDiffNoWeekends = dtNew
    
End Function

Open in new window


And call it from your after update event like this:

 Me.txtProdDate = DateDiffNoWeekends( Me.txtShipDate,   Me.txtTotalLTdays)

Open in new window



(Im assuming that you will be passing a positive number in txtTotalLTdays)
0
 
mbizupCommented:
try this:

txtProdDate =  DateAdd( "w", -20 , txtAck_Date)
0
 
SteveL13Author Commented:
I'm sorry.  One thing all should know is that "txtTotalLtDays" is also a calculated field.  I used 20 as an example but it could be any number.  

Using:

txtProdDate =  DateAdd( "w", -20 , txtAck_Date)

didn't work though.  When I enter 2/21/13 in the "txtShipDate" field 2/1/13 populated "txtProdDate" and it should be 1/24/13 becuase it has to NOT include weekend days.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
SteveL13Author Commented:
I tried:

 Me.txtProdDate = DateAdd("w", -Val(Me.txtTotalLTdays), Me.txtAck_Date)

And I still get 2/1/13 when I enter 2/21/13 instead of getting 1/24/13.

??
0
 
SteveL13Author Commented:
I meant to type:

I tried:

 Me.txtProdDate = DateAdd("w", -Val(Me.txtTotalLTdays), Me.txtShipDate)

And I still get 2/1/13 when I enter 2/21/13 instead of getting 1/24/13.

??
0
 
SteveL13Author Commented:
To mbizup:

A perfect work of art!  Thank you.
0
 
mbizupCommented:
Thanks, Steve!  Glad that helped out :-)

The next reasonable 'step' in these questions is very often "how do I exclude holidays?".

That block of code is a starting point... and if you needed to exclude holidays, you'd have to create a holiday table and use DLookups from that table or some other method within the loop to skip over dates that are included in your holiday table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.