Repost re Restricting entries based on Date and Time

[JohnB]

Hi. I posted this early on 7th March but have had no
replies. Can anyone help with this one? If it's a daft
question, let me know.

Im building a form which will allow users to create
records containing StartDate, StartTime, EndDate and
EndTime details as well as other information. I want
users to be restricted to creating records with
StartDates and StartTimes that are within the current
week, defined as being from 12.00 last Wednesday, to
12.00 next Wednesday.

If the user enters the StartDate first, then the
StartTime, is there some code I could use in the After
Update event of the StartTime field to check what the
user has entered in these two fields and to bar them and
bring up a suitable message if they are outside the
current week?

Are there any examples of this sort of thing anywhere?

Thanks in advance. JohnB

 

[Dirk Goldgar]

Hi. I posted this early on 7th March but have had no
replies. Can anyone help with this one? If it's a daft
question, let me know.

Im building a form which will allow users to create
records containing StartDate, StartTime, EndDate and
EndTime details as well as other information. I want
users to be restricted to creating records with
StartDates and StartTimes that are within the current
week, defined as being from 12.00 last Wednesday, to
12.00 next Wednesday.

If the user enters the StartDate first, then the
StartTime, is there some code I could use in the After
Update event of the StartTime field to check what the
user has entered in these two fields and to bar them and
bring up a suitable message if they are outside the
current week?

Are there any examples of this sort of thing anywhere?

Thanks in advance. JohnB

Any special reason you're storing StartDate and StartTime (and EndDate
and EndTime) as separate fields rather than as single date/time values;
e.g. WhenStarted and WhenEnded? It seems to me that would simplify
editing and querying, and all date/time values actually include both
date and time components.

It's not clear to me what your definition of "current week" really
means. Do you mean from midnight between to Tuesday and Wednesday to
the next midnight between Tuesday and Wednesday? Do you mean to include
the terminal boundary minute -- an extra minute, effectively? Or do you
really mean from 12:00:00 AM Wednesday to 11:59:00 PM Tuesday?

If it's the latter you want, then you might use something like this:

'----- start of code -----
Private Sub ValidateStart() As Boolean

' Return True if the combination of StartDate and StartTime
' is within the current week; False if not.

Dim dtWhenStarted As Date
Dim fValid As Boolean

dtWhenStarted = Nz(Me!StartDate, 0) + Nz(Me!StartTime, 0)

fValid = _
(DatePart("ww", dtWhenStarted, vbWednesday) = _
DatePart("ww", Date, vbWednesday))

ValidateStart = fValid

If Not fValid Then
MsgBox = _
"The date and time you entered are not within " & _
"the current week. Please re-enter.",
vbExclamation,
"Invalid Start Date/Time"
End If

End Sub

Private Sub StartDate_BeforeUpdate(Cancel As Integer)

Cancel = Not ValidateStart()

End Sub

Private Sub StartTime_BeforeUpdate(Cancel As Integer)

Cancel = Not ValidateStart()

End Sub

'----- end of code -----

 

[John Vinson]

Hi. I posted this early on 7th March but have had no
replies. Can anyone help with this one? If it's a daft
question, let me know.

Im building a form which will allow users to create
records containing StartDate, StartTime, EndDate and
EndTime details as well as other information.

Access Date/Time values are stored as a double float number - a count
of days and fractions of a day (times) since midnight, December 30,
1899. As such it's usually best to store StartDate and StartTime in
one field; if you store #3/8/2005 1:38pm# in a single field, then you
don't need to go through contortions combining #3/8/2005 12:00:00am#
with #12/30/1899 1:38pm# for comparison purposes.

I want
users to be restricted to creating records with
StartDates and StartTimes that are within the current
week, defined as being from 12.00 last Wednesday, to
12.00 next Wednesday.

If the user enters the StartDate first, then the
StartTime, is there some code I could use in the After
Update event of the StartTime field to check what the
user has entered in these two fields and to bar them and
bring up a suitable message if they are outside the
current week?

If you use a single field, you can put in the textbox's BeforeUpdate
event:

Private Sub txtStartDateTime_BeforeUpdate(Cancel as Integer)
If txtStartDateTime < DateAdd("d", 1 - Weekday(Date(), vbWednesday), _
Date()) Then
MsgBox "Date must be later that midnight last Wednesday", vbOKOnly
Cancel = True
End If
If txtStartTime > DateAdd("d", 8 - Weekday(Date(), vbWednesday), _
Date()) Then
MsgBox "Date must be before midnight next Wednesday", vbOKOnly
Cancel = True
End If
End Sub

If you insist on having two fields, you must add txtStartDate to
txtStartTime before doing these tests on the sum.

John W. Vinson[MVP]

 

[John Nurick]

Hi John,

It's not a daft question, but it's not easy to answer because you're
pretty vague about your situation. For instance, you mention

StartDate, StartTime, EndDate and
EndTime details

but are silent on the actual data structure. This matters, because
standard practice for this sort of task is to use Date/Time fields,
which store points in time - i.e. date and time in one. You seem to have
chosen to do it differently but haven't told us the details.

Also, "12.00 last Wednesday to 12:00 next Wednesday" may be clear to
you, but it's pretty ambiguous to me. Are you calculating days
Navy-style from noon to noon? If it's 9 am on Wednesday, is next
Wednesday today or the middle of next week?

Using date/time fields, the general idea would be to do this, in the
BeforeUpdate event procedure of the control:

1) If necessary, convert whatever the user has entered into a date/time
value (let's call it dtEntered).

2) Calculate the date-time value corresponding to 12.00 next Wednesday
(start by using Date() to get today's date, and Weekday() to find out
what day of the week it is).

3) If dtEntered is greater than 12.00 next Wednesday, put up an error
message and set BeforeUpdate's Cancel argument to True.

4) Repeat (2) and (3) for last Wednesday.

 

[JohnB]

Thank you all to for these very comprehensive replies. Im
sorry that my lack of knowledge about DateTime related
matters led me to post such a vague request. I appreciate
the advice on using combined fields and the efforts you
went to to give me code even though you wouldnt do it the
way I suggested.

Im going to spend some time learning more about DateTime
so as to best decide which of your suggestions to use.

Thank you all again for taking so much time on this.
Cheers, JohnB