Solved

Parse a text field in access query

Posted on 2016-10-25
8
26 Views
Last Modified: 2016-10-25
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
Comment
Question by:Lawrence Salvucci
  • 4
  • 4
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41859145
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41859155
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41859159
Scratch my last comment. I put this on the wrong field. Let me try it again.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41859164
try this one


Mid(s, InStrRev(s, "-.") + 1, 5)
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41859165
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41859166
sorry

Mid([NameOfField], InStrRev([NameOfField], "-.") +1, 5)
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 41859170
use this

IIF (Instr([NameOfField], "-.")>0, Mid([NameOfField], InStrRev([NameOfField], "-.") +1, 5),0)
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41859174
That worked! Thank you very much!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now