473,884 Members | 2,313 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

how to validate quarter-hour increments??

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.FormF ield
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
14 10092
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.FormF ield
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
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.FormF ield
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(txtEmplo yeeHours.Value) * 4 + 0.5) should provide the nearest
integer to be used for the comparison.

Abs(Int(Nz(txtE mployeeHours.Va lue) * 4 + 0.5) -
Nz(txtEmployeeH ours.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(txtEmplo yeeHours.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
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
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

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
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
14528
by: Matt | last post by:
Hello, I have a query that I would like to schedule in DTS. The criteria of this query checks for records in the table that are within the current quarter. Here is what I have. WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005' I would like to dynamically generate the Quarter End and Quarter Beginning dates within my where clause based on the date that DTWS
1
4063
by: Terencetrent | last post by:
I have created a query that examines qarterly sales for 5 regions in the country. The query contains data for the past 6 quarters for each region and calculates the perecentage of total sales for the quarter fo each region. To help enhance the report I would love to include a comparison to of current sales to previous quarter sales and a comparison of current sales to sales a year ago. I am having a devil of a time creating a query to...
8
11891
by: Dominique Vandensteen | last post by:
I have a datetime and want to format it to "quarter year" so 20 december 2003 should give: "4-2003" is this possible? I don't find a format character for quarter :-( Dominique
3
2864
by: RD | last post by:
Say Company's financial year starts October First and say we are now March 17th. How do you determine which quarter of the Financial year - not the actual year - March 17th is in. Thanks for any help, Bob
2
7659
benchpolo
by: benchpolo | last post by:
First Day of the QUARTER select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) Question: How do I get the last DAY of the QUARTER? For example: 1st Quarter is 01/01/2008 to 03/31/2008. I am having difficulties finding the last day of the quarter.
4
11985
by: gimme_this_gimme_that | last post by:
Is there a way to get the last day of the previous business quarter from DB2? For 10/21/2008 the day would be 9/30/2008. Thanks.
2
1691
by: beaudreaux | last post by:
I have a report that is grouped by Quarter and have the following to give me a daily running average and to break/reset each quarter. However, the daily runnning average does not reset after the end of each quarter. Any help on why this would not be resetting at the start of each quarter for the daily running average? SO my end result would be a daily average through the end of the quarter and the last day of the quarter would be the overall...
3
8847
by: Sushant Panda | last post by:
I m trying to validating xml file against xsd file in vb6.0 but i m getting an error xsd file:(Aces_Dlr.xsd) <?xml version="1.0" encoding="UTF-8"?> <!--W3C Schema generated by XMLSpy v2010 (http://www.altova.com)--> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:n="urn:sample" targetNamespace="urn:sample">
0
9953
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11167
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10768
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10868
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10422
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7984
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7137
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
3
3242
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.