By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 913 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Validate Time in MS Access 2000

P: n/a
Hello there,

I have a data bound text field containing worked time: hour and minute
the user worked. The rule is the user can only enter the minute in
the increment of 25. The reason for that is manager only care about
at least 15 minutes worked.

So, say the user worked for 2 hours 15 minutes, in the text box, they
need to enter: 2.25. If they worked for 4 hours 45 minutes, they
enter 4.75. If 5 hours, they enter: 5 or 5.0. They can enter any
hour (any number before the period is considered hour).

Are there anyway I can set my validation to prevent the user to enter
the minutes not in 0, 25, 50, or 75? . So, anything like these are
invalid: 3.15, 3.33, 3.17, 3.89, etc. In that case, I want to display
a message box.

Thanks in advance,
James
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"James P." <ha*********@yahoo.com> wrote in message
news:f4**************************@posting.google.c om
Hello there,

I have a data bound text field containing worked time: hour and minute
the user worked. The rule is the user can only enter the minute in
the increment of 25. The reason for that is manager only care about
at least 15 minutes worked.

So, say the user worked for 2 hours 15 minutes, in the text box, they
need to enter: 2.25. If they worked for 4 hours 45 minutes, they
enter 4.75. If 5 hours, they enter: 5 or 5.0. They can enter any
hour (any number before the period is considered hour).

Are there anyway I can set my validation to prevent the user to enter
the minutes not in 0, 25, 50, or 75? . So, anything like these are
invalid: 3.15, 3.33, 3.17, 3.89, etc. In that case, I want to display
a message box.

Thanks in advance,
James


Perhaps have seperate text boxes for "hours" and "minutes" ? Then it is
easy to enter a validation rule for each

Minutes -> 0 or 15 or 30 or 45
Hours -> 1 or 2 or 3 or 4 or 5 or 6 or 7 or 8 or 9 or 10 or 11 or 12

???
--
regards,

Bradley
Nov 13 '05 #2

P: n/a
First, WorkTime needs to be a Single datatype.

(Me!WorkTime - Int(Me!WorkTime)) MOD .25 = 0

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"James P." <ha*********@yahoo.com> wrote in message
news:f4**************************@posting.google.c om...
Hello there,

I have a data bound text field containing worked time: hour and minute
the user worked. The rule is the user can only enter the minute in
the increment of 25. The reason for that is manager only care about
at least 15 minutes worked.

So, say the user worked for 2 hours 15 minutes, in the text box, they
need to enter: 2.25. If they worked for 4 hours 45 minutes, they
enter 4.75. If 5 hours, they enter: 5 or 5.0. They can enter any
hour (any number before the period is considered hour).

Are there anyway I can set my validation to prevent the user to enter
the minutes not in 0, 25, 50, or 75? . So, anything like these are
invalid: 3.15, 3.33, 3.17, 3.89, etc. In that case, I want to display
a message box.

Thanks in advance,
James

Nov 13 '05 #3

P: n/a
"PC Datasheet" <no****@nospam.spam> wrote in message news:<5x******************@newsread3.news.atl.eart hlink.net>...
First, WorkTime needs to be a Single datatype.

(Me!WorkTime - Int(Me!WorkTime)) MOD .25 = 0

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"James P." <ha*********@yahoo.com> wrote in message
news:f4**************************@posting.google.c om...
Hello there,

I have a data bound text field containing worked time: hour and minute
the user worked. The rule is the user can only enter the minute in
the increment of 25. The reason for that is manager only care about
at least 15 minutes worked.

So, say the user worked for 2 hours 15 minutes, in the text box, they
need to enter: 2.25. If they worked for 4 hours 45 minutes, they
enter 4.75. If 5 hours, they enter: 5 or 5.0. They can enter any
hour (any number before the period is considered hour).

Are there anyway I can set my validation to prevent the user to enter
the minutes not in 0, 25, 50, or 75? . So, anything like these are
invalid: 3.15, 3.33, 3.17, 3.89, etc. In that case, I want to display
a message box.

Thanks in advance,
James


Wow, thank you so much for both of the responses. You guys are so
brilliant! I love both of the solution and will try them. Two good
tricks. I'm really appreciated.

James
Nov 13 '05 #4

P: n/a
James,

My solution doesn't work as is! MOD only works with whole numbers. Change my
solution to:
100*(Me!WorkTime - Int(Me!WorkTime)) MOD 25 = 0

Steve
PC Datasheet
"James P." <ha*********@yahoo.com> wrote in message
news:f4**************************@posting.google.c om...
"PC Datasheet" <no****@nospam.spam> wrote in message

news:<5x******************@newsread3.news.atl.eart hlink.net>...
First, WorkTime needs to be a Single datatype.

(Me!WorkTime - Int(Me!WorkTime)) MOD .25 = 0

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"James P." <ha*********@yahoo.com> wrote in message
news:f4**************************@posting.google.c om...
Hello there,

I have a data bound text field containing worked time: hour and minute
the user worked. The rule is the user can only enter the minute in
the increment of 25. The reason for that is manager only care about
at least 15 minutes worked.

So, say the user worked for 2 hours 15 minutes, in the text box, they
need to enter: 2.25. If they worked for 4 hours 45 minutes, they
enter 4.75. If 5 hours, they enter: 5 or 5.0. They can enter any
hour (any number before the period is considered hour).

Are there anyway I can set my validation to prevent the user to enter
the minutes not in 0, 25, 50, or 75? . So, anything like these are
invalid: 3.15, 3.33, 3.17, 3.89, etc. In that case, I want to display
a message box.

Thanks in advance,
James


Wow, thank you so much for both of the responses. You guys are so
brilliant! I love both of the solution and will try them. Two good
tricks. I'm really appreciated.

James

Nov 13 '05 #5

P: n/a
ha*********@yahoo.com (James P.) wrote in message news:<f4**************************@posting.google. com>...
Hello there,

I have a data bound text field containing worked time: hour and minute
the user worked. The rule is the user can only enter the minute in
the increment of 25. The reason for that is manager only care about
at least 15 minutes worked.

So, say the user worked for 2 hours 15 minutes, in the text box, they
need to enter: 2.25. If they worked for 4 hours 45 minutes, they
enter 4.75. If 5 hours, they enter: 5 or 5.0. They can enter any
hour (any number before the period is considered hour).

Are there anyway I can set my validation to prevent the user to enter
the minutes not in 0, 25, 50, or 75? . So, anything like these are
invalid: 3.15, 3.33, 3.17, 3.89, etc. In that case, I want to display
a message box.

Thanks in advance,
James


If Nz(Me!WorkTime) * 4 - Int(Nz(Me!WorkTime) * 4) > 0.03 Then boolValid = False

works for hours limited to two decimal places.

James A. Fortune
Nov 13 '05 #6

P: n/a
ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...
ha*********@yahoo.com (James P.) wrote in message news:<f4**************************@posting.google. com>...
Hello there,

I have a data bound text field containing worked time: hour and minute
the user worked. The rule is the user can only enter the minute in
the increment of 25. The reason for that is manager only care about
at least 15 minutes worked.

So, say the user worked for 2 hours 15 minutes, in the text box, they
need to enter: 2.25. If they worked for 4 hours 45 minutes, they
enter 4.75. If 5 hours, they enter: 5 or 5.0. They can enter any
hour (any number before the period is considered hour).

Are there anyway I can set my validation to prevent the user to enter
the minutes not in 0, 25, 50, or 75? . So, anything like these are
invalid: 3.15, 3.33, 3.17, 3.89, etc. In that case, I want to display
a message box.

Thanks in advance,
James


If Nz(Me!WorkTime) * 4 - Int(Nz(Me!WorkTime) * 4) > 0.03 Then boolValid = False

works for hours limited to two decimal places.

James A. Fortune


Thanks a lot, guys. I'll give it a try.

James
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.