Date Issues

  • Thread starter Thread starter Access Beginner
  • Start date Start date
A

Access Beginner

I have a system that stores delivery records of some documents that leave my
company and they always go on a certain day to a specific office for example

REPORT A - OFFICE A
Will go on Tuesday

REPORT B – OFFICE B
Will go on Tuesdays and Fridays



I currently have a combo box for selecting which office the report will go
to and I would like to have my delivery date estimated:

For example if the record was added today for OFFICE A it would estimate
the delivery date as Tuesday the 16th

- Or -

If a record was added for OFFICE B on Tuesday the 16th it would estimate
Friday the 19th as the delivery date and so forth OFFICE B addition date of
the 19th estimated delivery of the 23rd

Could someone please get back to me as soon as possible on this as it is
frustrating me to no end
 
hi,

Access said:
Could someone please get back to me as soon as possible on this as it is
frustrating me to no end
Click to expand...
What is frustrating?
Where is your problem?
What have you already done to solve this problem?


mfG
--> stefan <--
 
Put the following in the AfterUpdate event procedure of the combo box:

Dim ctrl As Control
Dim dtmDate As Date

Set ctrl = Me.ActiveControl
' add one day to current date
dtmDate = DateAdd("d", 1, VBA.Date)

If Not IsNull(ctrl) Then
' depending on which office selected step forward
' one day at a time until next Tuesday or Friday reached
Select Case ctrl
Case "Office A"
Do Until Weekday(dtmDate) = vbTuesday
dtmDate = DateAdd("d", 1, dtmDate)
Loop
Case "Office B"
Do Until Weekday(dtmDate) = vbTuesday _
Or Weekday(dtmDate) = vbFriday
dtmDate = DateAdd("d", 1, dtmDate)
Loop
End Select

Me.[EstimatedDeliveryDate] = dtmDate
Else
' if no office selected make date Null
Me.[EstimatedDeliveryDate] = Null
End If

where EstimatedDeliveryDate is a control bound to the field in the
underlying table in which the estimated delivery date is stored. You can of
course alter the date manually if necessary after its been assigned to the
control.

Ken Sheridan
Stafford, England
 
Back
Top