A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"\" Function



 
 
Thread Tools Display Modes
  #1  
Old March 12th, 2007, 03:53 AM posted to microsoft.public.access.tablesdbdesign
akelogorian
external usenet poster
 
Posts: 13
Default "\" Function

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.

  #2  
Old March 12th, 2007, 04:38 AM posted to microsoft.public.access.tablesdbdesign
Tom Lake
external usenet poster
 
Posts: 193
Default "\" Function


"akelogorian" wrote in message ...
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

  #3  
Old March 12th, 2007, 09:02 AM posted to microsoft.public.access.tablesdbdesign
akelogorian
external usenet poster
 
Posts: 13
Default "\" Function

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)?

"Tom Lake" wrote:


"akelogorian" wrote in message ...
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

  #4  
Old March 12th, 2007, 10:04 AM posted to microsoft.public.access.tablesdbdesign
Tom Lake
external usenet poster
 
Posts: 193
Default "\" Function


"akelogorian" wrote in message
...
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


  #5  
Old March 12th, 2007, 06:58 PM posted to microsoft.public.access.tablesdbdesign
akelogorian
external usenet poster
 
Posts: 13
Default "\" Function

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:


"akelogorian" wrote in message
...
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



  #6  
Old March 12th, 2007, 09:55 PM posted to microsoft.public.access.tablesdbdesign
Tom Lake
external usenet poster
 
Posts: 193
Default "\" Function


"akelogorian" wrote in message
...
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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:19 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.