LString - RString

  • Thread starter Thread starter Micah Chaney
  • Start date Start date
M

Micah Chaney

Hi Group...How do you use the LString and RString functions in a Query? I
have a series of concatenated fields that I'm trying to split up. I can
figure out how to do LString and so many characters, but what if I needed to
do LString to a certain character?

Example:

I can have in one field the following string...
| 12 |a 13553 |b 14 |c 156 |d 16 |e...

That way I can say in...
Field A: Find "|" and go Right to the next "|" and capture the value, giving
this field a value of 12.
Field B: Find "|a" and go Right to the next "|" and capture the value,
giving this field a value of 13553.
Field C: Find "|b" and go Right to the next "|" and capture the value,
giving this field a value of 14.
Field D: Find "|c" and go Right to the next "|" and capture the value,
giving this field a value of 156.

See what I'm saying? How do I do that?

The Access NewGroup/Forum has always, ALWAYS been the most helpful, I hope
you guys can help me with this issue as well. Thanks in Advance.
 
Sorry, I should know better by now...I'm on Access and Windows 2003. Thanks
again in Advance.
 
How much data are you dealing with? It might be easier to export the
data to excel, use text to columns, and reimport back to access.

Would splitting the string always create a fixed length array?
 
Thanks for the quick response. Splitting will NOT necessarily yield a fixed
length. A little more detail on the project.
I'm working with an Outlook Form. Outlook only allows for a selected few
fields to be linked with Access. However, I need to utilize more than the
preset number of fields to transport data from Outlook to Access. So what
I'm doing is creating a plethera of Custom Fields within Outlook, and
concatenating them together in the fields that can be linked with Access.
So for example:

I have these fields in Outlook: [Customer Name] [Order Number] [Order Date]
[Total Purchase]. OK? Now I concatenate those fields into one field that
can be linked with Access as follows:

"| "&[Customer Name]&" |a "&[Order Number]&" |b "&[Order Date]&" |c "&[Total
Purchase]

Now Access has a field that looks just like this:

| Mary Jo |a 54555 |b 3/9/2005 |c $12,649.45

Now I need a Query to split that up. The Query will end up being an
Append/Update Query...So the split data will be stored in a different table.
I want this to happen upon the opening of the Database, this way the end
users don't ever see the concatenated fields or the process by which they
are split.

I hope this makes more sense. I figure, it'll be tedious, perhaps, setting
up that Query, but once set up, I won't have to modify it again, and the
only data I'll see are the separated fields. I was able to do this before
when the fields were always of fixed length, 'cause it was easy to say Go
Left 4 spaces, but when the Name can be of varying Length, as can the Date
and Purchase Amount, I need the code to say Until a Certain Character.

Thanks again in Advance.
 
Micah said:
Hi Group...How do you use the LString and RString functions in a
Query? I have a series of concatenated fields that I'm trying to
split up. I can figure out how to do LString and so many characters,
but what if I needed to do LString to a certain character?

Example:

I can have in one field the following string...
Click to expand...


Look up the Split Function and use the "|" as the delimiter.
This will give you an arrary and you can add a record using the array

With MyRecordset
.addnew
.MyFirstFieldName = SomeArray(1)

etc.
 
I know it's been a while - since I first posted this topic, but I'm just now
getting to the Access portion of the project. I looked up the Split Function
as suggested, it looks like it'll work. Two questions though:

1. Can I use multiple characters as the delimiter?
2. Can I see an example? The Help didn't have an example, and I'm a little
fuzzy on all the terms they were using.

Thanks again.
 
OK This works, but is it the most efficient way to go?

Concat Field Contains the following string:

"FieldA|aFieldB|bFieldC|cFieldD|dFieldE|eFieldF|fFieldG|gFieldH|hFieldI|iFieldJ|jFieldK"

My SQL statement of my Select Query is this:

SELECT Table1.[Concat Field], Left([Concat Field],InStr([Concat
Field],"|")-1) AS FieldA,
Left(Mid([Concat Field],InStr([Concat Field],"|")+2),InStr(Mid([Concat
Field],InStr([Concat Field],"|")+1),"|")-2) AS FieldB,
Left(Mid([Concat Field],InStr([Concat Field],"|b")+2),InStr(Mid([Concat
Field],InStr([Concat Field],"|")+1),"|")-2) AS FieldC,
Left(Mid([Concat Field],InStr([Concat Field],"|c")+2),InStr(Mid([Concat
Field],InStr([Concat Field],"|")+1),"|")-2) AS FieldD,
Left(Mid([Concat Field],InStr([Concat Field],"|d")+2),InStr(Mid([Concat
Field],InStr([Concat Field],"|")+1),"|")-2) AS FieldE,
Left(Mid([Concat Field],InStr([Concat Field],"|e")+2),InStr(Mid([Concat
Field],InStr([Concat Field],"|")+1),"|")-2) AS FieldF,
Left(Mid([Concat Field],InStr([Concat Field],"|f")+2),InStr(Mid([Concat
Field],InStr([Concat Field],"|")+1),"|")-2) AS FieldG,
Left(Mid([Concat Field],InStr([Concat Field],"|g")+2),InStr(Mid([Concat
Field],InStr([Concat Field],"|")+1),"|")-2) AS FieldH,
Left(Mid([Concat Field],InStr([Concat Field],"|h")+2),InStr(Mid([Concat
Field],InStr([Concat Field],"|")+1),"|")-2) AS FieldI,
Left(Mid([Concat Field],InStr([Concat Field],"|i")+2),InStr(Mid([Concat
Field],InStr([Concat Field],"|")+1),"|")-2) AS FieldJ,
Left(Mid([Concat Field],InStr([Concat Field],"|j")+2),InStr(Mid([Concat
Field],InStr([Concat Field],"|")+1),"|")) AS FieldK
FROM Table1;

Thanks for any and all help.
 
Why am I getting the wrong value here? I have a field (First) with the
following value:

PREPAID|aBret Denio-BDW|bChaz Curtis|cExtract|d|eYes|fNew

In a Select Query I have the following expression:

CSR:
Left(Mid([First],InStr([First],"|b")+2),InStr(Mid([First],InStr([First],"|")+1),"|")-2)

I'm getting "Chaz Curtis|cE" as my result as opposed to "Chaz Curtis". Can
someone please tell me what's wrong with my expression? Thanks a million.
 
Back
Top