divide two numbers to get a percent and compare to field in datab.

  • Thread starter Thread starter Vicki47
  • Start date Start date
V

Vicki47

I want to a small number by a karger number(yielding a decimal ie: .81)in the
query and compare that to a field in my dataset. I can get whole number
results, but not decimal results.
 
The problem you may be having is that the decimal part of your number field
will likely have a small decimal part extended to several digits that you
don't even know about. For example, what you think is .81 may in fact be
..8100000000002
In that case, comparing to .81 will always fail. I suggest you format both
numbers to a desired number of decimals and compare on that:

WHERE format(5/6,"#.00") = format([MyField], "#.00")

This will compare on two decimal places.
 
Thanks, Klatuu. You gave me hope, but my problem is not quite solved. I am
showing two example queries that I ran just to see if I could get a value and
their results.
SELECT format((50)\20,"#.00") AS ['value']
FROM CLTVTiers;
2.00

and

SELECT format(5\20,"#.00") AS ['value']
FROM CLTVTiers;

..00

I wonder if it is related to another problem I am experiencing. When I
tried to format some other fields in the same dataset as decimal, I can not
get the digits to the right of the decimal to display, either.
Thanks,
Vicki47

******************************************************
Klatuu said:
The problem you may be having is that the decimal part of your number field
will likely have a small decimal part extended to several digits that you
don't even know about. For example, what you think is .81 may in fact be
.8100000000002
In that case, comparing to .81 will always fail. I suggest you format both
numbers to a desired number of decimals and compare on that:

WHERE format(5/6,"#.00") = format([MyField], "#.00")

This will compare on two decimal places.

--
Dave Hargis, Microsoft Access MVP


Vicki47 said:
I want to a small number by a karger number(yielding a decimal ie: .81)in the
query and compare that to a field in my dataset. I can get whole number
results, but not decimal results.
Click to expand...
Click to expand...
 
Vicki,

You are using the integer division symbol ( \ ) rather than the regular
division symbol ( / ). Change that in your queries and see what you get.

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Vicki47 said:
Thanks, Klatuu. You gave me hope, but my problem is not quite solved. I am
showing two example queries that I ran just to see if I could get a value and
their results.
SELECT format((50)\20,"#.00") AS ['value']
FROM CLTVTiers;
2.00

and

SELECT format(5\20,"#.00") AS ['value']
FROM CLTVTiers;

.00

I wonder if it is related to another problem I am experiencing. When I
tried to format some other fields in the same dataset as decimal, I can not
get the digits to the right of the decimal to display, either.
Thanks,
Vicki47

******************************************************
Klatuu said:
The problem you may be having is that the decimal part of your number field
will likely have a small decimal part extended to several digits that you
don't even know about. For example, what you think is .81 may in fact be
.8100000000002
In that case, comparing to .81 will always fail. I suggest you format both
numbers to a desired number of decimals and compare on that:

WHERE format(5/6,"#.00") = format([MyField], "#.00")

This will compare on two decimal places.

--
Dave Hargis, Microsoft Access MVP


Vicki47 said:
I want to a small number by a karger number(yielding a decimal ie: .81)in the
query and compare that to a field in my dataset. I can get whole number
results, but not decimal results.
Click to expand...
Click to expand...
Click to expand...
 
Dale gave you the correct answer regarding using the correct operator, but in
addition, use something other than Value as a name, Value is a reserved
word. Also, you don't need to put the single quotes in it.
Try:
SELECT format(50/20,"#.00") AS PercentValue
--
Dave Hargis, Microsoft Access MVP


Vicki47 said:
Thanks, Klatuu. You gave me hope, but my problem is not quite solved. I am
showing two example queries that I ran just to see if I could get a value and
their results.
SELECT format((50)\20,"#.00") AS ['value']
FROM CLTVTiers;
2.00

and

SELECT format(5\20,"#.00") AS ['value']
FROM CLTVTiers;

.00

I wonder if it is related to another problem I am experiencing. When I
tried to format some other fields in the same dataset as decimal, I can not
get the digits to the right of the decimal to display, either.
Thanks,
Vicki47

******************************************************
Klatuu said:
The problem you may be having is that the decimal part of your number field
will likely have a small decimal part extended to several digits that you
don't even know about. For example, what you think is .81 may in fact be
.8100000000002
In that case, comparing to .81 will always fail. I suggest you format both
numbers to a desired number of decimals and compare on that:

WHERE format(5/6,"#.00") = format([MyField], "#.00")

This will compare on two decimal places.

--
Dave Hargis, Microsoft Access MVP


Vicki47 said:
I want to a small number by a karger number(yielding a decimal ie: .81)in the
query and compare that to a field in my dataset. I can get whole number
results, but not decimal results.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top