formatting results using sql select with jet

Question

I have data in Excel files that I am pulling out using the MS Jet OLEDB provider. The results of my select are being passed into a component that I cannot change to generate an html table on the fly. Some of my data files contain numbers that I need formatted with commas (#,###). As I said I cannot change the output component to format the string for me. Is there a way I can format my results during my select statement. I have tried a bunch of different things but nothing has worked.

Here is an example select where I need to format enrollment...

select
[SCHOOL NAME],
[ENROLLMENT],
[STUDENT / TEACHER RATIO]
from [Public School Districts$]
where [ENROLLMENT]<>null

Any help would be greatly appreciated!

Answer 1

Can't try this out right now, but something like

FORMAT( [ENROLLMENT], '#,###')

should do the trick.

There are a few standard formats that would help you internationalization, but may not give you exactly what you want. Just google Format Access.