format a query for calculated field in a list box

[ahvc]

Dear friends
How do i format numbers in a list, while they are calculated and not from a specfic table. For example, Iwant to show the hours, rate, and total for many jobs, where only hours and rate are part of the database. The query had to fill in the list box with the total also.

My question is ,I want to format such a calculated field. In this case, I want the total to be of the format $999.99, right now, I get results like 34.343, while I need only two decimals at the end..

can you please show me some light..
thanks
VC

 

[Ally]

If you display the results in a form / report you can set the decimal points property to 2.

 

[Rich]

You can set the format and decimal places on the list box's underlying query property sheet, failing that use the Format function

 

[ahvc]

multiple columns in list box

Hi Rich,Ally

Thanks so much for writing.

The query I am using is in a list box, in a form.

The query I would be using is
"SELECT units, rate,rate*units AS [TOTAL] FROM service"

Since these are number, I would like to right align them. and if possible,put '$' sign before the nos.

I searched for 'format' command in my access2k, but nothing came up.

can you please help.

i really appreciate it.
with thankful heart,
vc.

 

[Ally]

If you format your fields at table level to Currency in your data type and Currency with $ in the field properties, you will then get $ displayed in your list box and it will automatically right align it because it's currency.

 

[ahvc]

not able to set format for a calculated field.

Hi Ally
I am able to set the format property as you said at table level for the fields which are database fields. But for the calculated field (rate * hours) , Iam not able to set format property in the query of the listbox.

Probably I need touse a 'format' command in the query? Which I am not able to find in my access help.

Thanks for your help.
VC

 

[Ally]

I've just had a play with some dummy data, and when I do a calculation, it is displayed with the £, because the calculation is using a currency figure. Therefore I'm not sure why yours isn't.

Unfortunately, I'm sorry, I was wrong about it right-aligning. It does do it in tables but not in listboxes.

I did a search in help and there is a RSet statement to right align text, but it has to be a string. Perhaps there's a way you can convert your result to a string?! Code's not a strong point so I hope someone else can help. Sorry I can't be of any more help to you.

Ally

 

[raskew]

From the debug window...

Fmt = "$###,###,##0.00"
widget = 123.456789
? format(widget, fmt)
$123.46

 

[Rich]

Total:Format([Rate]*[Units],"Currency")

 

[ahvc]

Farmat works; but no right align

Rich, Thanks for that.
But as Ally said, it does not do right align even though it is currency.

I tried the Rset command as suggested by Ally, but it did not work in the query. (After changing it to string).

Is there any way to right align em in the list box,especially the calculated field?

Thanks so much
VC