dividing two numbers

[Hasanain F. Esmail]

I have spent many hours to solve this problem but no result yet. Your
help will highly be appriciated.

Problem:
Let us say I have two field named as number1 and number2

There are 12 pcs of an item in a carton. when I enter 252 in number1
field and enter 12 in number2 field and run a query with calculated
field as follows:
[Number1}/[Number2] I get 21, which is what I should get.
But when I enter 251 in number1 field and enter 12 in number2 field
and run the same equation I get 20.92. This is not what my client
wants. He wants displayed 20.11. 20 before decimal represents 20
cartons and 11 after decimal represents 11 pcs left in a carton of 12
pc.

Thanking you all in advance

[Jeff Smith]

If you want to place the full equation in the one field use the following:
Cartons: Int([Number1]/[Number2]) & "." & [Number1] Mod [Number2]
The problem here is that the period is always displayed.

If you want to place the whole cartons in one field and the remainder in
another use the following:
Cartons: Int([Number1]/[Number2])
Remainder: [Number1] Mod [Number2]

Jeff
"Hasanain F. Esmail" <ha*******@yahoo.com> wrote in message
news:27**************************@posting.google.c om...

I have spent many hours to solve this problem but no result yet. Your
help will highly be appriciated.

Problem:
Let us say I have two field named as number1 and number2

There are 12 pcs of an item in a carton. when I enter 252 in number1
field and enter 12 in number2 field and run a query with calculated
field as follows:
[Number1}/[Number2] I get 21, which is what I should get.
But when I enter 251 in number1 field and enter 12 in number2 field
and run the same equation I get 20.92. This is not what my client
wants. He wants displayed 20.11. 20 before decimal represents 20
cartons and 11 after decimal represents 11 pcs left in a carton of 12
pc.

Thanking you all in advance

[Squirrel]

Hi Hasanain,

Here's your code:

Public Function CalcCartons(iPieces As Integer) As String
CalcCartons = iPieces \ 12 & "." & iPieces Mod 12
End Function

I tested it in the Immediate Window with these results:

?CalcCartons(252)
21.0
?CalcCartons(251)
20.11

Hope this helps.
Linda
"Hasanain F. Esmail" <ha*******@yahoo.com> wrote in message
news:27**************************@posting.google.c om...

I have spent many hours to solve this problem but no result yet. Your
help will highly be appriciated.

Problem:
Let us say I have two field named as number1 and number2

There are 12 pcs of an item in a carton. when I enter 252 in number1
field and enter 12 in number2 field and run a query with calculated
field as follows:
[Number1}/[Number2] I get 21, which is what I should get.
But when I enter 251 in number1 field and enter 12 in number2 field
and run the same equation I get 20.92. This is not what my client
wants. He wants displayed 20.11. 20 before decimal represents 20
cartons and 11 after decimal represents 11 pcs left in a carton of 12
pc.

Thanking you all in advance

[Mark]

Hi all,
Just trying to increase my knowledge here.... I'm guessing that Int
returns a whole number but what does Mod do?

Sorry if this is a basic question.

Mark

"Jeff Smith" <No***@Not.This.Address> wrote in message
news:cm**********@lust.ihug.co.nz...

If you want to place the full equation in the one field use the following:
Cartons: Int([Number1]/[Number2]) & "." & [Number1] Mod [Number2]
The problem here is that the period is always displayed.

If you want to place the whole cartons in one field and the remainder in
another use the following:
Cartons: Int([Number1]/[Number2])
Remainder: [Number1] Mod [Number2]

Jeff
"Hasanain F. Esmail" <ha*******@yahoo.com> wrote in message
news:27**************************@posting.google.c om...

I have spent many hours to solve this problem but no result yet. Your
help will highly be appriciated.

Problem:
Let us say I have two field named as number1 and number2

There are 12 pcs of an item in a carton. when I enter 252 in number1
field and enter 12 in number2 field and run a query with calculated
field as follows:
[Number1}/[Number2] I get 21, which is what I should get.
But when I enter 251 in number1 field and enter 12 in number2 field
and run the same equation I get 20.92. This is not what my client
wants. He wants displayed 20.11. 20 before decimal represents 20
cartons and 11 after decimal represents 11 pcs left in a carton of 12
pc.

Thanking you all in advance

[Jeff Smith]

Mod returns the remainder. Below is from the VBA help file

Mod Operator
Used to divide two numbers and return only the remainder.
Syntax
result = number1 Mod number2
The Mod operator syntax has these parts:
PartDescription
result Required; any numeric variable.
number1 Required; any numeric expression.
number2 Required; any numeric expression.

Remarks
The modulus, or remainder, operator divides number1 by number2 (rounding
floating-point numbers to integers) and returns only the remainder as
result. For example, in the following expression, A (result) equals 5.
A = 19 Mod 6.7

Mod Operator Example
This example uses the Mod operator to divide two numbers and return only the
remainder. If either number is a floating-point number, it is first rounded
to an integer.
Dim MyResult
MyResult = 10 Mod 5 ' Returns 0.
MyResult = 10 Mod 3 ' Returns 1.
MyResult = 12 Mod 4.3 ' Returns 0.
MyResult = 12.6 Mod 5 ' Returns 3.

Jeff
"Mark" <ma*********@ntlworld.com> wrote in message
news:JO*************@newsfe6-gui.ntli.net...

Hi all,
Just trying to increase my knowledge here.... I'm guessing that Int
returns a whole number but what does Mod do?

Sorry if this is a basic question.

Mark

[Hasanain F. Esmail]

Thanks Jeff, that worked fine for me

"Jeff Smith" <No***@Not.This.Address> wrote in message news:<cm**********@lust.ihug.co.nz>...

If you want to place the full equation in the one field use the following:
Cartons: Int([Number1]/[Number2]) & "." & [Number1] Mod [Number2]
The problem here is that the period is always displayed.

If you want to place the whole cartons in one field and the remainder in
another use the following:
Cartons: Int([Number1]/[Number2])
Remainder: [Number1] Mod [Number2]

Jeff
"Hasanain F. Esmail" <ha*******@yahoo.com> wrote in message news:27**************************@posting.google.c om...

I have spent many hours to solve this problem but no result yet. Your
help will highly be appriciated.

Problem:
Let us say I have two field named as number1 and number2

There are 12 pcs of an item in a carton. when I enter 252 in number1
field and enter 12 in number2 field and run a query with calculated
field as follows:
[Number1}/[Number2] I get 21, which is what I should get.
But when I enter 251 in number1 field and enter 12 in number2 field
and run the same equation I get 20.92. This is not what my client
wants. He wants displayed 20.11. 20 before decimal represents 20
cartons and 11 after decimal represents 11 pcs left in a carton of 12
pc.

Thanking you all in advance