Query for text output

[Guest]

I have a table with a field (I have tried making it currency and number with
a fixed decimal of 2) that may contain values like 1.1 or 22.222 or
3333.3333. I need to put it in a text field that is 8 characters long with a
format "xxxxx.xx". The internal storage retains the decimal and the
conversion to text retains the exact number. Is there a string function that
retains only the two decimal places? Or a table configuration that only
retains the number in the proper format?

 

[Guest]

What characters you want in front of the number if the number doesn't need 8
characters.

For example, if you want spaces in from, you can use something like:

Right$(Space(4) & Format([YourNumber], "0.00"), 8)

Check Access VB Help also on the FormatNumber() and FormatCurrency() function.

 

[John Spencer]

It depends on what you want to do with numbers like 123.567 and whether or
not you want leading zeroes and ... Do you want rounding of the fractional
portion or do you want truncation of the fractional portion.

Assumptions:
-- Leading zeroes are desired
-- Truncation is desired (that is 123.567 will return 000123.56 and not
000123.57)
-- Text string is desired and not a numeric value which can be manipulated

The following will produce a string with six numeric characters before the
decimal point and 2 characters after the decimal point. If you pass it a
null value it will return "00000000" - note the lack of a decimal.

Right(string(8,"0")& Format(Int(1234.568*100)/100,"0.00"),8)

 

[Guest]

John, your assumtions are correct, except for the second one. if the number
is 123.567, the value I want is 123.57. So text field should be"00123.58"...

 

[Guest]

sorry - "00123.57" is what is desired.....

John, your assumtions are correct, except for the second one. if the number
is 123.567, the value I want is 123.57. So text field should be"00123.58"...

 

[John Spencer]

To get it to round, drop the int and the multiplication and division.

Right(string(8,"0")& Format(1234.568,"0.00"),8)