Thread: Query to remove -xx
03-14-13, 10:45 #1Registered User
- 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.
03-14-13, 13:01 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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) Else FixBadData = Item End If end functionI'd rather be riding on the Tiger 800 or the Norton