percentage value round off

[Ravi Kumar]

Hello all ,

In my access database , i am using replace function to automatically fill the body are of my outlook while sending as below :

=Replace(Replace(Replace("Please note that there is a high qty of rework found in above order where inspected qty is:|1  Rework qty is: |2  And % is:|3 ","|1",Nz([Inspected_Qty],"")),"|2",Nz([Rework_Qty],"")),"|3",Nz([Rework_Percentage],""))

But in the outlook the value of percentage coming as scientific number (7.189543E-02) instead of percentage(7.2%)

the property of this field is :

kindly guide me how to solve this

 

[Gasman]

Use the Format() function?

 

[neuroman9999]

well I don't really think the setting regarding the data type itself is relevant to anything you're doing in outlook, if you're throwing the data from access to outlook. this is certainly strange though. how exactly is this number getting into an outlook message? code? another way? to me, the fact that it is appearing as scientific notation doesn't make much sense.

 

[Ravi Kumar]

Thank you so much for your reply ,

how exactly is this number getting into an outlook message? code? another way?

From the macro function "Emaildatabaseobject" and there i am using the above expression in subject field

 

[neuroman9999]

Thank you so much for your reply ,

From the macro function "Emaildatabaseobject" and there i am using the above expression in subject field

well personally, I think the format that is specified in the table, which you see in your image, applies to the data in the table and thusly in an access form too. because a form is nothing more than a viewpoint for table info. so it's all the same thing, really.

but....if I were you, I would try @Gasman 's solution first and try to encapsulate it with the format() function. that way, you can try to force the data to change before it leaves access, or even after it leaves for that matter.

it still does not make any sense that the data is changing its representation from program to program. personally, I've never seen that happen, ever. if you can't figure it out, can you upload the file you're working with so someone can run a test and see if they can replicate the prob?

 

[The_Doc_Man]

The data representation changes because that number, properly shown in an explicit fixed notation with enough decimal points, would show what you wanted. You saw 7.189543E-02 - which is actually 0.07189543 - instead of 7.2 (as a percent). At least two things are happening here.

First, because of that double-barrelled leading 0 (one to the left and one to the immediate right of the decimal), Access by default switches to scientific notation. That is because 0.0xxx in common notation is an unnormalized fraction by Access (actually, VBA) standards. You might consider some kind of expression in your query to multiply the fractional number by 100.0 and then format it with a "00.0" or "##.#" template with an explicit % sign concatenated after it. I.e. use Format function & "%" to get your right result.

BUT the second thing that you have to watch out for is that you used a SINGLE for the representation and then went fractional with a decimal number that is not an integer power of two. Therefore, you invite some serious representational errors. The reason for this is that the fraction in Access SINGLE notation is a binary fraction with a limited number of bits, I think 23 bits total. A SINGLE is rated for 6 full decimal digits and part of a 7th digit in precision. But the fraction is actually 72/1000 if you look at it as a normalized fraction. That division by 1000 can be factored as THREE divisions by 10, which is really three divisions by 5 and three more divisions by 2. And there is the rub. Dividing by 5 is totally not good in binary because in binary fractions, 1/5 is an irrational number. I.e. you can divide 2 by 5 about as well as you can divide 10 by 3. It NEVER comes out even. And that is why I suggested you multiply by 100 to get rid of some of that irrationality. For what it is worth, switching to DOUBLE might or might not work to make it better. The binary fraction in 23 bits remains irrational when in 55 bits.

 

[neuroman9999]

that's way over my head, Richard. and probably over his too! =)

 

[The_Doc_Man]

Well, Adam, I guess I have given you a new goal. Perhaps one day, in your claimed superior knowledge, you will find a chance to contemplate this until you reach understanding.

 

[neuroman9999]

you will find a chance to contemplate this until you reach understanding.

most of it I get, grandpa. but the reason I didn't read it thoroughly is because it doesn't really apply to stuff I've done. and trust me brother, it wouldn't take much reading. and I NEVER said I was superior. you're targeting me again.

 

[The_Doc_Man]

OK, abandon this line to avoid hijacking the thread.

Ravi Kumar, your best bet is to "re-scale" the number and then format it. This can be done in a query so that you leave your "raw" data intact in the table.

 

[Ravi Kumar]

OK, abandon this line to avoid hijacking the thread.

Ravi Kumar, your best bet is to "re-scale" the number and then format it. This can be done in a query so that you leave your "raw" data intact in the table.

I am very sorry for the late reply , i was hospitalized due to covid-19 , i used the format() and now it coming as percentage itself , thank you so much for your help.