Help with formatting a query

[Rob Wills]

I'm trying to set up my field with 2 decimal places and the Thou sep in place
(e.g. 2,569.52)

I know how to right click on the field to set my property - but I want to do
this dynamically.

Access won't accept the Cdec function using SQL, but it will accept the Ccur
function
(I can't use this as I am looking at differenct currencies)

Basically what I want to write looks like this (if this worked)

SELECT ccy, Cdec(Sum(Amount)) as total
FROM table a
GROUP BY ccy

is there another way of doing this in SQL?

I've already tried writing a VBA function as per another suggestion
==================
function New_Cdec(myVal)
New_Cdec = Cdec(myVal)
end function
==================

but the problem with this is that it returns a varient so any formatting is
lost once the query displays...

Any other suggestions welcome...

Thanks
Rob

 

[Duane Hookom]

Aren't you displaying the results of your query in a form or report? If so,
set the formatting in your text box.

The problem with formatting in a query is the value becomes a variant text
value and can't be used as a number without converting back to numeric.