Strain Your brain with me (text Length)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a query that will automatically return the string length of the last
few characters of a varable length Text string based on where the last space
Character is in the string.

I am trying to group and count division codes based on our E-mail List.
Each string is varable in the nubmer of spaces and length. The only near
constant is the division code is the last 2 to ?? characters after a space at
the end of the string.

Since this list changeds daily it is too manually intensive to do a Copy,
Paset, Find and replace "*_" with "", just to capture the codes.
 
SELECT "ABC 123" AS TestData, Mid$([TestData],InStrRev([TestData]," ")+1) AS
Expr1
FROM SomeTable"

The above SQL returns the value "123".

InStrRev is one of the new string handling functions introduced in VBA 6
(Access 2000). It won't work at all in Access 97 or earlier. It works in a
query in Access 2003, I'm not sure about Access 2000 and 2002. If you get
undefined function errors trying to use the function in Access 2000 or 2002,
you might need to use a custom VBA function as a wrapper.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
You didn't provide any examples of the division codes, so you might have to
tweak this a little. Here is a bit of code to get the division codes after
the last space.

Start by creating a standard module and pasting in the following code (or
add it to a module):

Public Function GetDivCode(txtStr As String) As String
GetDivCode = Right(txtStr, Len(txtStr) - InStrRev(txtStr, " "))
End Function

Now the query. I used 'txtDivisionCode' as the field that has the division
codes.
This is the test query I used:

SELECT tstTable.txtDivisionCode, GetDivCode([txtdivisionCode]) AS DivCode
FROM tstTable
WHERE tstTable.txtDivisionCode Is Not Null;

Change txtDivisionCode to the name of your field. If you don't use the
criteria 'Is Not Null' and the division code is null, the result will be
#Error.

HTH
Steve
 
Here is a version of the code for Access97 (and up).

Public Function GetDivCode(txtStr As String) As String
Dim Has_Space As Boolean
Dim Posn As Integer, i As Integer

' assume no spaces
GetDivCode = txtStr

Has_Space = False
Posn = 0
' loop to find the last space
For i = 1 To Len(Trim(txtStr))
If Mid(txtStr, i, 1) = " " Then
Has_Space = True
Posn = i
End If
Next
If Has_Space = True Then
GetDivCode = Right(txtStr, Len(txtStr) - Posn)
End If
End Function

The query remains the same:

SELECT tstTable.txtDivisionCode, GetDivCode([txtdivisionCode]) AS DivCode
FROM tstTable WHERE tstTable.txtDivisionCode Is Not Null;

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


SteveS said:
You didn't provide any examples of the division codes, so you might have to
tweak this a little. Here is a bit of code to get the division codes after
the last space.

Start by creating a standard module and pasting in the following code (or
add it to a module):

Public Function GetDivCode(txtStr As String) As String
GetDivCode = Right(txtStr, Len(txtStr) - InStrRev(txtStr, " "))
End Function

Now the query. I used 'txtDivisionCode' as the field that has the division
codes.
This is the test query I used:

SELECT tstTable.txtDivisionCode, GetDivCode([txtdivisionCode]) AS DivCode
FROM tstTable
WHERE tstTable.txtDivisionCode Is Not Null;

Change txtDivisionCode to the name of your field. If you don't use the
criteria 'Is Not Null' and the division code is null, the result will be
#Error.

HTH
Steve
--------------------------------
"Nunc Tutus Exitus Computarus."
(It's Now Safe To Turn Off Your Computer.)


MacALF said:
I need a query that will automatically return the string length of the last
few characters of a varable length Text string based on where the last space
Character is in the string.

I am trying to group and count division codes based on our E-mail List.
Each string is varable in the nubmer of spaces and length. The only near
constant is the division code is the last 2 to ?? characters after a space at
the end of the string.

Since this list changeds daily it is too manually intensive to do a Copy,
Paset, Find and replace "*_" with "", just to capture the codes.
Click to expand...
Click to expand...
 
Len (strString) - InStrRev (strString, " ")



I need a query that will automatically return the string length of the last
few characters of a varable length Text string based on where the last space
Character is in the string.

I am trying to group and count division codes based on our E-mail List.
Each string is varable in the nubmer of spaces and length. The only near
constant is the division code is the last 2 to ?? characters after a space at
the end of the string.

Since this list changeds daily it is too manually intensive to do a Copy,
Paset, Find and replace "*_" with "", just to capture the codes.
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top