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

# how to validate quarter-hour increments??

 P: n/a 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 Nov 13 '05 #1
Share this Question
14 Replies

 P: n/a tlyczko wrote: 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. 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 Nov 13 '05 #2

 P: n/a tlyczko wrote: 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 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 Nov 13 '05 #3

 P: n/a 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 Nov 13 '05 #4

 P: n/a 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 Nov 13 '05 #5

 P: n/a 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 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 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 Nov 13 '05 #6

 P: n/a 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 Nov 13 '05 #7

 P: n/a 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. -- Bas Cost Budde, Holland http://www.heuveltop.nl/BasCB/msac_index.html Nov 13 '05 #8

 P: n/a 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 Nov 13 '05 #9

 P: n/a 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 Nov 13 '05 #10

 P: n/a 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, Holland http://www.heuveltop.nl/BasCB/msac_index.html Nov 13 '05 #11

 P: n/a 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. -- Bas Cost Budde, Holland http://www.heuveltop.nl/BasCB/msac_index.html Nov 13 '05 #12

 P: n/a 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. Nov 13 '05 #13

 P: n/a 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 Nov 13 '05 #14

 P: n/a Uh Huh. Nov 13 '05 #15

### This discussion thread is closed

Replies have been disabled for this discussion. 