Link to home
Start Free TrialLog in
Avatar of marellano
marellano

asked on

Extracting string characters with different combination using MS Access 2007

I have a field in a table that has narritive information.  The field may or may not contain the characters that I want to be able to extract.  The characters I want to extract will contain the letter "L" somewhere in the field, followed by a series of number combinations:

For example:
"TESTL2TEST"                        = L2
"L6 TEST"                               = L6
"TEST L99/L4"                        =L4
 "TESTTEST L2.2"                   =L2.2
"TEST L16.6 TEST L99"          = L16.6
"L99/L72.3 TEST"                   = L72.3

The last example has one major exception.  If the field contains the charaters "L99", I do NOT want the logic to the "L99" but instead select the "L72.2".  So if the logic finds the characters "L99", I need it to have to check and see if another combination exists that has an "L" followed by 1-2 digits OR by 1-2 digits with a "." (decimal) and another digit.

I'm having to do this in MS Access so a sql syntax is preferred. I'm also open to creating a module in MS Visual Basic.  

Hope that makes sense!!

thanks for your expert help in advance!

marellano
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Select InPutField, fnReturn(InPutField) As OutField From Table1

Function fnReturn(strTemp) As String
' this function will do all for you.
' this method will allow you easily to revise and or improve your logic later
End Function

brb.

Mikr
Function fnReturn(strTemp) As String

Dim strResponse as string

'check to see if there is L99 in the string
IF Instr(strTemp,"L99")>0 then

 'remoe L99 and search for another occurance of L??
 strTemp = Replace(strTemp,"L99,"")
 strResponse =  fnResponse(strTemp)

 'check to see if there were another occurance:
  If Instr(strResponse,"L") > 0 then
      ' do nothing and accept strResponse
  else
    ' accept L99 as response if there were no L99
    ' if acceptable then remove ' from the following line:
    ' strResponse = "L99"
  end if

Else
 strResponse =  fnResponse(strTemp)
End If

    fnReturn = strResponse

End Function

Function

'----------------
Feunction fnResponse(str_Temp) as String
' brb
End Function

Mike
Function fnResponse(str_Temp) as String

Dim strTemp As String
Dim strTem2 As String
Dim strTemFinal As String
Dim i as integer

' in this function, str_Temp has no L99 occurance
' but it may have L followed by some numbers
' or my have no Ls at all

'check to see if it has L at all
if Instr(str_Temp,"L")>0 Then
' grab the first occurance of L
strTemp = Mid(str_Temp,Instr(str_Te,"L") +1)

For i = 1 to Len(strTem)
  if is numeric(Mid(strTem,i,1)) or Mid(strTem,i,1) = "." Then
  strTem2 = strTem2 & Mid(strTem,i,1)
Next i
  strTemFinal  = "L" & strTem2
Else
  strTemFinal  = ""
End If

fnResponse= strTemFinal  
End Function

Mike
Avatar of marellano
marellano

ASKER

eghtebas,
Thx for your prompt response...i will try your solution shortly.
eghtebas,

I tried compiling both modules but I'm getting a Syntax error on the following lines:

fnResponse Function Error:
  if is numeric(Mid(strTem,i,1)) or Mid(strTem,i,1) = "." Then

fnReturn Funtion Error:
 strTemp = Replace(strTemp,"L99,"")
brb in 45 min
"End If: was missing, try:

Function fnResponse(str_Temp) as String

Dim strTemp As String
Dim strTem2 As String
Dim strTemFinal As String
Dim i as integer
if Instr(str_Temp,"L")>0 Then
  strTemp = Mid(str_Temp,Instr(str_Te,"L") +1)
For i = 1 to Len(strTem)
  if Mid(strTem,i,1) = "." Then
     strTem2 = strTem2 & Mid(strTem,i,1)
  elseif is numeric(Mid(strTem,i,1)) then
     strTem2 = strTem2 & Mid(strTem,i,1)
  end if '****** was missing
Next i
  strTemFinal  = "L" & strTem2
Else
  strTemFinal  = ""
End If

fnResponse= strTemFinal  
End Function
The other function is also revised:
  a " was missing--------------------v
 strTemp = Replace(strTemp,"L99","")

add " as shown above.

Mike
hi Mike,

the fnResponse function is still giving me a text error at the following line:

  elseif is numeric(Mid(strTem,i,1)) then

Also, just to let you know, I'm saving and compiling both custom functions separate. Is that correct?

marellano
try:

Function fnResponse(str_Temp) as String

Dim strTemp As String
Dim strTem2 As String
Dim strTemFinal As String
Dim TempVal As Varian '<-- because we don't know it will be nemeric or string

Dim i as integer
if Instr(str_Temp,"L")>0 Then
  strTemp = Mid(str_Temp,Instr(str_Te,"L") +1)
For i = 1 to Len(strTem)
  TempVal = Mid(strTem,i,1)
  if  TepVal  = "." Then
     strTem2 = strTem2 & TempVal
  elseif is numeric(TepVal) then
     strTem2 = strTem2 & TempVal
  end if
Next i
  strTemFinal  = "L" & strTem2
Else
  strTemFinal  = ""
End If
correction...
                                   v-- t was missing
Dim TempVal As Variant
Last revision, I hope:

Function fnResponse(str_Temp) as String

Dim strTemp As String
Dim strTem2 As String
Dim strTemFinal As String
Dim TempVal As Varian '<-- because we don't know it will be nemeric or string

Dim i as integer
if Instr(str_Temp,"L")>0 Then
  strTemp = Mid(str_Temp,Instr(str_Te,"L") +1)
For i = 1 to Len(strTem)
  TempVal = Mid(strTem,i,1)
  if  TepVal  = "." Then
     strTem2 = strTem2 & TempVal
  elseif is numeric(TepVal) then
     strTem2 = strTem2 & TempVal
  else
    exit for   'to handle only first "L" related part
  end if
Next i
  strTemFinal  = "L" & strTem2
Else
  strTemFinal  = ""
End If
Hi mike,

I changed the text on the fourth line from "Varian" to "Variant", then compiled but got another syntax error on the line:

  elseif is numeric(TepVal) then    (Then I also changed "TepVal" to "TempVal", assuming it was an type)

I compiled a second time and still get a syntax error.

any other other suggestions?

maybe it should read:

Elseif IsNumeric(TepVal) then

not

elseif is numeric(TepVal) then      
Thx Mike...it looks as if we are almost there, I did get a different compile error stating "Expected End Function":

Function fnResponse(str_Temp) As String

Dim strTemp As String
Dim strTem2 As String
Dim strTemFinal As String
Dim TempVal As Variant '<-- because we don't know it will be nemeric or string

Dim i As Integer
If InStr(str_Temp, "L") > 0 Then
  strTemp = Mid(str_Temp, InStr(str_Te, "L") + 1)
For i = 1 To Len(strTem)
  TempVal = Mid(strTem, i, 1)
  If TepVal = "." Then
     strTem2 = strTem2 & TempVal
  ElseIf IsNumeric(TepmVal) Then
     strTem2 = strTem2 & TempVal
  End If
Next i
  strTemFinal = "L" & strTem2
Else
  strTemFinal = ""
End If
also, is the syntax on this line suppose to be "TepVal" or "TempVal"?

ElseIf IsNumeric(TepVal) Then

Just checking
I meant to write TempVal everywhere. If you have TepVal somewhere change it to TempVal please.

Also, it is a good idea to step though the code to see if it is performing according to the logic discussed in the body of my original code. If not debug it.

Mike
i made the syntax corrections but would you know why I'm still getting an "Expected End Function" error??

I believe that's the only thing missing.
Add "End Function" at the end as it asks for.
use:

Function fnResponse(str_Temp) As String

Dim strTemp As String
Dim strTem2 As String
Dim strTemFinal As String
Dim TempVal As Variant '<-- because we don't know it will be nemeric or string

Dim i As Integer
If InStr(str_Temp, "L") > 0 Then         'v--- had typo
  strTemp = Mid(str_Temp, InStr(str_Temp, "L") + 1)
For i = 1 To Len(strTem)
  TempVal = Mid(strTem, i, 1)
  If TempVal = "." Then
     strTem2 = strTem2 & TempVal
  ElseIf IsNumeric(TepmVal) Then
     strTem2 = strTem2 & TempVal
  End If
Next i
  strTemFinal = "L" & strTem2
Else
  strTemFinal = ""
End If

End Function
ok, that seemed to work but...unfortunately, it's now giving me an "Argument not optional"
compile error in the FNRETURN function line:

strTemp = Replace(strTemp, "L99,""")

The debug highlights the word "Replace" as the problem.

So far this is what I have:
==================
FNRETURN FUNCTION
==================
Function fnReturn(strTemp) As String

Dim strResponse As String

'check to see if there is L99 in the string
If InStr(strTemp, "L99") > 0 Then

 'remove L99 and search for another occurance of L??
 strTemp = Replace(strTemp, "L99,""")
 strResponse = fnResponse(strTemp)

 'check to see if there were another occurance:
  If InStr(strResponse, "L") > 0 Then
      ' do nothing and accept strResponse
  Else
    ' accept L99 as response if there were no L99
    ' if acceptable then remove ' from the following line:
    strResponse = "L99"
  End If

Else
 strResponse = fnResponse(strTemp)
End If

    fnReturn = strResponse

End Function

Function

'----------------
Feunction fnResponse(str_Temp) as String
' brb
End Function

==================
FNRESPONSE FUNCTION
==================

Function fnResponse(str_Temp) As String

Dim strTemp As String
Dim strTem2 As String
Dim strTemFinal As String
Dim TempVal As Variant '<-- because we don't know it will be nemeric or string

Dim i As Integer
If InStr(str_Temp, "L") > 0 Then
  strTemp = Mid(str_Temp, InStr(str_Te, "L") + 1)
For i = 1 To Len(strTem)
  TempVal = Mid(strTem, i, 1)
  If TepVal = "." Then
     strTem2 = strTem2 & TempVal
  ElseIf IsNumeric(TempVal) Then
     strTem2 = strTem2 & TempVal
  End If
Next i
  strTemFinal = "L" & strTem2
Else
  strTemFinal = ""
End If
End Function

this:

strTemp = Replace(strTemp, "L99,""")

Should read:
        ' replace this-----------------v     v--- with this
strTemp = Replace(strTemp, "L99","")

looking for more.
in "Function fnResponse(str_Temp) As String"

You never added

" exit for   'to handle only first "L" related part"

to it.

Also there are many other parts you have missed from my posts. You need back track and reread to correct them. If you are just trying copy them hoping it somehow would work, it will not. You need to roll your sleeves and understand the code and make it work. I am just helping you and are big part of the solution. It is your solution.

Otherwise, we can stay on this questions for the next two months without any workable solution.

Regards,

Mike
correction...

I am just helping you and you are a big part of the solution. It is your solution.
Hi Mike,

I appreciate the help.  Unfortunately, I'm very new to VB and I'm not familiar enough with it to be able to look at the code and troubleshoot it. However, I feel I did as much as I'm able to in identifying where there seemed to be typos or misreferences.    

I'll look back at what I might missed and test the code to see if I can make work.

Thanks again for your time.    
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Mike.  Your point is very well taken.  Let me absorb the logic a little more so I can ask what I don't undertand.  VB is a little overwhelming at first but I have a more time right now then I did yesterday to disect the code a bit more.
brb.
marellano  
I am exactly in the same place where you are with a question I have asked another expert. I don't know how it works but I am reading and re-reading and asking questions myself until I get it.

Mike
I managed to clean the code and do a test run.  I noticed that the function is only returning two result combinations; "L", "L99".  It seems that when the fnReturn function runs and attempts to search for another occurance of L, it does not ID the numeric correct values that follow.  Here's what I'm getting back:

"TESTL2TEST"                        Returns only "L" instead of "L2"
"444444444 L2.2"                   Returns only "L444444444" instead of "L2.2"
"22222 L99"                            Returns "L99"  (correct)
"TEST L99"                             Returns "L99"  (correct)
"L99/L72.3 TEST"                   Returns only "L" instead of "L72.3"


In studying your code, it seems to me the fnReturn function calls the fnResponse in the following line:

strResponse = fnResponse(strTemp)

fnResponse then looks at strTemp to flow through and ID the numeric values. Is that what the following code is suppose to be doing and is it correct???:

strTemp = Mid(str_Temp, InStr(str_Te, "L") + 1)
 
For i = 1 To Len(strTemp)
  TempVal = Mid(strTemp, i, 1)
  If TepVal = "." Then
     strTem2 = strTem2 & TempVal
  ElseIf IsNumeric(TempVal) Then
     strTem2 = strTem2 & TempVal

Please let me know if you have any thoughts on this.  

thx,
marellano

=======================
COMPILED CODE:
=======================
Function fnReturn(strTemp) As String

Dim strResponse As String

'check to see if there is L99 in the string
If InStr(strTemp, "L99") > 0 Then

 'remove L99 and search for another occurance of L??
 strTemp = Replace(strTemp, "L99", "")
 strResponse = fnResponse(strTemp)

 'check to see if there were another occurance:
  If InStr(strResponse, "L") > 0 Then
      ' do nothing and accept strResponse
  Else
    'accept L99 as response if there were no L99
    ' if acceptable then remove ' from the following line:
    strResponse = "L99"
  End If

Else
 strResponse = fnResponse(strTemp)
End If

    fnReturn = strResponse
End Function

Function fnResponse(str_Temp) As String

Dim strTemp As String
Dim strTem2 As String
Dim strTemFinal As String
Dim TempVal As Variant '<-- because we don't know it will be nemeric or string

Dim i As Integer
If InStr(str_Temp, "L") > 0 Then
  strTemp = Mid(str_Temp, InStr(str_Te, "L") + 1)
 
For i = 1 To Len(strTemp)
  TempVal = Mid(strTemp, i, 1)
  If TepVal = "." Then
     strTem2 = strTem2 & TempVal
  ElseIf IsNumeric(TempVal) Then
     strTem2 = strTem2 & TempVal
  Else
    Exit For   'to handle only first "L" related part
  End If
Next i
  strTemFinal = "L" & strTem2
Else
  strTemFinal = ""
End If

fnResponse = strTemFinal
End Function
correction:
"I noticed that the function is only returning two result combinations; "L", "L99"."

to

I notice that the function is only returning "L99" correctly but not the other "L" combinations
marellano,

No you are in the driver seat. The original code includes some comments. They explain what the code is supposed to do. Step through the code and docummnet where it fails and cannot do as it is expected.

By going a process like this, you will be able to add many other logics you may come accross and expected to adjust your code.

After you step through it, tell me where it fails and describe what it is supposed to do but not doing. Then I will be able to work with you to iron them out.

Regards,

Mike
Today my mental RAM is overloaded. Tomorrow, I will go over the code as well to help you out.
Hi Mike,
Just wated to let you know that I found a couple of minor typos, which one was causing the problem with the numbers after the "L".  I tested the module and it seems to be working right.

Dim i As Integer
If InStr(str_Temp, "L") > 0 Then
  strTemp = Mid(str_Temp, InStr(str_Temp, "L") + 1) '------changed str_Te to str_Temp

Thanks for your help.  I really appreciated the learning experience.

Have a great day!!
marellano
Again, thanks for all your time and teaching!
My pleasure.

Have a grat weekend.

Mike