Replace last comma with "and"

P

pdemarais

I have a access 2003 database that has a table in that has 2 fields:
Job Number and Description. The description is text field that has
expressions seperated by a comma. I would like to change the last comma
or if only comman in that string to the word "and".
Does anybody have any suggestions? I am more an access expert than a
vba expert.

thanks in advance
 
P

pdemarais

Is there an easier way?

Jamie said:
The description is text field that has
expressions seperated by a comma. I would like to change the last comma
or if only comman in that string to the word "and".
Does anybody have any suggestions? I am more an access expert than a
vba expert.
Click to expand...

Here's a SQL solution (example) which uses a sequence auxilary table
(i.e. table of incrementing integers) to parse the text, find the last
comma and replace it:

Sub parmlist()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection

' Create 100K row 'sequence' auxilary table
.Execute _
"CREATE TABLE [Sequence] (seq INTEGER NOT" & _
" NULL CONSTRAINT pk__Sequence PRIMARY KEY);"
.Execute _
"INSERT INTO [Sequence] (seq) VALUES (-1)"

Dim sql
sql = sql & _
"INSERT INTO [Sequence] (seq) SELECT Units.nbr" & _
" + Tens.nbr + Hundreds.nbr + Thousands.nbr" & _
" + TenThousands.nbr AS seq FROM ( SELECT" & _
" nbr FROM ( SELECT 0 AS nbr FROM [Sequence]" & _
" UNION ALL SELECT 1 FROM [Sequence] UNION" & _
" ALL SELECT 2 FROM [Sequence] UNION ALL" & _
" SELECT 3 FROM [Sequence] UNION ALL SELECT" & _
" 4 FROM [Sequence] UNION ALL SELECT 5 FROM" & _
" [Sequence] UNION ALL SELECT 6 FROM [Sequence]" & _
" UNION ALL SELECT 7 FROM [Sequence] UNION" & _
" ALL SELECT 8 FROM [Sequence] UNION ALL" & _
" SELECT 9 FROM [Sequence] ) AS Digits )" & _
" AS Units, ( SELECT nbr * 10 AS nbr FROM" & _
" ( SELECT 0 AS nbr FROM [Sequence] UNION" & _
" ALL SELECT 1 FROM [Sequence] UNION ALL" & _
" SELECT 2 FROM [Sequence] UNION ALL SELECT" & _
" 3 FROM [Sequence] UNION ALL SELECT 4 FROM" & _
" [Sequence] UNION ALL SELECT 5 FROM [Sequence]" & _
" UNION ALL SELECT 6 FROM [Sequence] UNION" & _
" ALL SELECT 7 FROM [Sequence] UNION ALL" & _
" SELECT 8 FROM [Sequence] UNION ALL SELECT" & _
" 9 FROM [Sequence] ) AS Digits ) AS Tens," & _
" ( SELECT nbr * 100 AS nbr FROM ( SELECT"
sql = sql & _
" 0 AS nbr FROM [Sequence] UNION ALL SELECT" & _
" 1 FROM [Sequence] UNION ALL SELECT 2 FROM" & _
" [Sequence] UNION ALL SELECT 3 FROM [Sequence]" & _
" UNION ALL SELECT 4 FROM [Sequence] UNION" & _
" ALL SELECT 5 FROM [Sequence] UNION ALL" & _
" SELECT 6 FROM [Sequence] UNION ALL SELECT" & _
" 7 FROM [Sequence] UNION ALL SELECT 8 FROM" & _
" [Sequence] UNION ALL SELECT 9 FROM [Sequence]" & _
" ) AS Digits ) AS Hundreds, ( SELECT nbr" & _
" * 1000 AS nbr FROM ( SELECT 0 AS nbr FROM" & _
" [Sequence] UNION ALL SELECT 1 FROM [Sequence]" & _
" UNION ALL SELECT 2 FROM [Sequence] UNION" & _
" ALL SELECT 3 FROM [Sequence] UNION ALL" & _
" SELECT 4 FROM [Sequence] UNION ALL SELECT" & _
" 5 FROM [Sequence] UNION ALL SELECT 6 FROM" & _
" [Sequence] UNION ALL SELECT 7 FROM [Sequence]" & _
" UNION ALL SELECT 8 FROM [Sequence] UNION" & _
" ALL SELECT 9 FROM [Sequence] ) AS Digits" & _
" ) AS Thousands, ( SELECT nbr * 10000 AS" & _
" nbr FROM ( SELECT 0 AS nbr FROM [Sequence]" & _
" UNION ALL SELECT 1 FROM [Sequence] UNION" & _
" ALL SELECT 2 FROM [Sequence] UNION ALL" & _
" SELECT 3 FROM [Sequence] UNION ALL SELECT"
sql = sql & _
" 4 FROM [Sequence] UNION ALL SELECT 5 FROM" & _
" [Sequence] UNION ALL SELECT 6 FROM [Sequence]" & _
" UNION ALL SELECT 7 FROM [Sequence] UNION" & _
" ALL SELECT 8 FROM [Sequence] UNION ALL" & _
" SELECT 9 FROM [Sequence] ) AS Digits )" & _
" AS TenThousands;"
.Execute sql

' Create test table
.Execute _
"CREATE TABLE Test1 (" & _
" aa_comp VARCHAR(100)" & _
" NOT NULL)"

' Create ', ' delimted data
.Execute _
"INSERT INTO Test1 (aa_comp)" & _
" VALUES ('03, 04, 05');"
.Execute _
"INSERT INTO Test1 (aa_comp)" & _
" VALUES ('03, 07, 05, 20');"
.Execute _
"INSERT INTO Test1 (aa_comp)" & _
" VALUES ('03, 06, 07');"

Dim rs
Set rs = .Execute( _
"SELECT T1.aa_comp, MID$(aa_comp, 1, MAX(S1.seq)" & _
" - 1) & ' and ' & MID$(aa_comp, MAX(S1.seq)" & _
" + 2) AS aa_comp_parsed FROM Test1 AS T1," & _
" [sequence] AS S1 WHERE S1.seq BETWEEN 1" & _
" AND 100 AND LEN(MID$(T1.aa_comp, S1.seq," & _
" 1)) > 0 AND MID$(T1.aa_comp, S1.seq, 1)" & _
" = ',' GROUP BY T1.aa_comp;")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
Click to expand...
 
S

schasteen

In a queary you could do something like:
Left([Description],InstrRev([Description],",") - 1) & " and " &
right(([Description],InstrRev([Description],","))

You could make this an update queary. If there is no comma, there will be
an error so you may want to put this in an if statement.

iif(Instr([Description],",")=0,[Description],Left([Description],InstrRev([Description],",")
- 1) & " and " & right(([Description],InstrRev([Description],",")))



[email protected] said:
Is there an easier way?

Jamie said:
The description is text field that has
expressions seperated by a comma. I would like to change the last comma
or if only comman in that string to the word "and".
Does anybody have any suggestions? I am more an access expert than a
vba expert.
Click to expand...

Here's a SQL solution (example) which uses a sequence auxilary table
(i.e. table of incrementing integers) to parse the text, find the last
comma and replace it:

Sub parmlist()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection

' Create 100K row 'sequence' auxilary table
.Execute _
"CREATE TABLE [Sequence] (seq INTEGER NOT" & _
" NULL CONSTRAINT pk__Sequence PRIMARY KEY);"
.Execute _
"INSERT INTO [Sequence] (seq) VALUES (-1)"

Dim sql
sql = sql & _
"INSERT INTO [Sequence] (seq) SELECT Units.nbr" & _
" + Tens.nbr + Hundreds.nbr + Thousands.nbr" & _
" + TenThousands.nbr AS seq FROM ( SELECT" & _
" nbr FROM ( SELECT 0 AS nbr FROM [Sequence]" & _
" UNION ALL SELECT 1 FROM [Sequence] UNION" & _
" ALL SELECT 2 FROM [Sequence] UNION ALL" & _
" SELECT 3 FROM [Sequence] UNION ALL SELECT" & _
" 4 FROM [Sequence] UNION ALL SELECT 5 FROM" & _
" [Sequence] UNION ALL SELECT 6 FROM [Sequence]" & _
" UNION ALL SELECT 7 FROM [Sequence] UNION" & _
" ALL SELECT 8 FROM [Sequence] UNION ALL" & _
" SELECT 9 FROM [Sequence] ) AS Digits )" & _
" AS Units, ( SELECT nbr * 10 AS nbr FROM" & _
" ( SELECT 0 AS nbr FROM [Sequence] UNION" & _
" ALL SELECT 1 FROM [Sequence] UNION ALL" & _
" SELECT 2 FROM [Sequence] UNION ALL SELECT" & _
" 3 FROM [Sequence] UNION ALL SELECT 4 FROM" & _
" [Sequence] UNION ALL SELECT 5 FROM [Sequence]" & _
" UNION ALL SELECT 6 FROM [Sequence] UNION" & _
" ALL SELECT 7 FROM [Sequence] UNION ALL" & _
" SELECT 8 FROM [Sequence] UNION ALL SELECT" & _
" 9 FROM [Sequence] ) AS Digits ) AS Tens," & _
" ( SELECT nbr * 100 AS nbr FROM ( SELECT"
sql = sql & _
" 0 AS nbr FROM [Sequence] UNION ALL SELECT" & _
" 1 FROM [Sequence] UNION ALL SELECT 2 FROM" & _
" [Sequence] UNION ALL SELECT 3 FROM [Sequence]" & _
" UNION ALL SELECT 4 FROM [Sequence] UNION" & _
" ALL SELECT 5 FROM [Sequence] UNION ALL" & _
" SELECT 6 FROM [Sequence] UNION ALL SELECT" & _
" 7 FROM [Sequence] UNION ALL SELECT 8 FROM" & _
" [Sequence] UNION ALL SELECT 9 FROM [Sequence]" & _
" ) AS Digits ) AS Hundreds, ( SELECT nbr" & _
" * 1000 AS nbr FROM ( SELECT 0 AS nbr FROM" & _
" [Sequence] UNION ALL SELECT 1 FROM [Sequence]" & _
" UNION ALL SELECT 2 FROM [Sequence] UNION" & _
" ALL SELECT 3 FROM [Sequence] UNION ALL" & _
" SELECT 4 FROM [Sequence] UNION ALL SELECT" & _
" 5 FROM [Sequence] UNION ALL SELECT 6 FROM" & _
" [Sequence] UNION ALL SELECT 7 FROM [Sequence]" & _
" UNION ALL SELECT 8 FROM [Sequence] UNION" & _
" ALL SELECT 9 FROM [Sequence] ) AS Digits" & _
" ) AS Thousands, ( SELECT nbr * 10000 AS" & _
" nbr FROM ( SELECT 0 AS nbr FROM [Sequence]" & _
" UNION ALL SELECT 1 FROM [Sequence] UNION" & _
" ALL SELECT 2 FROM [Sequence] UNION ALL" & _
" SELECT 3 FROM [Sequence] UNION ALL SELECT"
sql = sql & _
" 4 FROM [Sequence] UNION ALL SELECT 5 FROM" & _
" [Sequence] UNION ALL SELECT 6 FROM [Sequence]" & _
" UNION ALL SELECT 7 FROM [Sequence] UNION" & _
" ALL SELECT 8 FROM [Sequence] UNION ALL" & _
" SELECT 9 FROM [Sequence] ) AS Digits )" & _
" AS TenThousands;"
.Execute sql

' Create test table
.Execute _
"CREATE TABLE Test1 (" & _
" aa_comp VARCHAR(100)" & _
" NOT NULL)"

' Create ', ' delimted data
.Execute _
"INSERT INTO Test1 (aa_comp)" & _
" VALUES ('03, 04, 05');"
.Execute _
"INSERT INTO Test1 (aa_comp)" & _
" VALUES ('03, 07, 05, 20');"
.Execute _
"INSERT INTO Test1 (aa_comp)" & _
" VALUES ('03, 06, 07');"

Dim rs
Set rs = .Execute( _
"SELECT T1.aa_comp, MID$(aa_comp, 1, MAX(S1.seq)" & _
" - 1) & ' and ' & MID$(aa_comp, MAX(S1.seq)" & _
" + 2) AS aa_comp_parsed FROM Test1 AS T1," & _
" [sequence] AS S1 WHERE S1.seq BETWEEN 1" & _
" AND 100 AND LEN(MID$(T1.aa_comp, S1.seq," & _
" 1)) > 0 AND MID$(T1.aa_comp, S1.seq, 1)" & _
" = ',' GROUP BY T1.aa_comp;")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel does not accept text format! 0
Remove only the last comma 2
Pulling the town out of an address 5
Autonumber field in Excel that behaves the same as Access 0
Sort in striing? 9
Need help modifying code 0
trim a field 2
Multiple Commas 3

Top