Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Mod Operator

  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Mod Operator

    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.
    Inspiration Through Fermentation

  2. #2
    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.
    Inspiration Through Fermentation

  3. #3
    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
    currently using SS 2008R2

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote 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. #5
    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()
    Testimonial:
    pootle flump
    ur codings are working excelent.

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

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote 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!
    Inspiration Through Fermentation

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #9
    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?
    Inspiration Through Fermentation

  10. #10
    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).
    Me.Geek = True

  11. #11
    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. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    -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.
    George
    Home | Blog

  13. #13
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    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.

    Quote 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. #14
    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.
    Inspiration Through Fermentation

  15. #15
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote 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.
    Inspiration Through Fermentation

Posting Permissions

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