"\" Function

[Guest]

Can anybody help?
I have been upgrading an old payroll system in Access now that we have
Office 2007. The original system that I inherited calculated the Notes and
coin requirements with manual input for each employee, a long and tedious
job. I have attempted to "automate" this task by using the "\" function in
the Build window, and have the following results:
Using as an example a Nett Pay of $178.73,
$50: [Nett Weekly Pay]\50, returns 3 -$150.00
$20: ([Nett Weekly Pay]-([$50]*50))\20, returns 1- $20.00
$10: (([Nett Weekly Pay]-([$50]*50)-([$20]*20))\10), returns 0
$5: (([Nett Weekly Pay]-([$50]*50)-([$20]*20)-([$10]*10))\5), returns 1 -
$5.00
Balance at this stage is $3.73
$2: (([Nett Weekly Pay]-([$50]*50)-([$20]*20)-([$10]*10)-([$5]*5))\2),
returns 2, when it should be 1.

Any ideas? I would appreciate an answer.

 

[Tom Lake]

Can anybody help?
I have been upgrading an old payroll system in Access now that we have
Office 2007. The original system that I inherited calculated the Notes and
coin requirements with manual input for each employee, a long and tedious
job. I have attempted to "automate" this task by using the "\" function in
the Build window, and have the following results:
Using as an example a Nett Pay of $178.73,
$50: [Nett Weekly Pay]\50, returns 3 -$150.00
$20: ([Nett Weekly Pay]-([$50]*50))\20, returns 1- $20.00
$10: (([Nett Weekly Pay]-([$50]*50)-([$20]*20))\10), returns 0
$5: (([Nett Weekly Pay]-([$50]*50)-([$20]*20)-([$10]*10))\5), returns 1 -
$5.00
Balance at this stage is $3.73
$2: (([Nett Weekly Pay]-([$50]*50)-([$20]*20)-([$10]*10)-([$5]*5))\2),

(178.73 - 150 - 20 - 5)

returns 2, when it should be 1.

Any ideas? I would appreciate an answer.

Here's what the help file says (emphasis mine)
Before division is performed, the numeric expressions are rounded to Byte, Integer, or Long expressions.

Since 3.73 is ROUNDED, it goes up to 4 before the integer division. 4 \ 2 = 2



Tom Lake

 

[Guest]

Tom, I understand what you are saying, but, that being the case why did the
"$10" calc not display "1" (rounding $8.73 up to "$10" first)?

Can anybody help?
I have been upgrading an old payroll system in Access now that we have
Office 2007. The original system that I inherited calculated the Notes and
coin requirements with manual input for each employee, a long and tedious
job. I have attempted to "automate" this task by using the "\" function in
the Build window, and have the following results:
Using as an example a Nett Pay of $178.73,
$50: [Nett Weekly Pay]\50, returns 3 -$150.00
$20: ([Nett Weekly Pay]-([$50]*50))\20, returns 1- $20.00
$10: (([Nett Weekly Pay]-([$50]*50)-([$20]*20))\10), returns 0
$5: (([Nett Weekly Pay]-([$50]*50)-([$20]*20)-([$10]*10))\5), returns 1 -
$5.00
Balance at this stage is $3.73
$2: (([Nett Weekly Pay]-([$50]*50)-([$20]*20)-([$10]*10)-([$5]*5))\2),

(178.73 - 150 - 20 - 5)

returns 2, when it should be 1.

Any ideas? I would appreciate an answer.

Here's what the help file says (emphasis mine)
Before division is performed, the numeric expressions are rounded to Byte, Integer, or Long expressions.

Since 3.73 is ROUNDED, it goes up to 4 before the integer division. 4 \ 2 = 2



Tom Lake

 

[Tom Lake]

Tom, I understand what you are saying, but, that being the case why did
the
"$10" calc not display "1" (rounding $8.73 up to "$10" first)?

8.73 rounds to 9. Here are the steps:

$50: [Nett Weekly Pay]\50 178.73 rounds to 179 ==> 179 \ 50 = 3

$20: ([Nett Weekly Pay]-([$50]*50))\20

(178.73 - 3 * 50) = 28.73 which rounds to 29 ==> 29 \ 20 = 1

$10: (([Nett Weekly Pay]-([$50]*50)-([$20]*20))\10)

(178.73 - 3 * 50 - 1 * 20) = 8.73 which rounds to 9 ==> 9 \ 10 = 0

$5: (([Nett Weekly Pay]-([$50]*50)-([$20]*20)-([$10]*10))\5)

(178.83 - 3 * 50 - 1 * 20 - 0 * 10) = 8.73 which rounds to 9 ==> 9 \ 5 = 1

$2: (([Nett Weekly Pay]-([$50]*50)-([$20]*20)-([$10]*10)-([$5]*5))\2),

(178.83 - 3 * 50 - 1 * 20 - 0 * 10 - 1 * 5) = 3.73 which rounds to 4 ==> 4 \
2 = 2

Tom Lake

 

[Guest]

Thanks, Tom, I also figured that after I'd posted. But fact still remains,
how do I round down for $2, $1, 50c 20c 10c 5c 2c 1c? I have tried the
"Round" Syntax too, but I continue to get an error. Any ideas?

Tom, I understand what you are saying, but, that being the case why did
the
"$10" calc not display "1" (rounding $8.73 up to "$10" first)?

8.73 rounds to 9. Here are the steps:

$50: [Nett Weekly Pay]\50 178.73 rounds to 179 ==> 179 \ 50 = 3

$20: ([Nett Weekly Pay]-([$50]*50))\20

(178.73 - 3 * 50) = 28.73 which rounds to 29 ==> 29 \ 20 = 1

$10: (([Nett Weekly Pay]-([$50]*50)-([$20]*20))\10)

(178.73 - 3 * 50 - 1 * 20) = 8.73 which rounds to 9 ==> 9 \ 10 = 0

$5: (([Nett Weekly Pay]-([$50]*50)-([$20]*20)-([$10]*10))\5)

(178.83 - 3 * 50 - 1 * 20 - 0 * 10) = 8.73 which rounds to 9 ==> 9 \ 5 = 1

$2: (([Nett Weekly Pay]-([$50]*50)-([$20]*20)-([$10]*10)-([$5]*5))\2),

(178.83 - 3 * 50 - 1 * 20 - 0 * 10 - 1 * 5) = 3.73 which rounds to 4 ==> 4 \
2 = 2

Tom Lake

 

[Tom Lake]

Thanks, Tom, I also figured that after I'd posted. But fact still remains,
how do I round down for $2, $1, 50c 20c 10c 5c 2c 1c? I have tried the
"Round" Syntax too, but I continue to get an error. Any ideas?

"Tom Lake" wrote:

Int(expression) will do that for positive numbers.

Tom Lake