Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stripping off Trailing Commas (Access 2003)

    I'm drawing a blank.

    I have a field with text that I've been given by another department. The text has a trailing comma. There are other commas as separators within the field (it's a list of code and those separating commas need to be there for reporting).

    I think there is an easy way to remove that last comma, but I cannot remember what it would be. The strings vary in length - could be one code followed by a comma, or a string of four or five.

    Thanks for your help.

    Nancy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stripping off Trailing Commas (Access 2003)

    Create a query based on the table.
    Add only the text field to the query grid.
    If ALL records have a trailing comma in this field, you can omit the next step.
    Set the criteria for this field to <code>Like "*,"</code>
    Select Query | Update Query.
    Enter the following in the Update to line, substituting the actual field name:
    <code>
    Left([FieldName],Len([FieldName])-1)
    </code>
    Select Query | Run (or click the Run button on the toolbar)

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping off Trailing Commas (Access 2003)

    Something like:

    <code>
    Dim strWk As String
    strWk = [Field]
    If Right(strWk,1) = "," Then
    strWk = Left(strWk,Len(strWk)-1)
    End If
    </code>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping off Trailing Commas (Access 2003)

    Thank you!!!

    I was trying to do it with the Right function and it wasn't working.

    Nancy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •