Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013

    Unanswered: Query to remove -xx

    I have to tables of information that I am trying to compare. One table has the part # with the - and the quantity but the other does not. So for example table 1 has abcde-25 while table2 only has abcde. I want to remove the -xx from any parts in table1 so I can compare the matching records from both tables. Using the Expr1: Left([BIN]![Part],Len([BIN]![Part])-2) does not work since it not every record has a -xx or is the same length. I need to single out anything with a -xx and remove just that portion of the record.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    fix your data and make life easy for yourself. accepting dodgy data casues these sort of issues.

    other wise I think you may have to write a function which does it for you using say split

    Private Function FixBadData(Item As String, Symbol As String) As String
    Dim FoundSymbolAt As Integer
    FoundSymbolAt = InStr(Item, Symbol)
    If FoundSymbolAt > 0 Then ' we have found the sepcified symbol
      FixBadData = Left(Item, FoundSymbolAt - 1)
      FixBadData = Item
    End If
    end function
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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