STRING TRIM

[chexsp]

I am in need of help.

I need to trim multiple strings from a field in a Access database table.
ie. "[ IGOTTOTRIMTHIS ]: NAME=MARK;ADDRESS=777 STREET;CODE=88"

How would I just extract "MARK" using the proper SQL statement?

 

[adamroof]

I want to keep an eye on this thread as i am heading the same way.
Trim will only take the leading and trailing spaces off of a string,
I know of Left() and Right() where you can count how many characters you want to move in to grab data, but handling variable lengths is confusing me.

Check the help files for Left() and Right(), and see if thatll help.

 

[chexsp]

Ya,

unfortunatly they varry in size.

I am attempting the Right() and Left(), but for some reason my query is coming out blank.

Where could I check for the Right/Left trim tips?

 

[chris153]

If the data you want to extract doesn't move around in the string you should be able to use the mid function and for the [ IGOTTOTRIMTHIS ]: NAME=MARK;ADDRESS=777 STREET;CODE=88
example this should do the job :

Mid([{FieldName}],26,4) (=MARK)

If the data you need to extract moves from 26 or is longer that 4 you may need to use the instr function which returns the position of the first occurrence of one string within another.

Hope this helps !

 

[chexsp]

I may have another idea.

using a byVal function may work on the field.

name:byVal("NAME=",[fieldname])

However, Access recognizes that this is a VB function, and it does not understand.

Is there a way to import VB functions to Access?

 

[RoyVidar]

Hmmmm - InStr function?

Based on the string, perhaps something like the following:

[tt]NewField: Mid(FieldName,InStr(FieldName,"="), (InStr(FieldName,";")-InStr(FieldName,"="))-1)[/tt]

- doesn't work, look up the InStr function in the helpfiles

And - welcome to Tek-Tips!
You might want to read this faq too faq181-2886 (4 addresses crossposting, that's not exactly encouraged here;-))

Roy-Vidar

 

[luceze]

Roy,

- doesn't work, look up the InStr function in the helpfiles

I tested what I posted in the other thread and it seems to work fine. It's basically the same as your formula.

Eric

 

[RoyVidar]

luceze:
My comment in this thread contains a slip of the hand/mind or something similar, and was meant to address my own suggestion, which worked on the string provided in this thread (and should also work in chexsp's setup, providing I've been able to type it reasonable correct).

Should have been/what I meant:

- if it doesn't work, look up the InStr function in the helpfiles

(should perhaps also have added - or post back with more details, too...)

Sometimes it seems fingers and head aren't fully synchronized (I've tried me.refresh and me.requery to no avail)

No offense meant, and I offer my sincere apologies if I in any way have offended you or others!

Roy-Vidar

 

[luceze]

No offense taken. I was just trying to pick your extremely database information laden brain. Like you said I just misunderstood what you posted.


Eric

 

[chexsp]

Sorry guys...but I am confused.

Is the Instr function supposed to work?

My problem isn't trimming the first string (ie. NAME=MIKE). My problem is trimming the other strings after the first one...

ie. "[ IGOTTOTRIMTHIS ]: NAME=MARK;ADDRESS=777 STREET;CODE=88"

 

[luceze]

Now I'm not sure if I understand.
How exactly do you want your output (from your example) to look. Do you want each part in its own field or do you want everything in one field without the "labels"?


Eric

 

[chexsp]

Sorry bout that. I should've mentioned that...

Each in its own field.

But, when it comes time to trimming the second field (ie. ADDRESS, according to my example), how will an Instr function work...if their are multiples of ";" and "=".

won't it just keep outputing the first string inbetween the "=" and ";"...? instead of moving onto the ADDRESS string...

make sense?

 

[chexsp]

oh....
nevermind

I think I got it...

instead of using "="...I use "ADDRESS="

DUH!

sorry...
thanks guys.

 

[adamroof]

hey chex,
im a little confused, can you post the code that you have so far? What event are you launching this on?

What field are you putting the trimmed string into?

Thanks.

Adam

 

[luceze]

Correct, you need to keep the "search" strings unique. Also, the instr function will only return the position of the first character in the string. So you will need to add 8 to the first argument of the mid function to get the correct starting point.

Example:
NewField: Mid(FieldName,InStr(FieldName,"Address=")+8, (InStr(FieldName,";")-InStr(FieldName,"Address=")+8)-1)
(Not Tested)

Eric

 

[chexsp]

ok...

basically I am building a query using Access.

One of my fields has all the information, but in one full string (like the example I first posted in the topic).

I am trying to extract each string piece individually(ie.NAME, ADDRESS, CODE, etc.)

so, using the ACCESS Query Design builder, how do I extract this full string, into individual fields.

 

[adamroof]

ahh, this is a Form forum i thought.

 

[adamroof]

What i am doing is such...

I have a linked table to an Outlook Folder, and when an email comes in, i would like to generate a new "Client Record". I can easily grab the email address, sent time, and all that, but the body of the message contains one string that has a bunch of Info...

Name: Adam Roof
Address: PO Box 1234
City: My Town
State: CA
Interest: Network Wiring
Source: WWW

That is a sample of my website Form Post that emails me.
I see how i can grab the data now, thanks to all of your posts.

MY OTHER question, is can this happen as soon as a new email comes in??

rst!Name = Mid(me.emailBody,InStr(me.emailBody,"Name:")+5, (InStr(me.emailBody,";")-InStr(me.emailBody,"Name:")+5)-1)
rst!Address = Mid(me.emailBody,InStr(me.emailBody,"Address:")+8, (InStr(me.emailBody,";")-InStr(me.emailBody,"Address:")+8)-1)...etc