Text Formatting: Add "." to text

[Leslie W.]

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!

 

[kissybean]

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 W.]

Krissybean,

What is your suggestion for the IIF statement?

Thanks for your help!
Leslie

 

[Marshall Barton]

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?

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