[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 106
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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