Insert Commas

  • Thread starter Thread starter jseger22
  • Start date Start date
J

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
 
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?
 
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
..
 
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
Click to expand...

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]),"")
 
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 -
Click to expand...

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.
 
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 -
Click to expand...

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.
Click to expand...
 
Hi,
Click to expand...
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?
Click to expand...
Thanks
Click to expand...

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]),"")
Click to expand...

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