Validation Rule: Nearest Quarter-Hour

[tbl]

My "hours fished" field is numeric, fixed, 2 decimal places.

Can I create a validation rule in a form (or table) for a
time field, where I want the data to be recorded as the
nearest 0.25? If so, how?

 

[Guest]

n = now
? n
9/18/2007 4:09:24 PM
? format(n,"mm")
09
? format(n,"mm") mod 15
9
? format(n,"mm") mod 15 = 0
False

 

[Douglas J. Steele]

Shouldn't that be "nn", not "mm", Steve?

?Now()
2007-09-18 16:53:20
?Format(Now(), "mm")
09
?Format(Now(), "nn")
53

As well, since Format returns a string and you're trying to do arithmetic,
it would probably be better to use DatePart("n", Now()) Mod 15

 

[tbl]

Shouldn't that be "nn", not "mm", Steve?

?Now()
2007-09-18 16:53:20
?Format(Now(), "mm")
09
?Format(Now(), "nn")
53

As well, since Format returns a string and you're trying to do arithmetic,
it would probably be better to use DatePart("n", Now()) Mod 15

Thanks for the replies.

Well I can see that I screwed this thread up with Subject
line that I used...

I'm actually using a numeric field, not time. It's just a
factor by which to multiply. It's recorded on paper field
forms like (hopefully) 3.75, and simply transcribed into the
db.

What I'm after here is something that not only won't allow
other fractions (silently rounding), but will protest if a
user tries to enter a number like 1.1, instead of 1, or
1.25.

Or did I miss the point of the replies, entirely? (I have
to admit that the examples given are not in my realm of
understing...)

 

[Douglas J. Steele]

Thanks for the replies.

Well I can see that I screwed this thread up with Subject
line that I used...

I'm actually using a numeric field, not time. It's just a
factor by which to multiply. It's recorded on paper field
forms like (hopefully) 3.75, and simply transcribed into the
db.

What I'm after here is something that not only won't allow
other fractions (silently rounding), but will protest if a
user tries to enter a number like 1.1, instead of 1, or
1.25.

Or did I miss the point of the replies, entirely? (I have
to admit that the examples given are not in my realm of
understing...)

What Steve & I were trying to show was how you could check whether a time
was a quarter hour. (You'd put that check in the control's BeforeUpdate
event, and set Cancel = True if it weren't)

If your field is named txtTime, you could try something like:

Private Sub txtTime_BeforeUpdate(Cancel As Integer)

If IsNumeric(Me.txtTime) Then
If 4*Me.txtTime <> Int(4*Me.txtTime) Then
MsgBox Me.txtTime & " is invalid."
Cancel = True
End If
Else
MsgBox Me.txtTime & " is invalid."
Cancel = True
End If

End Sub

 

[Jamie Collins]

My "hours fished" field is numeric, fixed, 2 decimal places.

Can I create a validation rule in a form (or table) for a
time field, where I want the data to be recorded as the
nearest 0.25? If so, how?

Do you want to *validate* values as being accurate to granularity of
0.25 e.g. (assuming 'Numeric' implies DECIMAL, as IMO it should)?
e.g.

your_column - FIX(your_column) IN (0.00, 0.25, 0.50, 0.75)

Or do you want to *round* values to the nearest 0.25, in which case
define 'nearest' e.g. see

How To Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

Jamie.

--

 

[tbl]

Do you want to *validate* values as being accurate to granularity of
0.25 e.g. (assuming 'Numeric' implies DECIMAL, as IMO it should)?
e.g.

your_column - FIX(your_column) IN (0.00, 0.25, 0.50, 0.75)

Yes. Field personel are instructed to record to the nearest
..25, but sometimes they get forgetful, and put something
like 0.1 when they actually meant 10 minutes. Data-entry
folks tend to enter whatever they see on the field-form
(paper), so I'd like the db to yell at them if they try to
enter something other than a number rounded to the nearest
..25. Hopefully, that will wake them up to get to the field
agent and ask what the correct data should be.

Is your text starting with FIX a proper validation rule?
I've tried the on-disk Help, but couldn't get it to cough up
anything close (it may be in there somewhere, but my
searches couldn't find it).

Thanks for your reply.

 

[Jamie Collins]

Yes.

Is your text starting with FIX a proper validation rule?
I've tried the on-disk Help, but couldn't get it to cough up
anything close (it may be in there somewhere, but my
searches couldn't find it).

What you you implying <g>? Why would my validation rule appear in
Access Help?!

FWIW mine was aircode it does appear to work e.g. (ANSI-92 Query Mode
SQL syntax -- you won't find CHECK in the Access Help either <g>):

CREATE TABLE YourTable
(
your_column DECIMAL(4,2) NOT NULL,
CONSTRAINT your_validation_rule
CHECK (your_column - FIX(your_column) IN (0.00, 0.25, 0.50, 0.75))
)
;
INSERT INTO YourTable (your_column) VALUES (31.25)
;
INSERT INTO YourTable (your_column) VALUES (25.31)
;

The first insert succeeds, the second fails, QED.

Jamie.

--

 

[tbl]

What Steve & I were trying to show was how you could check whether a time
was a quarter hour. (You'd put that check in the control's BeforeUpdate
event, and set Cancel = True if it weren't)

If your field is named txtTime, you could try something like:

Private Sub txtTime_BeforeUpdate(Cancel As Integer)

If IsNumeric(Me.txtTime) Then
If 4*Me.txtTime <> Int(4*Me.txtTime) Then
MsgBox Me.txtTime & " is invalid."
Cancel = True
End If
Else
MsgBox Me.txtTime & " is invalid."
Cancel = True
End If

End Sub

Many thanks Douglas. I don't understand all of that*, but
it sure works well.


* What does "Cancel As Integer" do? I've searched the help
for that many times, and never found an answer.

Also...

Why is the MsgBox in the "Else" part the same as the one in
the "If" part?

 

[Douglas J. Steele]

Many thanks Douglas. I don't understand all of that*, but
it sure works well.


* What does "Cancel As Integer" do? I've searched the help
for that many times, and never found an answer.

Search the help for BeforeUpdate Event.
http://msdn2.microsoft.com/en-us/library/aa211264(office.11).aspx

As it says there, "Setting the Cancel argument to True (-1) cancels the
BeforeUpdate event. "

Also...

Why is the MsgBox in the "Else" part the same as the one in
the "If" part?

If you look closely, you'll see that there's another If statement inside the
first one. I'm checking to see whether what's in the text box is numeric. If
it is numeric, I check to see whether it's a quarter hour. If it's not a
quarter hour, I raise an error. If it is a quarter hour, I'm happy, so I do
nothing. If it's not numeric, I raise an error as well.

 

[tbl]

Search the help for BeforeUpdate Event.
http://msdn2.microsoft.com/en-us/library/aa211264(office.11).aspx

As it says there, "Setting the Cancel argument to True (-1) cancels the
BeforeUpdate event. "

Interesting. When I search Help (Access 2002) for
BeforeUpdate Event, the Help screen I see doesn't have that
nifty little quote! But your link to MSDN2 did.

If you look closely, you'll see that there's another If statement inside the
first one. I'm checking to see whether what's in the text box is numeric. If
it is numeric, I check to see whether it's a quarter hour. If it's not a
quarter hour, I raise an error. If it is a quarter hour, I'm happy, so I do
nothing. If it's not numeric, I raise an error as well.

Thanks again! It's that "look closely" thing that my brain
seems to be afraid of... sigh.


I have another field with a similar need, except instead of
the nearest 0.25, I'd like to get a MsgBox when the user
enters any decimal fraction. Again, I don't want Access to
silently round the number--I want to get in the face of the
user, so to speak.

I thought I could simply remove the "4*" from each side of
the equation, and get the result I wanted, but that didn't
work, and I don't understand why. It's also an "Integer"
field.