Hello all,
I'm trying to format data returned by a SQL select query in MS Access. For ex, I have data dispaying as "6500000" when I desire commas to denote thousands and perhaps to take it out 2 or more decimals (i.e. "6,500,000.00").

Any way to enforce this formatting with a Select statement? I've seen "Cast" and "Convert" but I don't think these play in the Access SQL world, at least if they do I can't figure the syntax out.

Any ideas out there? Thanks in advance for any assistance.

Format the control on the form or report where the data is being displayed. It doesn't need to be formatted at the query level, users shouldn't have access to the tables or queries.

I agree with Fiat. You also have the option to apply formats in your tables. Type in "Format Property" in Access help. You should find what you are looking for.

Thank you both for your replies, and you are certainly right about using reports/forms to control the data.

We have a tendency to need answers on the fly, so this is not really an end user consumption database application, but rather a working research database so to speak. The constantly changing data needs rules out reports as they are inflexible and laborious to modify, however it is still nice for clarity to get the formatting right on the screen even if it never goes to print.

I believe there is a date format handling function like: select format(date,dddd). Surely there is an equivalent SQL funciton for number formats also. If not, I guess I'll have to go the report route.

Thanks again guys. Really appreciate the input

Format(FieldName,"#,##0.00")

That syntax does it - thanks so much!!! Really appreciate the time and expertise.

You are welcome.