Question mod calculation incorrect in Access, correct in Excel (1 Viewer)

wiklendt · Apr 23, 2009

Hi, i've got a form where users can convert centimeters into "Hands high" (a horse database).

so, for 172 cm, the result is 16 Hh and 3 inches (which is then concatenated into a 'result' texbox to "16.3" hands high).

1 hand = 4 inches, so there's no such thing as 16.4 hands, because that's just 17.0 Hands.

however: when i put these calculations into excel (just using cells intead of textboxes), 172 = 16.3 hands, but in access, 172 = 16.0 hands??

the calculation (each step i have in a different textbox on the form) goes like this:

in access:

Code:

txtCentimetres = user input value

txtInches =[txtCentimetres]*0.393700787

txtHands =Int([txtInches]/4)

txtInchesMod4 =[txtInches] Mod 4

txtHh =[txtHands] & "." & [txtInchesMod4]

in excel cells (see attached image):

Code:

cell B2 = user input centimeter value

cell B3 =B2*0.393700787

cell B4 =INT(B3/4)

cell B5 =INT(MOD(B3,4))

cell B6 =B4 & "." & B5

so:

166 cm = 16.1 Hh (both access and excel)
167 cm = 16.2 Hh (both access and excel)
168 cm = 16.2 Hh (both access and excel)
169 cm = 16.2 Hh (both access and excel)
170 cm = 16.3 Hh (both access and excel)
171 cm = 16.3 Hh (both access and excel)
172 cm = 16.0 Hh (access, incorrect) or (edit) 16.3 Hh (excel, correct)
173 cm = 17.0 Hh (both access and excel)
174 cm = 17.1 Hh (both access and excel)

am i doing something wrong?? Mod in access help, is, predictably, no help at all. the calculation in access fails on every 'cusp' (i.e., seems to calc the remainder (mod) fine, but not increment the whole integer....)

Banana · Apr 23, 2009

Here's my results:

Code:

?(171 * 0.393700787)/4
 16.83070864425 
?(171 * 0.393700787) mod 4
 3 
?(172 * 0.393700787)/4
 16.929133841 
?(172 * 0.393700787) mod 4
 0 
?(173 * 0.393700787)/4
 17.02755903775 
?(173 * 0.393700787) mod 4 
 0

In Excel:

Code:

171	67.32283458	16.83070864	3.322834577
172	67.71653536	16.92913384	3.716535364
173	68.11023615	17.02755904	0.110236151

[strike]So, both Access and Excel agrees for all three possible inputs.[/strike] No, I'm blind as a bat. They didn't agree on mod.

[strike]I suspect we're seeing a rounding error due to how you had your number formatted, perhaps?[/strike]

PS: A thought occurred to me. I am getting non-integers for my mod calculation in Excel while Access gives me integers. (I didn't do a explicit typecast in my case)... could be a clue?

wiklendt · Apr 23, 2009

Banana said:
PS: A thought occurred to me. I am getting non-integers for my mod calculation in Excel while Access gives me integers. (I didn't do a explicit typecast in my case)... could be a clue?

that's interesting, i'll check that out asap - got a few meetings to go to first (damn work gets in the way all the time!) LOL

wiklendt · Apr 23, 2009

ok, tried 'general number', 'standard', and 'fixed' as format for each textbox with a number in it (the form is unbound). none of these changed the value of the mod calculation for the 172cm input... hm, may have to dig around a little more.

google will be my friend!

wiklendt · Apr 23, 2009

ha! i think i got it! (i'll have to do a thorough test) but i made the inches textbox an integer i.e.,

Code:

txtInches =Int([txtCentimetres]*0.393700787)

and that seems to be returning correct values for the inches mod

thanks banana

wiklendt · Apr 23, 2009

from immediate window

Code:

?int((172 * 0.393700787)) mod 4
 3

Banana · Apr 23, 2009

Glad we got that sorted out!

gemma-the-husky · Apr 23, 2009

the problem with mod is that it returns an integer result

so you cant use mod 1 to get the fractional part of a number

ie 12.67 mod 1 is 0, not 0.67

---------
the best way to get the remainder is to use INT or FIX function

(you cant use clng and things like that, as they round the number - you need something to truncate a number)

so

REMAINDER = mynum - int(mynum)

wiklendt · Apr 24, 2009

gemma-the-husky said:
the problem with mod is that it returns an integer result

so you cant use mod 1 to get the fractional part of a number

ie 12.67 mod 1 is 0, not 0.67

---------
the best way to get the remainder is to use INT or FIX function

(you cant use clng and things like that, as they round the number - you need something to truncate a number)

so

REMAINDER = mynum - int(mynum)

but excel calculated the mod as 3, and access as 0 - that can't just be a decimal issue. in any case, i don't want it to return 0.67 - i DO want integers.

but thanks for your comments - i'm sure it will help me and others in the future

gemma-the-husky · Apr 24, 2009

I know you only want an integer answer - i was demonstrating the rounding

the trouble is in access if you say (as banana did)

?(172 * 0.393700787) mod 4

the bracket is resolved to 67.7, which is rounded UP to 68
hence 68 mod 4 is 0

so to do the calc in access you need this expression

Int(4 * ((inches / 4) - Int(inches / 4)))

effectively

parthands = (inches / 4) - Int(inches / 4) - eg. 97 of a hand
parthands = int(4 * parthands) - rounds down to 3 inches

DJkarl · Apr 24, 2009

This function will give the same results as the MOD function in Excel.

Function ExcelMOD(number As Double, divisor As Double) As Double
ExcelMOD = number - divisor * Int(number / divisor)
End Function

wiklendt · Apr 25, 2009

gemma-the-husky said:
I know you only want an integer answer - i was demonstrating the rounding

the trouble is in access if you say (as banana did)

?(172 * 0.393700787) mod 4

the bracket is resolved to 67.7, which is rounded UP to 68
hence 68 mod 4 is 0

so to do the calc in access you need this expression

Int(4 * ((inches / 4) - Int(inches / 4)))

effectively

parthands = (inches / 4) - Int(inches / 4) - eg. 97 of a hand
parthands = int(4 * parthands) - rounds down to 3 inches

ah, ok. i get ya. thanks for clarifying

wiklendt · Apr 25, 2009

You must spread some Reputation around before giving it to gemma-the-husky again.

Banana · Apr 25, 2009

Here's an idea.

Find a random noob and give him some rep for asking good question then you can give it to Gemma.

Question mod calculation incorrect in Access, correct in Excel (1 Viewer)

wiklendt

i recommend chocolate

Attachments

Banana

split with a cherry atop.

wiklendt

i recommend chocolate

wiklendt

i recommend chocolate

wiklendt

i recommend chocolate

wiklendt

i recommend chocolate

Banana

split with a cherry atop.

gemma-the-husky

Super Moderator

wiklendt

i recommend chocolate

gemma-the-husky

Super Moderator

DJkarl

Registered User.

wiklendt

i recommend chocolate

wiklendt

i recommend chocolate

Banana

split with a cherry atop.

Similar threads

Users who are viewing this thread