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

Parse field in access query

I need to parse a text field in a query. The values I need are always after these values:

RD-
FL-
HX-
TU-

Then I need to stop the parse at either the end of the text value totally or at the next "-"

For example:

6061-T6-RD-0.567-.010  - I need the 0.567 only
4340-RD-0.4375 - I need the 0.4375 only

They don't always have the same amount of characters though as you can see above in my examples. So if there is a "-" after the value I need then it should stop the parsing at the "-". If there is no "-" after it then I just need the balance of the text values which will always end as shown above. BUT the values won't always be the same length in characters.  I hope this explains it clearly enough.

Thanks
Larry
0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 11
  • 9
1 Solution
 
Jack LeachCommented:
That brings back some memories... (15yrs in machining).

Because the stock shape can vary, you should have a table holding shape designations.  You can get away with stuffing them into one big constant in code, but a table is usually a better means to do it.  For querying, you'll have to call a custom VBA function for this.

Take the stock shape codes and concatenate them into a delimited string, being sure to put the delimited on the beginning and end as well as in between, so you'll have something like:

StockShapesList = ";RD;SQ;HX;"

Next, split the entire value of your field, loop until you find a stock shape, then grab the next value.  This is aircode, but try it out and tweak it around:

Const StockShapesList = ";RD;SQ;HX;"
Dim v As Variant
Dim s As String
Dim i As Integer
Dim PrimarySize As Double

v = Split(FieldValue, "-")  'split on the - into different parts

'loop each "-" delimited section of the string
For i = 0 to UBound(v)
  'if this part has a stock shape, get the next part as the primary size
  If Instr(1, StockShapesList, ";" & Trim(CStr(v(i)))) & ";") > 0 Then
    s = Trim(Cstr(v(i + 1)))
    PrimarySize = CDbl(s)
  End If
Next i

Open in new window



You can come up with a separate function to look up the stock shapes from the shape code table and concatenate them all into a string.  Best would be to hold this variable in a module-level scope so you can perform a self-healing test on it and thus only have the table call overhead be introduced once per application session (eg, if the module-level StockShapeList is a ZLS, call the table and build it).

Also, there's some little amount of consideration that can go into how to store these.  While I ended up using an AutoNumber ID to store as the actual unique ID, the codes are so common that I always had to parse them out anyway...  not that much fun.

Hope it helps...
-jack
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I'm 16yrs into machining and still going strong. :)

I like the idea of storing these variables in a table as opposed to hard-coding them into the function. That makes much better sense in case we add some, etc. I'll have to figure out how to do that but I first want to test out your code. I did notice on Line 12 that there is something wrong with it as it's red text in my vba window which tells me there's something wrong with the syntax. Not knowing much about VBA hinders my ability to see the problem. Can you take a look at line 12 again and see where the problem may be? Do I need to declare a string for my function when I create the function name? Example: GetDiameter(MyTextField As String)?
0
 
Jack LeachCommented:
This portion of the line:

Trim(CStr(v(i))))

Looks like it has an extra parentheses on the end.  Try removing one of them.

Technically, this function will get the primary size of any shape, so GetDiameter() may be misleading, because if you pass a flatbar code to it, it's going to give you something that's not the diameter (whereas semantically, I'd expect a GetDiameter function to give me an error if it couldn't determine something to be a diameter and only a diameter).  As such, you may want to consider alternative methods to determining which codes are for roundbar before you run them in this function, which is a whole different bag of worms: how do you determine what's round vs flat?  The code is in your material code, but it's not a great means to make a determination because of all the parsing required...

This is really quite a mess: handling stock naming in a database is one of the worse design projects I've come across.  What I ended up with was different tables for all of the various categories: Alloys, Grades, Shapes, Tempers, Process (HR/CR, Waterjet blanks, etc).  Because your alloy steels have different property sets than your carbon steels or red metals or speciality alloys, what you end up with is a whole bunch of tables to track various information, many of which are one to one against "master" tables, and the stock code itself (6061-T6-FL-0.500x0.500) becomes a calculation of the various parameters for a given requirement.  I needed to do it this way for weight calculation purposes (density/sq. foot, if you're tracking in feet), so then I could calculate weights on hand for inventory purposes, etc.  Then you get into stuff like ASTM codes for Military/Aerospace use, and you wind up with junction tables to link up those (and don't forget foreign designations!).

Anyway, didn't mean to get too sidetracked there, but I literally spent months working out different designs for how to handle this stuff, and I found that internally, it was easier and more effective in the long run to break all this information into separate tables, and save the parsing of codes only for consuming externally provided material codes (which is another pain, being that every company uses a slightly different nomenclature for them).

Good luck!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I appreciate your explanations but this data is coming from a SQL table from our ERP system. That is the syntax of the part description. So I need to figure out how to parse out the size from the description field. I will keep plugging away. Thank you again for your help.
0
 
Jack LeachCommented:
Sounds good.  The code snippet I gave you is really the key to doing all sorts of such parsing.  The key concept is:

- Determine a delimiter (in this case, "-")
- Use Split to split the string into parts (an array) based on the delimiter
- Loop the resultant array and look at each part

Cheers,
-jack
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
ok I tried to run the code but I'm getting an error saying "Type Mismatch" on line 14 (PrimarySize = CDbl(s)) of the code. Here's what I have for the code:

Function GetPrimarySize(FieldValue As String)
Const StockShapesList = ";RD;SQ;HX;FL;TU;"
Dim v As Variant
Dim s As String
Dim i As Integer
Dim PrimarySize As Double

v = Split(FieldValue, "-")  'split on the - into different parts

'loop each "-" delimited section of the string
For i = 0 To UBound(v)
  'if this part has a stock shape, get the next part as the primary size
  If InStr(1, StockShapesList, ";" & Trim(CStr(v(i))) & ";") > 0 Then
    s = Trim(CStr(v(i + 1)))
    PrimarySize = CDbl(s)
  End If
Next i
End Function

Open in new window

0
 
Jack LeachCommented:
We'll want to do some debugging, specifically to determine what the value of "s" is before trying to convert it to a Double.

Add a debug.print line in there and we'll take a look at the value and see what we need to fix:

Function GetPrimarySize(FieldValue As String)
Const StockShapesList = ";RD;SQ;HX;FL;TU;"
Dim v As Variant
Dim s As String
Dim i As Integer
Dim PrimarySize As Double

v = Split(FieldValue, "-")  'split on the - into different parts

'loop each "-" delimited section of the string
For i = 0 To UBound(v)
  'if this part has a stock shape, get the next part as the primary size
  If InStr(1, StockShapesList, ";" & Trim(CStr(v(i))) & ";") > 0 Then
    s = Trim(CStr(v(i + 1)))
    Debug.Print s   '<<<<<<<<<<<<<<<<<<<< ADD THIS
    PrimarySize = CDbl(s)
  End If
Next i
End Function

Open in new window


That will print the value of s to the immediate window (if you're unfamiliar with the immediate window, CTRL+G in the VBA Editor will show it, or pick it out of the View menu).

The error itself is stating that the value of "s" cannot be converted to a double precision number, so there's probably a alpha value in there or some sort.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
ok there are some that have alpha characters in it. Here's are a couple of the results from the immediate window:
I forgot there were some that had this structure for flat stock. So I guess it would have to end with a "-" or nothing at all OR an "X". I would only need the first size parameter in the example below.


0.250X1.750X7.000
0.250X1.500X5.250
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I found another that had a letter "G" right after the size.

0.119G

Would it be easier to say any character after instead of just a "-"?
0
 
Jack LeachCommented:
Ok, there's two ways we can do this... the first would be to further Split() that string on the "X" into yet another array (where you could then count the number of sizes it has, and get the 2nd, 3rd etc as well), or we can use InStr() to find the position of the "X" and get everything up to that.  We'll use the latter as it's a simpler method.

InStr() returns the position of one string within another.  Left() returns the leftmost portion of a string up to the length specified, so we'll use the two in conjunction with each other:

Function GetPrimarySize(FieldValue As String)
Const StockShapesList = ";RD;SQ;HX;FL;TU;"
Dim v As Variant
Dim s As String
Dim i As Integer
Dim PrimarySize As Double

v = Split(FieldValue, "-")  'split on the - into different parts

'loop each "-" delimited section of the string
For i = 0 To UBound(v)
  'if this part has a stock shape, get the next part as the primary size
  If InStr(1, StockShapesList, ";" & Trim(CStr(v(i))) & ";") > 0 Then
    s = Trim(CStr(v(i + 1)))
    Debug.Print s
    If InStr(1, s, "X") > 0 Then
      PrimarySize = Left(s, InStr(1, s, "X") - 1)
    Else
      PrimarySize = CDbl(s)
    End If
  End If
Next i
End Function

Open in new window


Try that...
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Still getting the Type Mismatch in the same line of code. And when I look in the immediate window I see the same ones with the "X" and now the "G" after the size.
0
 
Jack LeachCommented:
Regarding "G"... we then have to enter another loop to determine if the characters are numeric or not.

Consider a separate function:

(again, untested aircode)
Public Function GetFirstNumericValue(ValueIn As String) As Double

  Dim i As Integer
  Dim c As String
  Dim ret As String

  'Len() gives the length of the string, loop all characters
  For i = 1 To Len(ValueIn)

    'Mid() gives the middle of a string at a specified start and length, get the current character)
    If IsNumeric(Mid(s, i, 1) OR Mid(s, i, 1) = "." Then
      'this is numeric or a decimal, add it to the return
      ret = ret & Mid(s, i, 1)
    Else
      'not numeric or a return, we're done
      Exit For
    End If

    'increment the counter
    i = i + 1

  Next i
  
  If Len(ret) > 0 Then
    GetFirstNumericValue = CDbl(ret)
  Else
    GetFirstNumericValue = 0
  End If

End Function

Open in new window


then, change your original function to something like this:

For i = 0 To UBound(v)
  'if this part has a stock shape, get the next part as the primary size
  If InStr(1, StockShapesList, ";" & Trim(CStr(v(i))) & ";") > 0 Then
    s = Trim(CStr(v(i + 1)))
    Debug.Print s
    PrimarySize = GetFirstNumericValue(s)
  End If
Next i

Open in new window


hth
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Getting the same Type Mismatch error on this line of code now:

If IsNumeric(Mid(s, i, 1) Or Mid(s, i, 1)) = "." Then

Open in new window

0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
There was no declaration for the "s" so I added it like this:

Dim s As Integer

Open in new window


Not sure if that should be an Integer or a String.
0
 
Jack LeachCommented:
Here, use this.  I just tested on my end and removed a few errors:

Function GetPrimarySize(FieldValue As String)
Const StockShapesList = ";RD;SQ;HX;FL;TU;"
Dim v As Variant
Dim s As String
Dim i As Integer
Dim PrimarySize As Double

v = Split(FieldValue, "-")  'split on the - into different parts

'loop each "-" delimited section of the string
For i = 0 To UBound(v)
  'if this part has a stock shape, get the next part as the primary size
  If InStr(1, StockShapesList, ";" & Trim(CStr(v(i))) & ";") > 0 Then
  
    s = Trim(CStr(v(i + 1)))
    Debug.Print s
    PrimarySize = GetFirstNumericValue(s)
    Exit For
    
  End If
Next i

GetPrimarySize = PrimarySize

End Function

                






Public Function GetFirstNumericValue(ValueIn As String) As Double

  Dim i As Integer
  Dim c As String
  Dim ret As String

  'Len() gives the length of the string, loop all characters
  For i = 1 To Len(ValueIn)

    'Mid() gives the middle of a string at a specified start and length, get the current character)
    If (IsNumeric(Mid(ValueIn, i, 1))) Or (Mid(ValueIn, i, 1) = ".") Then
      'this is numeric or a decimal, add it to the return
      ret = ret & Mid(ValueIn, i, 1)
    Else
      'not numeric or a return, we're done
      Exit For
    End If

  Next i
  
  If Len(ret) > 0 Then
    GetFirstNumericValue = CDbl(ret)
  Else
    GetFirstNumericValue = 0
  End If

End Function

Open in new window


That ought to do...
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Works like a charm now! Perfect! Thank you very much for your help! I greatly appreciate it!!
0
 
Jack LeachCommented:
Glad to help.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Actually I am getting another error now when I test it out for all 4000 records. I'm getting "subscript out of range" for this line of code in the GetPrimarySize function:

s = Trim(CStr(v(i + 1)))

Open in new window

0
 
Jack LeachCommented:
Sounds like you have a record where there's nothing after the shape designation.

Add a Debug.Print line to the code and see what the value is that it's trying to work on:

Function GetPrimarySize(FieldValue As String)
Const StockShapesList = ";RD;SQ;HX;FL;TU;"
Dim v As Variant
Dim s As String
Dim i As Integer
Dim PrimarySize As Double

Debug.Print FieldValue '<<<<<<< ADD THIS

v = Split(FieldValue, "-")  'split on the - into different parts

'loop each "-" delimited section of the string
For i = 0 To UBound(v)
  'if this part has a stock shape, get the next part as the primary size
  If InStr(1, StockShapesList, ";" & Trim(CStr(v(i))) & ";") > 0 Then
  
    s = Trim(CStr(v(i + 1)))
etc etc...

Open in new window


Then you have the choice of handling it at the source (making sure the data is good before you try to process it), or handling it here in the function and returning 0 if the data's not good.

Here's a fix to handle here in the function (a good idea whether you handle it upstream later or not):

Function GetPrimarySize(FieldValue As String)
Const StockShapesList = ";RD;SQ;HX;FL;TU;"
Dim v As Variant
Dim s As String
Dim i As Integer
Dim PrimarySize As Double

v = Split(FieldValue, "-")  'split on the - into different parts

'loop each "-" delimited section of the string
For i = 0 To UBound(v)
  'if this part has a stock shape, get the next part as the primary size
  If InStr(1, StockShapesList, ";" & Trim(CStr(v(i))) & ";") > 0 Then
  
    ' ADD THIS
    If i + 1 > UBound(v) Then
      'nothing after the shape designation
      PrimarySize = 0
      Exit For
    End If

    s = Trim(CStr(v(i + 1)))
    Debug.Print s
    PrimarySize = GetFirstNumericValue(s)
    Exit For
    
  End If
Next i

GetPrimarySize = PrimarySize

End Function

Open in new window



hth
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That works now. Thank you again!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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