Add a . every 3rd digit/pad with 0 in SELECT

Question

In an MS Access 2007 project, I have a report with the following Record Source:

SELECT SomeTable.SomeCol FROM SomeTable;

SomeCol should be either 1, 2, 3, 6, or 9 digits long.

Let's take the 3, 6 & 9 first. Say it is 123 - this should remain as 123. If it's 123456, this should be changed to 123.456. If it's 123456789, this should be changed to 123.456.789. If it's 1, or 65, this should be changed to 001 and 065 respectively.

How can I modify my SELECT to perform these changes? If the length is not 1, 2, 3, 6, or 9, the string should remain unmodified.

Thanks!

Undergrown

What datatype is your SomeCol – Souther

@RowlandShaw It is Text - I know, I know. It should be a number. This is something I unfortunately have no control over. – Undergrown

Answer 1

I found a solution, although it's not very pretty.

In reality, SomeTable.SomeCol is a very long string, so it looks much worse on my system. The SELECT is part of a much larger query, with multiple JOIN's, so SomeTable is required. I have a feeling this is the best solution:

SELECT switch(
    len(SomeTable.SomeCol) < 3,
        format(SomeTable.SomeCol, '000'),
    len(SomeTable.SomeCol) = 6,
        format(SomeTable.SomeCol, '000\.000'),
    len(SomeTable.SomeCol) = 9,
        format(SomeTable.SomeCol, '000\.000\.000'),
    true,
        SomeTable.SomeCol
) as SomeCol
FROM SomeTable;

Undergrown

@HansUp This is only to be used in a single report, so I think doing it in the SQL is ok. – Undergrown

Answer 2

I do not know your locale, so I don't know whether numbers default to a stop or a comma:

SELECT IIf(Len([atext])<4,Format([atext],"000"),Replace(Format(Val([atext]),"#,###"),",",".")) AS FmtNumber
FROM Table2 AS t;

You could use a similar format statement without basing your report on a query but make sure you change the name of the control before setting it to a function.

Konstance

This helped a lot. I didn't use this exact SQL, but the use of format() inspired a much better way to do this. – Undergrown