Search code examples
sqlselectformattingoledbjet

Formatting results using SQL select with JET

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!

Solution

  • 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.