Lawrence Salvucci
asked on
Parse a text field in access query
I need to parse a text field and pull out a certain string of characters that aren't always in the same location in the text field.
The string will always start with a - followed by a decimal point and then either 3, 4, or 5 numeric values. If it doesn't follow this syntax then I just want it to return a 0. Here are some examples:
-.0002 Is what I want to parse from this example: 416SS-RD-0.2503-.0002
-.0010 Is what I want to parse from this example: INCONEL-625-RD-0.2661-.001 0
-.0004 Is what I want to parse from this example: 17-4PH-RD-0.3752-.0004-AR. 0002
-.0004 Is what I want to parse from this example: 304SS-SPR-TEMP-RD-0.0317-. 0004X73
The string will always start with a - followed by a decimal point and then either 3, 4, or 5 numeric values. If it doesn't follow this syntax then I just want it to return a 0. Here are some examples:
-.0002 Is what I want to parse from this example: 416SS-RD-0.2503-.0002
-.0010 Is what I want to parse from this example: INCONEL-625-RD-0.2661-.001
-.0004 Is what I want to parse from this example: 17-4PH-RD-0.3752-.0004-AR.
-.0004 Is what I want to parse from this example: 304SS-SPR-TEMP-RD-0.0317-.
ASKER
It seems to be working for some and not for others. I put it in my query instead of using a function.
This one it works fine for and returns .0020:
420SS-RD-0.241-.0020X1.185
This one does not when I should get .0010:
TI-ELI-RD-0.3755-.0010
This one it works fine for and returns .0020:
420SS-RD-0.241-.0020X1.185
This one does not when I should get .0010:
TI-ELI-RD-0.3755-.0010
ASKER
Scratch my last comment. I put this on the wrong field. Let me try it again.
try this one
Mid(s, InStrRev(s, "-.") + 1, 5)
Mid(s, InStrRev(s, "-.") + 1, 5)
ASKER
Ok it's working fine now that I have it using the correct field. One question...instead of getting the #Error when it can't find that string at all how can I have it just return 0 instead?
This string should return 0 since it won't find that string in it. There are others similar to this so if it can't find that string can it just return 0?
Item: F020532 Line: 001
This string should return 0 since it won't find that string in it. There are others similar to this so if it can't find that string can it just return 0?
Item: F020532 Line: 001
sorry
Mid([NameOfField], InStrRev([NameOfField], "-.") +1, 5)
Mid([NameOfField], InStrRev([NameOfField], "-.") +1, 5)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked! Thank you very much!
Mid([NameOfField], InStrRev([NameOfField], "."), 5)
or use a function
Function ParseInfo(vStr as string)
dim strVal as string
strVal=Mid(vStr, InStrRev(vStr, "."), 5)
ParseInfo=strVal
end function