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".
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".
ASKER
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.
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.
ASKER
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.
??
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.
??
ASKER
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.
??
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.
??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To mbizup:
A perfect work of art! Thank you.
A perfect work of art! Thank you.
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.
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.
txtProdDate = DateAdd( "w", -20 , txtAck_Date)