RoundUp in Access 97

[Rich]

OK, I have read the previous posts and put into Access. All works fine for
the most part, but a couple of entries give rise to strange results.

I have 2 fields:
[Net_Amount] being the amount of an item before tax ($#,##0.00)
[GST_Flag] being a yes/no field to indicate if I need to add 10% tax

I need to round the amounts up to the nearest cent.
Formula I am using is:

=IIf([NetAmount]*IIf([GST_Flag],1.1,1)*100=Int([NetAmount]*IIf([GST_Flag],1.1,1)*100),[NetAmount]*IIf([GST_Flag],1.1,1)*100,Int([NetAmount]*IIf([GST_Flag],1.1,1)*100+1))/100

Essentially, this gives me a roundup to the nearest cent including a 10% tax
if the flag is ticked. Tested it and it work fine. for example,

$123.45 plus tax comes out as $135.80

However, $100.00 does not work. This comes out as $100.01. Dont ask me why
and I have tried and tried, but Int(100*1.1) does not equal 100*1.1 in my
copy of Access 97. I am using SR-2.

Anyone any ideas? Can anyone else recreate this error?

Please help!!! Why couldn't Access have a RoundUp function built in?

 

[Ron Hinds]

I just tried to re-create this "error" and I get 110 as an answer for either
case. I pressed Ctrl-G to bring up the Debug window. Then I typed in the
following with the corresponding results:

?Int(100*1.1)
110
?100*1.1
110

I'm using Access97 SR2 also.

 

[Rich]

I get the same answer for both too...however, try a 3rd query to see if (in
Access) they equal each other...and I get false.

i.e. ?Int(100*1.1)=100*1.1

I just tried to re-create this "error" and I get 110 as an answer for either
case. I pressed Ctrl-G to bring up the Debug window. Then I typed in the
following with the corresponding results:

?Int(100*1.1)
110
?100*1.1
110

I'm using Access97 SR2 also.

OK, I have read the previous posts and put into Access. All works fine for
the most part, but a couple of entries give rise to strange results.

I have 2 fields:
[Net_Amount] being the amount of an item before tax ($#,##0.00)
[GST_Flag] being a yes/no field to indicate if I need to add 10% tax

I need to round the amounts up to the nearest cent.
Formula I am using is:

=IIf([NetAmount]*IIf([GST_Flag],1.1,1)*100=Int([NetAmount]*IIf([GST_Flag],1.
1,1)*100),[NetAmount]*IIf([GST_Flag],1.1,1)*100,Int([NetAmount]*IIf([GST_Fla
g],1.1,1)*100+1))/100

Essentially, this gives me a roundup to the nearest cent including a 10% tax
if the flag is ticked. Tested it and it work fine. for example,

$123.45 plus tax comes out as $135.80

However, $100.00 does not work. This comes out as $100.01. Dont ask me why
and I have tried and tried, but Int(100*1.1) does not equal 100*1.1 in my
copy of Access 97. I am using SR-2.

Anyone any ideas? Can anyone else recreate this error?

Please help!!! Why couldn't Access have a RoundUp function built in?

 

[Rich]

Whereas i.e. ?Int(1000*1.1)=1000*1.1 give TRUE.

I get the same answer for both too...however, try a 3rd query to see if (in
Access) they equal each other...and I get false.

i.e. ?Int(100*1.1)=100*1.1

I just tried to re-create this "error" and I get 110 as an answer for either
case. I pressed Ctrl-G to bring up the Debug window. Then I typed in the
following with the corresponding results:

?Int(100*1.1)
110
?100*1.1
110

I'm using Access97 SR2 also.

OK, I have read the previous posts and put into Access. All works fine for
the most part, but a couple of entries give rise to strange results.

I have 2 fields:
[Net_Amount] being the amount of an item before tax ($#,##0.00)
[GST_Flag] being a yes/no field to indicate if I need to add 10% tax

I need to round the amounts up to the nearest cent.
Formula I am using is:

=IIf([NetAmount]*IIf([GST_Flag],1.1,1)*100=Int([NetAmount]*IIf([GST_Flag],1.
1,1)*100),[NetAmount]*IIf([GST_Flag],1.1,1)*100,Int([NetAmount]*IIf([GST_Fla
g],1.1,1)*100+1))/100

Essentially, this gives me a roundup to the nearest cent including a 10% tax
if the flag is ticked. Tested it and it work fine. for example,

$123.45 plus tax comes out as $135.80

However, $100.00 does not work. This comes out as $100.01. Dont ask me why
and I have tried and tried, but Int(100*1.1) does not equal 100*1.1 in my
copy of Access 97. I am using SR-2.

Anyone any ideas? Can anyone else recreate this error?

Please help!!! Why couldn't Access have a RoundUp function built in?

 

[Ron Hinds]

Yes, I do to. I believe it's because the data types are different, though.
The second expression returns 110 as type Single and the first one is
casting it to a type of Integer. I agree that doesn't seem correct, though.

I get the same answer for both too...however, try a 3rd query to see if (in
Access) they equal each other...and I get false.

i.e. ?Int(100*1.1)=100*1.1

I just tried to re-create this "error" and I get 110 as an answer for either
case. I pressed Ctrl-G to bring up the Debug window. Then I typed in the
following with the corresponding results:

?Int(100*1.1)
110
?100*1.1
110

I'm using Access97 SR2 also.

OK, I have read the previous posts and put into Access. All works

fine
for

the most part, but a couple of entries give rise to strange results.

I have 2 fields:
[Net_Amount] being the amount of an item before tax ($#,##0.00)
[GST_Flag] being a yes/no field to indicate if I need to add 10% tax

I need to round the amounts up to the nearest cent.
Formula I am using is:

=IIf([NetAmount]*IIf([GST_Flag],1.1,1)*100=Int([NetAmount]*IIf([GST_Flag],1.
1,1)*100),[NetAmount]*IIf([GST_Flag],1.1,1)*100,Int([NetAmount]*IIf([GST_Fla
g],1.1,1)*100+1))/100

Essentially, this gives me a roundup to the nearest cent including a

10%
tax

if the flag is ticked. Tested it and it work fine. for example,

$123.45 plus tax comes out as $135.80

However, $100.00 does not work. This comes out as $100.01. Dont ask

me
why

and I have tried and tried, but Int(100*1.1) does not equal 100*1.1 in my
copy of Access 97. I am using SR-2.

Anyone any ideas? Can anyone else recreate this error?

Please help!!! Why couldn't Access have a RoundUp function built in?

 

[Rich]

Any solutions then on how to get around it? Basically, I need a roundup
function that is stable in Access 97. I do not believe any that I have read
about in the posts work under these conditions!

clues anyone?

Yes, I do to. I believe it's because the data types are different, though.
The second expression returns 110 as type Single and the first one is
casting it to a type of Integer. I agree that doesn't seem correct, though.

I get the same answer for both too...however, try a 3rd query to see if (in
Access) they equal each other...and I get false.

i.e. ?Int(100*1.1)=100*1.1

I just tried to re-create this "error" and I get 110 as an answer for either
case. I pressed Ctrl-G to bring up the Debug window. Then I typed in the
following with the corresponding results:

?Int(100*1.1)
110
?100*1.1
110

I'm using Access97 SR2 also.

OK, I have read the previous posts and put into Access. All works fine
for
the most part, but a couple of entries give rise to strange results.

I have 2 fields:
[Net_Amount] being the amount of an item before tax ($#,##0.00)
[GST_Flag] being a yes/no field to indicate if I need to add 10% tax

I need to round the amounts up to the nearest cent.
Formula I am using is:


=IIf([NetAmount]*IIf([GST_Flag],1.1,1)*100=Int([NetAmount]*IIf([GST_Flag],1.
1,1)*100),[NetAmount]*IIf([GST_Flag],1.1,1)*100,Int([NetAmount]*IIf([GST_Fla
g],1.1,1)*100+1))/100

Essentially, this gives me a roundup to the nearest cent including a 10%
tax
if the flag is ticked. Tested it and it work fine. for example,

$123.45 plus tax comes out as $135.80

However, $100.00 does not work. This comes out as $100.01. Dont ask me
why
and I have tried and tried, but Int(100*1.1) does not equal 100*1.1 in my
copy of Access 97. I am using SR-2.

Anyone any ideas? Can anyone else recreate this error?

Please help!!! Why couldn't Access have a RoundUp function built in?

 

[Douglas J. Steele]

You're experiencing floating point inaccuracies, something that's part of
computing.

Just as humans can't represent, say, 1/3 exactly, so too are there numbers
that computers can't represent.

Take a look at

?Int(100*1.1) - 100*1.1
-8.88178419700125E-15

See that very small number that represents the difference between the two
values?

Typically, you don't use = with floating point numbers. Instead, you decide
what's "close enough", and check that the difference between the two numbers
is within that range.

In other words, you'd use something like:

?Abs(Int(100*1.1) - 100*1.1) < 0.0000001
True


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Any solutions then on how to get around it? Basically, I need a roundup
function that is stable in Access 97. I do not believe any that I have
read
about in the posts work under these conditions!

clues anyone?

Yes, I do to. I believe it's because the data types are different,
though.
The second expression returns 110 as type Single and the first one is
casting it to a type of Integer. I agree that doesn't seem correct,
though.

I get the same answer for both too...however, try a 3rd query to see if (in
Access) they equal each other...and I get false.

i.e. ?Int(100*1.1)=100*1.1


:

I just tried to re-create this "error" and I get 110 as an answer for either
case. I pressed Ctrl-G to bring up the Debug window. Then I typed in
the
following with the corresponding results:

?Int(100*1.1)
110
?100*1.1
110

I'm using Access97 SR2 also.

OK, I have read the previous posts and put into Access. All works fine
for
the most part, but a couple of entries give rise to strange
results.

I have 2 fields:
[Net_Amount] being the amount of an item before tax ($#,##0.00)
[GST_Flag] being a yes/no field to indicate if I need to add
10% tax

I need to round the amounts up to the nearest cent.
Formula I am using is:


=IIf([NetAmount]*IIf([GST_Flag],1.1,1)*100=Int([NetAmount]*IIf([GST_Flag],1.1,1)*100),[NetAmount]*IIf([GST_Flag],1.1,1)*100,Int([NetAmount]*IIf([GST_Fla
g],1.1,1)*100+1))/100

Essentially, this gives me a roundup to the nearest cent including
a 10%
tax
if the flag is ticked. Tested it and it work fine. for example,

$123.45 plus tax comes out as $135.80

However, $100.00 does not work. This comes out as $100.01. Dont
ask me
why
and I have tried and tried, but Int(100*1.1) does not equal 100*1.1
in my
copy of Access 97. I am using SR-2.

Anyone any ideas? Can anyone else recreate this error?

Please help!!! Why couldn't Access have a RoundUp function built
in?

 

[Van T. Dinh]

(small quibble here - just to make it harder for you <smile>)

Quote:

"Just as humans can't represent, say, 1/3 exactly ..."

is a contradition: YOU (part of human race) stated the exactly value of 1/3
in writing (and I just did also ...)

Another way, mathematically, is to use repeating decimal representation 0.3
with a dot above the digit 3 to represent the repeating digit 3 ad
infinitum.

 

[Rich]

Excellent!
In the end, I had to use:

=IIf([GST_Flag]=False,[NetAmount],IIf(Abs([NetAmount]*110-Int([NetAmount]*110))<0.0001,[NetAmount]*110,Int([$]*110+1))/100)

But at least with all the values I tested, it works!!!!!!!

Thanks for your help.

You're experiencing floating point inaccuracies, something that's part of
computing.

Just as humans can't represent, say, 1/3 exactly, so too are there numbers
that computers can't represent.

Take a look at

?Int(100*1.1) - 100*1.1
-8.88178419700125E-15

See that very small number that represents the difference between the two
values?

Typically, you don't use = with floating point numbers. Instead, you decide
what's "close enough", and check that the difference between the two numbers
is within that range.

In other words, you'd use something like:

?Abs(Int(100*1.1) - 100*1.1) < 0.0000001
True


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Any solutions then on how to get around it? Basically, I need a roundup
function that is stable in Access 97. I do not believe any that I have
read
about in the posts work under these conditions!

clues anyone?

Yes, I do to. I believe it's because the data types are different,
though.
The second expression returns 110 as type Single and the first one is
casting it to a type of Integer. I agree that doesn't seem correct,
though.

I get the same answer for both too...however, try a 3rd query to see if
(in
Access) they equal each other...and I get false.

i.e. ?Int(100*1.1)=100*1.1


:

I just tried to re-create this "error" and I get 110 as an answer for
either
case. I pressed Ctrl-G to bring up the Debug window. Then I typed in
the
following with the corresponding results:

?Int(100*1.1)
110
?100*1.1
110

I'm using Access97 SR2 also.

OK, I have read the previous posts and put into Access. All works
fine
for
the most part, but a couple of entries give rise to strange
results.

I have 2 fields:
[Net_Amount] being the amount of an item before tax ($#,##0.00)
[GST_Flag] being a yes/no field to indicate if I need to add
10%
tax

I need to round the amounts up to the nearest cent.
Formula I am using is:



=IIf([NetAmount]*IIf([GST_Flag],1.1,1)*100=Int([NetAmount]*IIf([GST_Flag],1.

1,1)*100),[NetAmount]*IIf([GST_Flag],1.1,1)*100,Int([NetAmount]*IIf([GST_Fla
g],1.1,1)*100+1))/100

Essentially, this gives me a roundup to the nearest cent including
a
10%
tax
if the flag is ticked. Tested it and it work fine. for example,

$123.45 plus tax comes out as $135.80

However, $100.00 does not work. This comes out as $100.01. Dont
ask
me
why
and I have tried and tried, but Int(100*1.1) does not equal 100*1.1
in
my
copy of Access 97. I am using SR-2.

Anyone any ideas? Can anyone else recreate this error?

Please help!!! Why couldn't Access have a RoundUp function built
in?

 

[Douglas J Steele]

And to think I used to like you... <g>

BTW, what makes you think I'm human? Maybe that line should have been

"Just as you humans can't represent, say, 1/3 exactly, ..."

 

[Van T. Dinh]

Ha ...

I reckon Microsoft should check potential MVPs and make sure they belong to
the human race ...