Insert Commas

[jseger22]

Hi,

I have a string field of numbers that can be up to 26 characters
long. What I am trying to do is insert a comma every other number so
a string value of 020507 would look like 02,05,07. I started
designing a query using If statements based on the length of the
fields, but I was wondering if there is an easier way to do this?

Thanks

 

[Klatuu]

A replace function could be used, but the rules for where a number starts and
stops would have to be defined. For example how do you know that 020507
should not come out as 020, 507 or 0, 2, 0, 5, 0, 7?

 

[John Spencer]

You might try something like the following

Left(Format("01234567","!@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@"),Instr(1,Format("01234567","!@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@"),"
")-2)

Other than that, you will probably need to write a custom function.

How do you want an odd-number of characters handled?


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[fredg]

Hi,

I have a string field of numbers that can be up to 26 characters
long. What I am trying to do is insert a comma every other number so
a string value of 020507 would look like 02,05,07. I started
designing a query using If statements based on the length of the
fields, but I was wondering if there is an easier way to do this?

Thanks

Just create your own User Defined function in a module, then call the
function from the query, passing the Field's value.

Function AddCommas(FieldIn as String) as String

Dim intX As Integer
Dim intY As Integer
intX = 2
Do While intX < Len(FieldIn)
FieldIn = Left(FieldIn, intX) & "," & Mid(FieldIn, intX + 1)
intX = intX + 3
Loop
AddCommas = FieldIn
End Function

Pass the field value to the function using:
NewColumn:IIf(Not IsNull([FieldName]),AddCommas([FieldName]),"")

 

[jseger22]

You might try something like the following

Left(Format("01234567","!@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@"),Instr(1,F-ormat("01234567","!@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@"),"
")-2)

Other than that, you will probably need to write a custom function.

How do you want an odd-number of characters handled?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.








- Show quoted text -

Thanks for the help!

I won't need to do anything for odd numbered of characters the way I
set it up there will always be an even number.

So I inserted that into my query replacing the "01234567" with my
field name and it reformats the formula to look like
Left(Format([NegativeResponse],"!@@\,@@\,@@\,@@\,@@\,@@\,@@\,@@\,@@\,@@
\,@@\,@@\,@@"),InStr(1,Format([NegativeResponse],"!@@\,@@\,@@\,@@\,@@
\,@@\,@@\,@@\,@@\,@@\,@@\,@@\,@@"),"
")-2). I am not sure what I am doing wrong.

 

[John Spencer]

Did you try the query?

The back slashes are being put in by Access to indicate that the next
character is a literal character - that is just put in a comma here..

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

You might try something like the following

Left(Format("01234567","!@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@"),Instr(1,F-ormat("01234567","!@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@,@@"),"
")-2)

Other than that, you will probably need to write a custom function.

How do you want an odd-number of characters handled?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.








- Show quoted text -

Thanks for the help!

I won't need to do anything for odd numbered of characters the way I
set it up there will always be an even number.

So I inserted that into my query replacing the "01234567" with my
field name and it reformats the formula to look like
Left(Format([NegativeResponse],"!@@\,@@\,@@\,@@\,@@\,@@\,@@\,@@\,@@\,@@
\,@@\,@@\,@@"),InStr(1,Format([NegativeResponse],"!@@\,@@\,@@\,@@\,@@
\,@@\,@@\,@@\,@@\,@@\,@@\,@@\,@@"),"
")-2). I am not sure what I am doing wrong.

 

[jseger22]

Hi,

I have a string field of numbers that can be up to 26 characters
long. What I am trying to do is insert a comma every other number so
a string value of 020507 would look like 02,05,07. I started
designing a query using If statements based on the length of the
fields, but I was wondering if there is an easier way to do this?

Thanks

Just create your own User Defined function in a module, then call the
function from the query, passing the Field's value.

Function AddCommas(FieldIn as String) as String

Dim intX As Integer
Dim intY As Integer
intX = 2
Do While intX < Len(FieldIn)
FieldIn = Left(FieldIn, intX) & "," & Mid(FieldIn, intX + 1)
intX = intX + 3
Loop
AddCommas = FieldIn
End Function

Pass the field value to the function using:
NewColumn:IIf(Not IsNull([FieldName]),AddCommas([FieldName]),"")

Great!, I used this method and it worked perfect. Thanks for
everyones help!