Access - Validation Start/End Times

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please can somone let me know what the validation rule should be in the End
Time field to ensure that this number is after the Start Time.
 
Pendelfin said:
Please can somone let me know what the validation rule should be in
the End Time field to ensure that this number is after the Start Time.
Click to expand...

The validation rule for both controls would be...

DateValue(Nz([Start Date],"1000-01-01"))<DateValue(Nz([End Date],"3000-01-01"))
Or Is Null

The Nz()s allow for the fileds top be null (required since you must fill out one
first). The DateValue functions would not be necessary if the controls are
bound to DateTime fields, but might be if the controls are unbound.
 
Sorry to be a little slow here, but how would I amnd this for Times. I am
new to access so am trying to teach myself.

Many Thanks

Rick Brandt said:
Pendelfin said:
Please can somone let me know what the validation rule should be in
the End Time field to ensure that this number is after the Start Time.
Click to expand...

The validation rule for both controls would be...

DateValue(Nz([Start Date],"1000-01-01"))<DateValue(Nz([End Date],"3000-01-01"))
Or Is Null

The Nz()s allow for the fileds top be null (required since you must fill out one
first). The DateValue functions would not be necessary if the controls are
bound to DateTime fields, but might be if the controls are unbound.
Click to expand...
 
Pendelfin said:
Sorry to be a little slow here, but how would I amnd this for Times.
I am new to access so am trying to teach myself.
Click to expand...

In Access when you enter "only times" you are actually entering a DateTime where
the date is assumed to be 12/30/1899. Since the dummy date I used for the Nz()
expression for the Start Date is prior to 12/30/1899 that same expression should
work for times as well.
 
Rick said:
In Access when you enter "only times" you are actually entering a
DateTime where the date is assumed to be 12/30/1899. Since the dummy
date I used for the Nz() expression for the Start Date is prior to
12/30/1899 that same expression should work for times as well.
Click to expand...

I was mistaken above. Since I used DateValue() any time component is stripped
off. For times one would either change both DateValue() functions to
TimeValue() or one could use CDate() instead which would work for any
combination of dates-only, times-only or when both date and time are used
together.
 
Back
Top