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!

@RowlandShaw It is Text - I know, I know. It should be a number. This is something I unfortunately have no control over. — Danny Beckett, Sep 24 '12 at 07:35

Danny Beckett · Accepted Answer · 2013-05-04T11:38:25.787

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;

edited May 04 '13 at 11:38

answered Sep 24 '12 at 07:55

Danny Beckett

20,529
24
107
134

@HansUp This is only to be used in a single report, so I think doing it in the SQL is ok. – Danny Beckett Sep 24 '12 at 10:45

Fionnuala · Answer 2 · 2012-09-24T09:14:29.720

0

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.

edited Sep 24 '12 at 09:14

answered Sep 24 '12 at 08:28

Fionnuala

90,370
7
114
152

This helped a lot. I didn't use this exact SQL, but the use of `format()` inspired a much better way to do this. – Danny Beckett Sep 24 '12 at 10:51

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

2 Answers2

Linked