Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

Replace last comma with "and"

Discussion in 'Information Technology' started by pdemarais@gmail.com, Jul 28, 2006.

  1. pdemarais@gmail.com

    pdemarais@gmail.com
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. OP
    Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    pdemarais@gmail.com wrote:
    > 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.


    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.

    --
     
  4. OP
    pdemarais@gmail.com

    pdemarais@gmail.com
    Expand Collapse
    Guest

    Is there an easier way?

    Jamie Collins wrote:
    > pdemarais@gmail.com wrote:
    > > 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.

    >
    > 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.
    >
    > --
     
  5. OP
    Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    pdemarais@gmail.com wrote:
    > Is there an easier way?


    Which part are you having difficulty with?

    It's ironic that in order to avoid a VBA (which I thought you wanted)
    solution and create scenario in which I can demonstrate a SQL solution,
    I need to post VBA to create the scenario :(

    Jamie.

    --
     
  6. OP
    schasteen

    schasteen
    Expand Collapse
    Guest

    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],",")))



    "pdemarais@gmail.com" wrote:

    > Is there an easier way?
    >
    > Jamie Collins wrote:
    > > pdemarais@gmail.com wrote:
    > > > 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.

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

    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page