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

Question

In the MS Access 2007 project, I have a report with the following record source:

SELECT SomeTable.SomeCol FROM SomeTable; 

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

First, take 3, 6, and 9. Let's say that 123 - this should remain like 123 . If it is 123456 , this should be changed to 123.456 . If it is 123456789 , this should be changed to 123.456.789 . If it is 1 or 65 , it should be changed to 001 and 065 respectively.

How can I change my SELECT to make these changes? If the length is not 1, 2, 3, 6, or 9, the line should remain unchanged.

Thanks!

Answer 1

I found a solution, although it is not very pretty.

SomeTable.SomeCol is actually a very long string, so it looks a lot worse on my system. SELECT is part of a much larger query with multiple SomeTable , so SomeTable is required. I have the feeling that 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; 

Answer 2

I don’t know your locale, so I don’t know whether the default numbers or comma are:

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

You can use a similar format operator without basing your report on the request, but make sure you change the name of the control before setting it to the function.