Welcome to SPN

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

Sign Up Now!

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

    >
    >
     

Share This Page