if i want to have the result f a calulation look like this 36,123.19342 must i format result before i do this me.txtResult = dblResult?

Question

I see there is a built in format that includes " , " when it should. And "Decimal Places" with  settings ranging from zero to auto.  I see the "standard" and "general"  seems I cannot have the " , " and the have a correct # of decimal places.

the number in the txtbox could range from  10 to -37 to 36,123 to 36,456.123458 to 0.0000125

using Scientific would not be easy for the user.   and this is not a currency value.

is there a easy way to do using the formatting drop down box settings in the property sheet of the textbox?

I am asking because there have been more than a few cases where the answer is much, much simpler than I thought wat the only way to do something.

I think it is easier due to this is only showing the results.  I think it is possible to use format or numberformat to do this, not sure.  I have not needed to format anything this fancy.  

yes I did see a format built-in function for only formatting numbers. if there is NOT a easy way using the property sheet of the textbox.  Then I will be searching for that "NumberFormat" or "FormatNumber" and be hoping it did applies to win 10 , access 2016.

iit seems to me if I have to use "format" I had to do a bit of searching to find all the settings I needed to use the last time. Lots of settings were next to the example, but I had to find ones not on that page, or even near, to find what I needed to format it how I needed it to be.

Mark J

---

Mark J

Answer 1

Mark –

It’s not entirely clear from your description what your formatting needs are. Let me suggest that you insert a new Module, call it Explorations. Insert a Public Sub XFormatting and play around with it for an hour or until you exhaust your patience. When you don’t get what you want out of the test procedure, post with more detail.

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 2

Mark - I would format the number field at the table level. If you use this format:

#,##0.0000

it will put in the comma and pad the right side with up to 4 zeroes. If you format at the form control level (the text box) it won't show properly until the cursor is in the box.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 3

If you don't want to include trailing zeros use #,##0.##### as the format. This will return the value to five significant decimal places, but will not include trailing zeros where an expression evaluates to a number with less than five decimal places.  You can see this in the immediate window, e.g.

dblResult = 36123.19342345
? Format(dblResult,"#,##0.#####")
36,123.19342

whereas:

dblResult = 36123.193
? Format(dblResult,"#,##0.#####")
36,123.193

However, this won't cope if the expression returns an integer value, returning a trailing decimal point, so to cover all bases you'd need to conditionally format the value, e.g.

dblResult = 36123
? Format(dblResult,IIf(dblResult = Int(dblResult),"#,##0","#,##0.#####"))
36,123

dblResult = 36123.193
? Format(dblResult,IIf(dblResult = Int(dblResult),"#,##0","#,##0.#####"))
36,123.193

dblResult = 36123.19342345
? Format(dblResult,IIf(dblResult = Int(dblResult),"#,##0","#,##0.#####"))
36,123.19342

You can assign the formatted value to a control, therefore with:

Me.txtResult = Format(dblResult,IIf(dblResult = Int(dblResult),"#,##0","#,##0.#####"))

Note that the Format property returns a string expression, so you cannot do any further arithmetic on the returned value.  If any further arithmetic is to be done it should be done on the dblResult variable, not the value of the control.  If any further arithmetic is required to be done on the value rounded to five significant decimal places the arithmetic should be done on the return value of the Round function, e.g.

dblResult = 36123.19342345
? Round(dblResult,5)* 2
 72246.38684


PS:  even better would be:

? Format(Round(dblResult,5),IIf(Round(dblResult,5) = Int(dblResult),"#,##0","#,##0.#####"))

which would cope with values like 36123.0000000001

---

Ken Sheridan, Stafford, England

Answer 4

Ken,

Thank you so much. 

you were very clear.     VERY very helpful.

also THANK  YOU FOR THE WARNING ABOUT IT RETURNS A STRING SO I CANNOT ANY MORE ARITHMETIC ON THE RESULTS.

Mark J

---

Mark J

Answer 5

Peter N Roth,

sorry i was not clear enough.

what i had was the result of some math    stored in dblResult  (the variable was dim as a double)

what i needed to do was format how the result looked in a textbox.

i could not find out if was a way to use a built-in property of the textbox to get the formating i wanted.

i tried to reply to your post at the same time as i did the others.

BUT i kept getting msg you must close open post first.   i did not have a open post. and outlook was closed.

i hope this helps explains what i was asking.

Mark J

p.s. only knowing the terms to format date and time wouldn't make any difference on how long i played with it. due to i was not trying to format as date or time.

---

Mark J

Answer 6

Yes, it helps. And you got some answers you can proceed with.

Glad it worked out.

---

peter n roth - http://PNR1.com, Maybe some useful stuff