Time

  • Thread starter Thread starter netadmin
  • Start date Start date
N

netadmin

I have a date and time field on a form that automatically
fills in the correct time and date, what my users would
like is if a form is filled out past a certain time let
say 5:00pm the form would then move the date and time
(8:30am the next day)automatically. Also if this done on
a weekend day the date and time would set to Monday at
8:30am. Any help would be appreciated.

Thanks
 
I have a date and time field on a form that automatically
fills in the correct time and date, what my users would
like is if a form is filled out past a certain time let
say 5:00pm the form would then move the date and time
(8:30am the next day)automatically. Also if this done on
a weekend day the date and time would set to Monday at
8:30am. Any help would be appreciated.

Thanks
Click to expand...

A bit of code in the Form's BeforeUpdate event could handle this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any form validation code that might cancel the record>
Dim dtX As Date
Dim iDay As Integer
dtX = Me!txtTimeField
If TimeValue(dtX) > #17:00# Then
iDay = DatePart("w", dtX, vbMonday) ' 1 = Monday, 5 = Friday
If iDay >= 5 Then
Me!txtTimeField = DateAdd("d", 8 - iDay, dtX) + #08:30#
Else
Me!txtTimeField = DateAdd("d", 1, dtX) + #08:30#
End If
End If

Air code, test it carefully!
 
Cool little problem...

You could set the default value to

=IIf(Weekday(Now(),2)<6,IIf(Now()-Int(Now())>17/24,Int(Now())+IIf(Weekday(No
w(),2)=5,3,1)+8.5/24,Now()),Int(Now())+8-Weekday(Now(),2)+8.5/24)

I think this should work. (I haven't tested it)

It's based on the fact that date/times are really decimals, the integer
being the day number and the decimal part being the fraction of day. Hence
17/24 is the equivalent of 5pm. The weekday function returns 1 to 7, (where
1 is monday when the second parameter is 2).

HTH
Sam
 
Back
Top