Text Formatting: Add "." to text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following dataset of numbers stored as text (I would like to leave
it as text so that the leading 0s are not dropped):

02345
1234567
1234567ABC
123456789

I would like it to appear as follows:

02345
12345.67
12345.67.ABC
12345.67.89

I wrote the following SQL statement:

Format(tblChargeNum713.ProjNum,"!@@@@@.@@.@@@") AS Expr1

This is my result:
02345. .
12345.67.
12345.67.ABC
12345.67.89{space}

Is there a way that I can make my query understand NOT to add the periods or
the spaces when there is only a 5- or 7-digit number?

Thanks in advance!
 
Hi,

You probably could check the length first then format appropriately:

For example in VBA code:

if len(tblChargeNum713.ProjNum) = 10 then
Format(tblChargeNum713.ProjNum,"!@@@@@.@@.@@@") AS Expr1
else
if len(tblChargeNum713.ProjNum) = 7 then
Format(tblChargeNum713.ProjNum,"!@@@@@.@@") AS Expr1
else
you get the idea...

if you are doing this in a query you can use the iif function to do this also:


hope this helps!
 
Leslie said:
I have the following dataset of numbers stored as text (I would like to leave
it as text so that the leading 0s are not dropped):

02345
1234567
1234567ABC
123456789

I would like it to appear as follows:

02345
12345.67
12345.67.ABC
12345.67.89

I wrote the following SQL statement:

Format(tblChargeNum713.ProjNum,"!@@@@@.@@.@@@") AS Expr1

This is my result:
02345. .
12345.67.
12345.67.ABC
12345.67.89{space}

Is there a way that I can make my query understand NOT to add the periods or
the spaces when there is only a 5- or 7-digit number?
Click to expand...


Trim(Format(ProjNum,
Switch(Len(ProjNum)<=5, "!@@@@@",
Len(ProjNum)<=7, "!@@@@@.@@",
True, "!@@@@@.@@.@@@")
)) AS Expr1
 
Back
Top