Parse from Description

  • Thread starter Thread starter Matt Cromer
  • Start date Start date
M

Matt Cromer

I would like to expand this query to be B followed by any 2 numbers, for
example B01, B99, etc.

Mid([description], InStr([description], "B45") + 1)

Thank you
 
This expression will tell you if the next 2 characters after the first B in
the string are numbers.

IsNumeric(Mid([description], InStr([description], "B") + 1,2))

This expression will tell you if there is a B followed by two numbers
[Description] Like "*B##*"

But if you are trying to extract the B plus two numbers from the field, then
things are a bit more complex.

For example
ab2abc23 xys

would pass the second test, but would fail the first test and if you were
trying to extract the value you would end up with b2a using the Mid expression.

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

I did a poor job of explaining what I am trying to do. I would like to
create a field called Bin Nbr. Bin Nbr would look at description and return
B14 in this text - "24 IN ROTOR STR 11 B17". In many examples the Bin Nbr is
not the last 3 characters of description or I would just use Right.
Sometimes the Bin Nbr is in the middle or does not exist at all. If it does
not exist, I would like to return "Not Assigned".

Thank you

John Spencer said:
This expression will tell you if the next 2 characters after the first B in
the string are numbers.

IsNumeric(Mid([description], InStr([description], "B") + 1,2))

This expression will tell you if there is a B followed by two numbers
[Description] Like "*B##*"

But if you are trying to extract the B plus two numbers from the field, then
things are a bit more complex.

For example
ab2abc23 xys

would pass the second test, but would fail the first test and if you were
trying to extract the value you would end up with b2a using the Mid expression.

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

Matt said:
I would like to expand this query to be B followed by any 2 numbers, for
example B01, B99, etc.

Mid([description], InStr([description], "B45") + 1)

Thank you
Click to expand...
Click to expand...
 
That is most easily handled using a VBA function. You can use the following
UNTESTED function. Save the function in a VBA module and call it in your
query (or elsewhere) just as you would using the built in VBA funtions. Make
sure when you save the module you name it something besides fGetBin

fGetBin([Description])

Public Function fGetBin (strIn)
Dim vValues as Variant
Dim vReturn as Variant: vReturn = "Not Assigned"
Dim iLoop as Long

'Untested, needs error handler

If Len(strIn & "") = 0 Then
'Do nothing
Else
vValues = Split(strIn," ")
For iLoop = Lbound(vValues) to Ubound(vValues)
If vValues(iLoop) Like "B##" Then
vReturn = vValues(iLoop)
exit for
End If
Next iLoop
End If

fGetBin = vReturn

End Function

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

Matt said:
John -

I did a poor job of explaining what I am trying to do. I would like to
create a field called Bin Nbr. Bin Nbr would look at description and return
B14 in this text - "24 IN ROTOR STR 11 B17". In many examples the Bin Nbr is
not the last 3 characters of description or I would just use Right.
Sometimes the Bin Nbr is in the middle or does not exist at all. If it does
not exist, I would like to return "Not Assigned".

Thank you

John Spencer said:
This expression will tell you if the next 2 characters after the first B in
the string are numbers.

IsNumeric(Mid([description], InStr([description], "B") + 1,2))

This expression will tell you if there is a B followed by two numbers
[Description] Like "*B##*"

But if you are trying to extract the B plus two numbers from the field, then
things are a bit more complex.

For example
ab2abc23 xys

would pass the second test, but would fail the first test and if you were
trying to extract the value you would end up with b2a using the Mid expression.

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

Matt said:
I would like to expand this query to be B followed by any 2 numbers, for
example B01, B99, etc.

Mid([description], InStr([description], "B45") + 1)

Thank you
Click to expand...
Click to expand...
Click to expand...
 
John -

It took a little while to figure out how to save a custom function, but once
I did...it works GREAT! Thanks for the help.

John Spencer said:
That is most easily handled using a VBA function. You can use the following
UNTESTED function. Save the function in a VBA module and call it in your
query (or elsewhere) just as you would using the built in VBA funtions. Make
sure when you save the module you name it something besides fGetBin

fGetBin([Description])

Public Function fGetBin (strIn)
Dim vValues as Variant
Dim vReturn as Variant: vReturn = "Not Assigned"
Dim iLoop as Long

'Untested, needs error handler

If Len(strIn & "") = 0 Then
'Do nothing
Else
vValues = Split(strIn," ")
For iLoop = Lbound(vValues) to Ubound(vValues)
If vValues(iLoop) Like "B##" Then
vReturn = vValues(iLoop)
exit for
End If
Next iLoop
End If

fGetBin = vReturn

End Function

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

Matt said:
John -

I did a poor job of explaining what I am trying to do. I would like to
create a field called Bin Nbr. Bin Nbr would look at description and return
B14 in this text - "24 IN ROTOR STR 11 B17". In many examples the Bin Nbr is
not the last 3 characters of description or I would just use Right.
Sometimes the Bin Nbr is in the middle or does not exist at all. If it does
not exist, I would like to return "Not Assigned".

Thank you

John Spencer said:
This expression will tell you if the next 2 characters after the first B in
the string are numbers.

IsNumeric(Mid([description], InStr([description], "B") + 1,2))

This expression will tell you if there is a B followed by two numbers
[Description] Like "*B##*"

But if you are trying to extract the B plus two numbers from the field, then
things are a bit more complex.

For example
ab2abc23 xys

would pass the second test, but would fail the first test and if you were
trying to extract the value you would end up with b2a using the Mid expression.

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

Matt Cromer wrote:
I would like to expand this query to be B followed by any 2 numbers, for
example B01, B99, etc.

Mid([description], InStr([description], "B45") + 1)

Thank you
Click to expand...
Click to expand...
Click to expand...
 
Back
Top