Data Validation

[Guest]

I have a text field that has data in it that I would like to Validate will
the client is entering the data. The first three bytes of data should be
'00:'; the next three bytes can be a range between '01:' to '59:'; the last
two bytes must be between '01' and 59. I do not see how I can do this on the
Validation line. What are my alternatives? I know I'm missing something
obvious. Ideas?

 

[Guest]

If you change the text field to a Date field (it sure seems like you are
tracking time), the following will work in the properties of the field in the
table:

Input Mask: 00:00:00;;_
Validation Rule: Between 0.000706018518518518 And 0.0416550925925926
Validation Text: You big dummy!

There is a problem though as your rules disallow the following time: 00:29:00

If the last two characters must be between 01 and 59, you can never have an
even minute. The rule above limits the time to 00:01:01 to 00:59:59 but
allows whole minutes in between them.

 

[Guest]

Jerry,

Yes, it is a date value in a text field. The problem was we were entering
data in a short time format without entering hours. Since we did not really
want hours, we just put an input mask of 00:00. This was fine as long as the
minutes that we were entering was less than 24 which Access thought was
hours. We were told by the SME this scenario would never happen. Well it
did! (Murphy was an optimist). So now, I am converting the entered data to
an Hour, Minute, Second format. I tried to put all the data entered back to
where it belongs (Minutes to the Seconds and Hours to the Minutes) but was
unable to get the job done. So, I converted the data in a query and
formatted it the way it needs to be and got the table looking fine. Then I
tried to get the editing piece to work. Sigh! This brings me back to trying
to edit the input as I want to happen. I do know that some of the times will
fall in 00 seconds but not very often. I'll see what I can do. Aren't you
sorry you asked?

Thanks for your help!