CODEWITHSUNDEEP
sqlselectformattingoledbjet
NJChim
NJChim

Reputation: 297

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!

Upvotes: 0

Views: 570

Answers (1)

Thorsten
Thorsten

Reputation: 13181

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.

Upvotes: 1

Related Questions