$#,###.## format in a List Box

B

Bob V

My list box is showing 1234.6789 When It should be showing $1,234.67 can I
format my list box, my query that is showing the correct format
Thanks for any Help....Bob
 
J

John W. Vinson

My list box is showing 1234.6789 When It should be showing $1,234.67 can I
format my list box, my query that is showing the correct format
Thanks for any Help....Bob
Click to expand...

Put a calculated field in the query using the Format() Function:

ShowNum: Format([yourfield], "Currency")
or
ShowNum: Format([yourfield], "$#,###.00")

and display this field in the listbox. If it's also the bound column, leave
both the unformatted and the formatted one - make the Format() value the
visible column and the unformatted one the bound column.

John W. Vinson [MVP]
 
B

Bob V

Thanks John.....Bob

John W. Vinson said:
My list box is showing 1234.6789 When It should be showing $1,234.67 can I
format my list box, my query that is showing the correct format
Thanks for any Help....Bob
Click to expand...

Put a calculated field in the query using the Format() Function:

ShowNum: Format([yourfield], "Currency")
or
ShowNum: Format([yourfield], "$#,###.00")

and display this field in the listbox. If it's also the bound column,
leave
both the unformatted and the formatted one - make the Format() value the
visible column and the unformatted one the bound column.

John W. Vinson [MVP]
Click to expand...
 
J

Jamie Collins

My list box is showing 1234.6789 When It should be showing $1,234.67 can I
format my list box, my query that is showing the correct format
Click to expand...

Put a calculated field in the query using the Format() Function:

ShowNum: Format([yourfield], "Currency")
or
ShowNum: Format([yourfield], "$#,###.00")
Click to expand...

Assuming the data in question is of type CURRENCY, your suggestion
will not return the OP's desired result e.g.

SELECT CCUR(1234.6789) AS test_value,
Format(test_value, '$#,###.00') AS result_text

returns '$1,234.68' whereas the OP wanted it to end in '.67'.

The Format() expression (not 'Function' <g>) exhibits symmetric
rounding i.e. 0.5 always rounds away from zero. The OP hasn't stated
which rounding algorithm but it's not banker's rounding either, most
often associated with monetary amounts e.g.

SELECT CCUR(1234.6789) AS test_value,
ROUND(test_value, 2) as result_value

returns 1234.68, noting that the Round() expression exhibits banker's
rounding.

I would suggest the OP takes a look at this KB article:

How To Implement Custom Rounding Procedures
http://support.microsoft.com/kb/196652

to find a recognized name for their rounding algorithm then search
these groups' archives for a suitable Jet expression.

Jamie.

--
 
Top