How do I setup an Access Validation rule for both Date & Time?

[Guest]

I need to have the date and time entered on the form (05/05/05 13:00 PM). Any
suggestions on a validation rule that will force both the date and time to be
input?

Thanks for any suggestions you may have.

 

[Dirk Goldgar]

I need to have the date and time entered on the form (05/05/05 13:00
PM). Any suggestions on a validation rule that will force both the
date and time to be input?

Thanks for any suggestions you may have.

If the time could conceivably be midnight, then I don't think you can do
this with a validation rule as such, because validation rules are
value-based, and there is no difference in value between "05/05/05 12:00
AM", "05/05/05 00:00", and "05/05/05". I imagine you could test the
..Text property of the control to make sure that both a date portion and
a time portion were entered, provided that you do it in an event in
which the control still has the focus.

If midnight is not an acceptable value for the time portion of the
date/time field, then you can use a validation rule. Maybe something
like this will do:

(Fix([TheField])<>0) And (Abs([TheField])-Abs(Fix([TheField]))<>0)

If dates must be on or after 12/31/1899, and midnight is not a valid
time, then you can simplify the above rule to

([TheField] > 1) And ([TheField] - Fix([TheField]) <> 0)

I haven't really tested this all out thoroughly, but I think something
like that should work.

 

[Paul Overway]

Have you considered using an Input Mask?

 

[Guest]

Dirk,

Thanks for the feedback. The problem I have as you outlined is that 05/05/05
is viewed as the same value as 05/05/05 00:00 or 05/05/05 12:00 AM. We use
military time so the issue of 12:00 AM vs 00:00 is not really a problem. It
also appears using an Input Mask also allows the user to just enter 05/05/05
without actually forcing a time to be entered.

Thanks again,
blh3213

I need to have the date and time entered on the form (05/05/05 13:00
PM). Any suggestions on a validation rule that will force both the
date and time to be input?

Thanks for any suggestions you may have.

If the time could conceivably be midnight, then I don't think you can do
this with a validation rule as such, because validation rules are
value-based, and there is no difference in value between "05/05/05 12:00
AM", "05/05/05 00:00", and "05/05/05". I imagine you could test the
..Text property of the control to make sure that both a date portion and
a time portion were entered, provided that you do it in an event in
which the control still has the focus.

If midnight is not an acceptable value for the time portion of the
date/time field, then you can use a validation rule. Maybe something
like this will do:

(Fix([TheField])<>0) And (Abs([TheField])-Abs(Fix([TheField]))<>0)

If dates must be on or after 12/31/1899, and midnight is not a valid
time, then you can simplify the above rule to

([TheField] > 1) And ([TheField] - Fix([TheField]) <> 0)

I haven't really tested this all out thoroughly, but I think something
like that should work.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

Thanks for the feedback. The problem I have as you outlined is that
05/05/05 is viewed as the same value as 05/05/05 00:00 or 05/05/05
12:00 AM. We use military time so the issue of 12:00 AM vs 00:00 is
not really a problem. It also appears using an Input Mask also
allows the user to just enter 05/05/05 without actually forcing a
time to be entered.

Really? I just used this input mask on a date/time field and it seemed
to work, although I only made the most rudimentary test:

99/99/9999\ 99:99;0;_

On the other hand, I don't like the way it displays midnight.

For your purposes, it may be better to use two unbound text boxes, one
for date and one for time, and use code in the form's BeforeUpdate event
to validate them, then combine their values into a single date/time
value and assign that value to a field that is in the form's
recordsource but not displayed on the form. You'd use the form's
Current event to take that field from the current record, split it into
data and time portions, and assign each to the appropriate text box.