how extract part of the begin text.

  • Thread starter Thread starter ldiaz
  • Start date Start date
L

ldiaz

Hello, can you help to extract the text before "--",

Example:
Night--P-002 =Night
Day--P-002 =Day



Thanks
LD
 
Try the following statement, which assumes a variable (which I've called
strTimePeriod for the sake of creating an example) that contains strings
like "Night--P-002" or "Day--P-002"

Left(strTimePeriod, Instr(strTimePeriod, "--")-1)
 
Hi Lorenzo,

In a query, you can use something like this (watch for word wrap):

Stuff = Left$([FieldName], IIf(InStr([FieldName], "--") - 1 > 0,
InStr([FieldName], "--") - 1, 0))

where FieldName is the name of the field. Make the appropriate substitution.

In code, you can use something like this (two variations shown):

Sub TestStuff()

Debug.Print Stuff("Night--P-002")
Debug.Print Stuff("Day--P-002")

'--Does not match search pattern
Debug.Print Stuff("Night-P-002")

Debug.Print
Debug.Print Stuff2("Night--P-002")
Debug.Print Stuff2("Day--P-002")

'--Does not match search pattern
Debug.Print Stuff2("Night-P-002")

End Sub

Function Stuff(strVal As String) As String

Stuff = Left$(strVal, IIf(InStr(strVal, "--") - 1 > 0, _
InStr(strVal, "--") - 1, 0))

End Function

Function Stuff2(strVal As String) As String
Dim intPos As Integer

intPos = InStr(strVal, "--") - 1

Select Case intPos
Case Is > 0
Stuff2 = Left$(strVal, (InStr(strVal, "--") - 1))
Case Else
Stuff2 = "Search string not found."
End Select

End Function


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Thank you George,
I'm having an error when the field is null, but I think I know how to handle
this..

anyway thank you for your help
--
Lorenzo Díaz
Cad Technician


George said:
Try the following statement, which assumes a variable (which I've called
strTimePeriod for the sake of creating an example) that contains strings
like "Night--P-002" or "Day--P-002"

Left(strTimePeriod, Instr(strTimePeriod, "--")-1)
Click to expand...
 
Try these versions to account for the possibility of nulls:

Sub TestStuff()
Debug.Print Stuff()
Debug.Print Stuff("Night--P-002")
Debug.Print Stuff("Day--P-002")

'--Does not match search pattern
Debug.Print Stuff("Night-P-002")

Debug.Print
Debug.Print Stuff()
Debug.Print Stuff2("Night--P-002")
Debug.Print Stuff2("Day--P-002")

'--Does not match search pattern
Debug.Print Stuff2("Night-P-002")

End Sub

Function Stuff(Optional varVal As Variant) As String

If Not IsMissing(varVal) Then
Stuff = Left$(varVal, IIf(InStr(varVal, "--") - 1 > 0, _
InStr(varVal, "--") - 1, 0))
Else
Stuff = "Search string not found."
End If

End Function

Function Stuff2(Optional varVal As Variant) As String
Dim intPos As Integer

intPos = InStr(varVal, "--") - 1

Select Case intPos
Case Is > 0
Stuff2 = Left$(varVal, (InStr(varVal, "--") - 1))
Case Else
Stuff2 = "Search string not found."
End Select

End Function


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
 
The easy way to handle nulls is to add a zero-length string to the Instr
functions argument.

Left(strTimePeriod, Instr(strTimePeriod & "","--")-1)

OR if you want the entire string if the strTimePeriod does not have the
"--" delimiter

Left(strTimePeriod, Instr(strTimePeriod & "--","--")-1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top