• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

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-.0010
-.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
0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 4
  • 4
1 Solution
 
Rey Obrero (Capricorn1)Commented:
place this in one of the column in your query

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
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Scratch my last comment. I put this on the wrong field. Let me try it again.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Rey Obrero (Capricorn1)Commented:
try this one


Mid(s, InStrRev(s, "-.") + 1, 5)
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
sorry

Mid([NameOfField], InStrRev([NameOfField], "-.") +1, 5)
0
 
Rey Obrero (Capricorn1)Commented:
use this

IIF (Instr([NameOfField], "-.")>0, Mid([NameOfField], InStrRev([NameOfField], "-.") +1, 5),0)
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That worked! Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now