1. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941

I have 2 fields on a form that show Case weight and Each weight for a product. I run a validation every time the user changes one of the two fields. A case can only contain full eaches, so the case weight must be evenly divisible by the each weight. So if the case weighs 25, and an each weighs 5, then 25/5=5 - the input is fine. However, if the case weight is 25.2, and the each weight is 4.2, then my vailidation doesn't work.

I've several different version of the following 2 methods, but can't get it to work:

If txtUom_Wt / txtEa_Wt <> CInt(txtUom_Wt / txtEa_Wt) Then

or

If txtUom_Wt Mod txtEa_Wt <>0 Then

Both return False. I thought it may have been something with my numbers - like maybe one of them was actually 4.200001, so I tried this in the immediate window using the actual numbers, and it still wouldn't work.

2. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
Just noticed a line in the help file for Mod that I'd missed before...

"If either number is a floating-point number, it is first rounded to an integer."

So I guess that rules that one out.

3. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
can you use currency for the weights of cases and eaches (nice word)?
it's good for four decimals and will get you out of the float nightmare.

izy

4. Registered User
Join Date
Oct 2002
Location
Posts
697
Originally Posted by RedNeckGeek
Just noticed a line in the help file for Mod that I'd missed before...

"If either number is a floating-point number, it is first rounded to an integer."

So I guess that rules that one out.
For what you're trying to do, use the alternate formula for mod:

Code:
`x mod y = x - floor(x / y) * y`
That will give you a value that you can compare to 0.

However it will often be close to zero but not exactly zero.

What you might do is, instead of storing Cases and Eaches, store Cases and "eaches per case." EPC is an integer.

When the person enters a value for eaches, the system simply asks "cases / each," rounds that to an integer, stores that in EPC and calculates Eaches every time by, again, dividing Case by EPC in a derived view.

Example: user enters 20 in case and enters 4.5 in eaches.

EPC = int(20 / 4.5) = 4

System displays 20 in case and 5 in eaches. That seems to be like it would be cleaner.

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
I imagine you know this but just in case I think you will need to use int() instead of floor()

6. Registered User
Join Date
Oct 2002
Location
Posts
697
Yeah, and oddly enough I used int elsewhere in my post.

7. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
Originally Posted by sco08y

However it will often be close to zero but not exactly zero.
That gave me an idea. Tested this in the immediate window
?25.2/4.2-cint(25.2/4.2)

gives me
-4.22838847269347E-16

Which leads me to think WTF? Stooppiid computers.

Anyway, I'm not in a position to change 2 of the major fields in my database,
so I had to find a workaround. This seems to work...

Code:
```  Dim intUomWt As Integer
Dim intEaWt As Integer
intUomWt = CInt(txtUom_Wt * 100)
intEaWt = CInt(txtEa_Wt * 100)
If cmbUom = "CS" Then
If intUomWt / intEaWt <> CInt(intUomWt / intEaWt) Then
msgbox "You screwed up!"
...```
Thanks for the input!

8. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Hi all

I may have the wrong end of the stick, but wouldn't this do the job

If Abs(txtUom_Wt / txtEa_Wt - Int(txtUom_Wt / txtEa_Wt)) > 0.01 Then
MsgBox " this is NOT OK"
Else
MsgBox " this is OK"
End If

> 001 is just the tolerance on the error you are prepared to accept (because you are using real numbers).

This is virtually the same as RedNeck's solution, but is easier to follow !!?

MTB

9. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
That is a cleaner solution than mine. I'll use that instead.
Thanks

I still would like to know why my calculator can do this more accurately than my PC?!?!?! I'm now beginning to doubt the accuracy of other calculations I do in my app. How long before one of the accounting trolls comes and pays me a visit with one of my reports in hand?

10. Computer Monkey
Join Date
May 2005
Posts
1,191
I know this may be a little bit more complex than is needed, but the brunt force method might be:
Code:
```Dim dblCase As Double, dblEach As Double
dblCase = txtUom_Wt
dblEach = txtEa_Wt
While dblCase > 0
dblCase = dblCase - dblEach
Wend
If dblCase = 0 Then
'it's divisible
Else
'it's not divisible
End If```
I'd just be worried if I had to do thousands of these iterations (might get a little time consuming).

11. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
If you use integer division your comparison is should be pretty easy:

If (txtUom_Wt / txtEa_Wt) <> (txtUom_Wt \ txtEa_Wt) Then

Integer Division (\) returns the only the integer part of a number, even if the fractional part is more than .5.

12. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
-4.22838847269347E-16

That's rediculously close to zero...

And am I missing a trick here....

If <Case> / <Each> <> 0 Then
MsgBox "This doesn't divide correctly"
Else
Msgbox "That'll do pig"
End if

And I totally agree with whoever it was above...
Don't store case weight, store quantity and each.
Then calculate on the fly mi hearty.
Last edited by gvee; 03-05-07 at 19:08.

13. Registered User
Join Date
Oct 2002
Location
Posts
697
Alright: now I realize why I wrote "floor." Because that's the correct mathematical expression, and VB (and thus Access) have a handful of functions that don't always work the way you'd expect.

CInt is not the same as Int which is not the same as Fix.

http://msdn2.microsoft.com/en-us/library/xh29swte(VS.80).aspx

The CInt, CString, etc are all typecasts. CInt, unfortunately, happens to round numbers using the grade school "round up on .5 or greater." I think it's the same as Excel's ROUND() function.

When you learn algebra, you learn floor and ceiling. Wikipedia has nice graphs and a good explanation. Fix is the same as floor. (And -Fix(-x) would be the same as ceiling...) They also show C's (int) typecast which is the same as VB's Int() function.

Originally Posted by RedNeckGeek
That gave me an idea. Tested this in the immediate window
?25.2/4.2-cint(25.2/4.2)

gives me
-4.22838847269347E-16

Which leads me to think WTF? Stooppiid computers.
Might I suggest:

What Every Computer Scientist Should Know About Floating Point Arithmetic

14. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
Thanks for the link scoO8Y. An explanation as to "why" is all I was really looking for. CInt was just the version I tried to post here. I'd also tried Int, Floor, Ceiling, and GarageDoor. Got the same result no matter what. It just bugged me that my calculator could perform this simple task and my computer couldn't.

15. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
Originally Posted by georgev
...
And I totally agree with whoever it was above...
Don't store case weight, store quantity and each.
Then calculate on the fly mi hearty.
But why store the one that I DON'T need - except for this one validation?
I use the other 2 frequently, and I should be able to calculate
the third if I ever need it for anything else. The same argument could be
used for any one of the 3, since they are just a calculation based on the other 2.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•