Formatting mshflexgrid columns

[TomG]

I am binding a mshflexgrid control to an ADO datasource, created through an SQL statement. Is there a way to format a column, for all rows, in the grid?

My specific example is I'm retreiving a numeric department number I'd like to pad with leading zeros. (e.g. 99 displays as 0099) I'm using an ODBC datasource and can't use format$ in the SQL statement.

Any ideas would be appreciated. (Especially if it CANNOT be done, cause I'm tired of looking for an answer...)

Thanks
Tom

[Gen-X]

If you are using SQL through ODBC why not use T-SQL commands?

It may change the datatype but that depends on if you are viewing only or if you want to update.

Ie :

Lets say we have a field Copies...

Originally you do this :

SELECT Copies FROM Table

Output :
5
23
6
3
567


Instead you do this :

SELECT RIGHT('0000' + CONVERT(VARCHAR,Copies),4) as Copies FROM Table

Output :
0005
0023
0006
0003
0567


What you have done is turned the actual value into a character string... added a number of zeros to the start and then taken the RIGHTMOST number of characters such that the total equals the correct amount of padding you want.

Hope it helps

[TomG]

I get the use of the Right function, but didn't think I could use it as the database I'm going against is SQL Anywhere.

What your answers suggests is that I can use T-SQL because I'm going through ODBC?

I don't think I even tried it, just assumed it wouldn't work. I'll give it a go, thanks for your help!

[JHausmann]

T-SQL is Sybase and SQL Server specific.

[Gen-X]

Welcome to the world of "Functional Dependancy".

If you are using SQLAnywhere then you should read the manuals to determine if they have functions to allow you to get the right of a string and also to convert an integer into a string.

I quoted you T-SQL because that was the format I know... SQLAnywhere is very old (unless there is a new version) which means these functions might NOT be available.

[TomG]

Found an online help file at my client's, and found the CONVERT function in it. So, of course, the code listed above works like a charm.

Manuals/Help ARE sometimes a good thing. I had a UNION query with an ORDER BY clause that was blowing up in SQL Anywhere. Turns out I have to specify a column number in the ORDER BY clause, not a column name...

Thanks for the help