Isolate Characters to Sort By

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

Guest

Hi,

I need to isolate 4 characters out of an account string, and then using
those 4 characters I would like my query to do an ORDER BY, yet show the
entire account string.

How would you isolate the "FDSA" from the following example?
11R546649720000.2005.TTWDWJE851300.RTFDSA0000.25203.1700500.000000.00

The length of each set, between the periods varies, however the number of
periods in an account code is a constant. Can they be used to help isolate
the text?

I was looking through the discussion page, but I don't really understand the
Right (?) expression or if that even applies. Also, I do not know VB. How
can I (or can I) do this using SQL? I would appreciate any insight on this
matter.

Thanks,
David
 
Hi,

I need to isolate 4 characters out of an account string, and then using
those 4 characters I would like my query to do an ORDER BY, yet show the
entire account string.

How would you isolate the "FDSA" from the following example?
11R546649720000.2005.TTWDWJE851300.RTFDSA0000.25203.1700500.000000.00
Click to expand...

Eeeuwww... that's an UGLY compound field. Any chance that this
emphatically non-atomic field could be split into its logical
components? If this were eight (or maybe more) fields, they could be
concatenated for display purposes much more easily than they can be
picked apart.
The length of each set, between the periods varies, however the number of
periods in an account code is a constant. Can they be used to help isolate
the text?

I was looking through the discussion page, but I don't really understand the
Right (?) expression or if that even applies. Also, I do not know VB. How
can I (or can I) do this using SQL? I would appreciate any insight on this
matter.
Click to expand...

Assuming - and I DON'T KNOW YOUR BUSINESS, so this assumption may be
flat wrong - that the piece you want to extract is in the fourth block
of fields, you could use some snarky nested Mid() and InStr()
functions; but a little VBA routine may be the best bet:

Private Function ExtractIt(Acctno As String) As String
Dim Tokens() As String
Tokens = Split(AcctNo, ".")
ExtractIt = Mid(Tokens(4), 3, 4)
End Function

See the online help for Split if this doesn't work first try, it's
untested.

John W. Vinson[MVP]
 
Hi, David.

See VBA Help (<Ctrl-G>, Help) on the Split, Mid, Left Right, and InStr
functions.

The new Split function returns an array of substrings that are delimited by
a character, in your case the period:

Dim MyArray(8) As String
MyArray = Split(MyString, ".")

Once you've parsed them, you can use the other functions to return the
string you're after, presuming it's at a consistent position. In the example
you gave, to return "FDSA", it would be:

Mid(MyArray(3),3,4)

Hope that helps.
Sprinks
 
Well apparently one of part of the organization outputs this information from
some customized program that they have, and what they export to the
organization, who I am creating the database for, is just copied and pasted
into excel.

At anyrate, you are right about the fact that what I want to extract is in
the 4th block.
I know SQL pretty well, but where do I put this code in at? Do I put it in
a Module?
If so, how do I tie the module to the query so that I can sort by the
extracted characters?

(Clearly, I am going to have to read up on VB.)

Thanks,
David
 
Hi, David.

Yes, place the code in a module. Declare the function Public to give it
scope from anywhere in the application, including from a query:

Public Function ExtractIt(Acctno As String) As String
....
End Function

In the query, create a calculated field, passing the full string field as a
parameter:

MyString: ExtractIt([MyFullStringField])

Hope that helps.
Sprinks
 
Back
Top