how to validate quarter-hour increments??

[tlyczko]

I am looking to validate time worked in quarter-hour increments, e.g.
.25, .5, .75, 1.0, etc. etc.
I know I can use a modulo statement for the evaluation but I could not
find an explanation of how to use this in a form field validation.
I am fairly certain it would be something like If result=Me.FormField
mod .25 is an even integer (evenly divisible by .25)
I imagine the code would be [Me.formfield]%.25???
I cannot find anything in Access help, either.
Thank you, Tom

[Bas Cost Budde]

tlyczko wrote:

For Mod to work you must have integers. Lift the values to the integer
plane by dividing by the smallest step size (or multiplying by its
inverse which reads a little easier).

However, when trying to type an expression I found I would take an angle
slightly to the side:

[field]*4 = Int(field*4)

will tell you whether "field" (this is not your real field name :-) ) is
an exact multiple of .25

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[jimfo...@compumarc.com]

Normally I'd suggest to validate the time worked multiplied by four;
but I think it's better to add a little bit of self-defensive margin
for floating point error.

I'd like both .24999 and .25001 to be valid, but not .26 or .24.

.24999 * 4 = 0.99996
.25001 * 4 = 1.00004

So Int(Nz(txtEmployeeHours.Value) * 4 + 0.5) should provide the nearest
integer to be used for the comparison.

Abs(Int(Nz(txtEmployeeHours.Value) * 4 + 0.5) -
Nz(txtEmployeeHours.Value) * 4) < 0.01

should be pretty safe.

Examples:
.24 Abs(Int(.96 + .5) - .96) = .04 invalid
.249 Abs(Int(.996 + .5) - .996) = .004 valid
.25 Abs(Int(1 + .5) - 1) = 0 valid
.251 Abs(Int(1.004 + 0.5) - 1.004) = .004 valid
.26 Abs(Int(1.04 + 0.5) - 1.04) = .04 invalid

If you just want to round to the nearest .25 hour automatically:

Int(Nz(txtEmployeeHours.Value)) * 4 + 0.5) / 4#

There may also be other solutions that use the Mod function instead.
You can adjust 0.01 to smaller values.

James A. Fortune

[Panzerlied]

Hi,

I'm thinking that you could just multiply your entry by 100 and then do
the Mod(entry, 25) and if this is 0, then it is a multiple of .25 - if
not, you have an error.

Of course, I could be wrong...

JimA

[tlyczko]

I tried the following in the field's BeforeUpdate and AfterUpdate
events:

If Abs(Int(Nz([lngTimeSpent].[Value]) * 4 + 0.5) -
Nz([lngTimeSpent].[Value]) * 4) < 0.01 Then
MsgBox "true"
Exit Sub
Else
MsgBox "false, retype"
End If

But it always comes back true and the field resets to zero, why??

My original desire was to put this test into the form field's
'Validation Rule' property field, which would mean that if it is false,
the validation text would come back, but this did not work out either.

Meanwhile, I will keep trying.

Thank you, Tom

[jimfo...@compumarc.com]

Because of the Nz function, a Null value will cause 0 - 0 = 0 < 0.01.
Your code is not getting a value for [lngTimeSpent].[Value].

You need to use the name of the control on the form that contains the
number.

James A. Fortune

[tlyczko]

Thank you, I double-checked.
lngTimeSpent IS the form control's name.
I will try again and see what nz(...etc.) produces in a message box.
Thank you, Tom

[Bas Cost Budde]

tlyczko wrote:

> I tried the following in the field's BeforeUpdate and AfterUpdate
> events:
>
> If Abs(Int(Nz([lngTimeSpent].[Value]) * 4 + 0.5) -
> Nz([lngTimeSpent].[Value]) * 4) < 0.01 Then
> MsgBox "true"
> Exit Sub
> Else
> MsgBox "false, retype"
> End If

No brackets around property names, is that the point of failure here?
(Value is the default propery anyway)

BeforeUpdate... is the control bound to a table field? There is
something in the back of my mind about unbound controls and data events

Does it hang on a regional setting? In my country, decimal separator is
the comma, and if I enter a value with dot as decimal, the integer
portion is considered which is by definition a whole number of quarters.

How about using a slightly different control that allows only correct
*inputs*? Something similar to a spin button. Or have two controls, one
for the integer portion (can be simple) and one, presumably a combobox,
for the quarters. You can even catch keystrokes in the first so you can
jump to the second when the user presses dot or comma, whichever.

[tlyczko]

Thank you, I fixed the problem with the brackets, none were necessary,
I mis-copied or mis-typed from Mr. Fortune's example, and I tried it
again, and it still does not work because it's always zero, I have not
figured out if it is the way the calculation is written, I tried it out
with MsgBox-ing the contents of the formula in the AfterUpdate event,
and it seems that the first part (Abs etc.) is always zero if the
number is < 1).

I'll keep plugging away with it, this would be useful in other
applications someday.

Thank you, Tom

[tlyczko]

I figured it out...I had the table field format of lngTimeSpent set to
long INTEGER!!
No wonder I couldn't do any decimal math!!
I reset the table field to SINGLE and now Mr. Fortune's check works
correctly in the BeforeUpdate event, I will now test in the Validation
Rule, etc.
Thank you, everyone, for your help!!
:) Tom

[Bas Cost Budde]

Does it help if you explicitly apply the second argument to Nz()? I am
not sure whether a control will arrive as text or as number, and it
doesn't hurt to put Nz(something,0).

I reformatted the formula for closer inspection.

Abs(
Int(
Nz(
[lngTimeSpent].[Value]
) * 4 + 0.5

) - Nz(
[lngTimeSpent].[Value]
) * 4
) < 0.01

So we take the Abs of the difference between an Int and a Nz*4, and see
if that is less than 0.01
The Int is taken from the Nz*4 plus 0.5 (so it is a 'fair round')

Should I input .75, the formula becomes

abs( int(.75*4 + .5) - .75*4 )
abs( 3 - 3 ) = 0

Should I input .6, the formula becomes

abs( int(.6*4 + .5) - .6*4 )
abs( 2 - 2.4 ) = .4

Looks good to me.

[Bas Cost Budde]

tlyczko wrote:

> I figured it out...I had the table field format of lngTimeSpent set to
> long INTEGER!!

Gur.

You could leave it that way and divide by 4 for display purposes. If you
input anything, multiply by 4 and round. (You must round yourself
otherwise the value will truncate). Integer math has the fine effect of
being exact.

But, having a Single field does fine, I think.

[lyle...@yahoo.ca]

Eventually, using fractions with DateTime variables may cause grief.
This is because VBA coerces DateTime variables to Doubles before doing
basic arithmetic. While DateTime variables can be differentiated to
seconds, Doubles can be differentiated to (hmmmm ... memory says
1/2^32-1 but memory may be way wrong). As a result, the showing of the
results of your arithmetic may be dependent on how VBA wants to (re)
coerce the Double outcomes back to DateTimes. TTBOMK VBA makes no
guarantees that it will do this correctly (from the sense of being in
keeping with what we expect) 100% of the time.
But VBA does provide complete procedures to deal with DateTime. These
are the DateTime functions such as DateAdd and DateDiff. With them we
use integers; in the case you describe we would use 15 and minutes.
In CDMA there are many experts who can tell us about arithmetic and
Dates. I think they are misled and misleading. DateTime functions exist
for a reason. Use them.

[tlyczko]

I'm not using Date/Time functions because I only care about duration,
which is easiest to express as decimal fractions.
I already have a field to enter the date, I only care if they spent a
quarter hour, half hour, whatever.
That's why I used a Single, because no hourly duration value will be
long enough to fill it up.
Thank you, Tom

[lyle...@yahoo.ca]

Uh Huh.