Complex Splitting of cell

[ttbeverley]

Gday Guys.

I have a cell in one table of mine called MsgContext1, this cell contains the content of an SMS sent by an external program that i have no control over, the cell contains: devicename(location, IP:xxx.xxx.xxx.xxx):ALARM MESSAGE(channel: xx,xx,xx,xx);

I would like to know how to pull out the IP Address, and the ALARM message.
With this information i will add it to another two colums in a new table and use that new table to link to the source which will give me the device name and location based purely on the IP Address. i figure this way i am not making it too complex.

Can anyone help me with this? I have been looking at this for a couple of days now and am not getting anywhere.

This is all done in Access 2007.

Thanks for your help.

 

[Mike375]

This will give you the idea

Exp: InStr([abc],"IP")

That finds the character count where IP starts which is 22

Exp1: Mid([abc],[Exp],18)

That extracts starting at I and goers along for 18 characters to give

IP:xxx.xxx.xxx.xxx

Exp2: Mid([abc],InStr([abc],"IP"),18) is combining both to give the
IP:xxx.xxx.xxx.xxx

 

[ttbeverley]

This will give you the idea

Exp: InStr([abc],"IP")

That finds the character count where IP starts which is 22

Exp1: Mid([abc],[Exp],18)

That extracts starting at I and goers along for 18 characters to give

IP:xxx.xxx.xxx.xxx

Exp2: Mid([abc],InStr([abc],"IP"),18) is combining both to give the
IP:xxx.xxx.xxx.xxx

That was really quick, either im clueless, or your a genius..... leaning towards the later.....
Where do i put this to make it run? since i need it to run every time the table updates (note the field is located on a linked table called MSGSOURCE) Having just the IP Address will enable me to link all the other data.

Using this same theory, can i link that to the alarm at the end, changing mid to right and use the key in EXP as ALARM:?? but i dont want to limit the amount of charaters after the ALARM:, is there a way to use everything after the key word?
Thanks again.

 

[Mike375]

You create a calculated field in a query.

Exp2: Mid([abc],InStr([abc],"IP"),18) in this case I called the new field Exp2.

It can also be put as the data source for an unbound text box and in that case you start with an = sign

=Mid([abc],InStr([abc],"IP"),18)

To extract different parts in different ways search around (heaps of stuff) on Mid() (what I used), Left() and Right as well as InStr() (what I also used) and Len().

Len() counts the number of characters in a string either the original string or a string that has been extracted. inStr() find the position in a string of a character, including a space or a group of characters such as "IP". If I had only done "I" then that would given the position of the i in device.

Left() obviously extracts characters from the left and Right is from the right and Mid() is from the middle Mid() differs from the other two in the sense it is given a starting point to extract from and a finishing point.

You won't get ALARM with Right. Well you could by using Right and Left. Right would give ALARM MESSAGE(channel: xx,xx,xx,xx); and then Left could extract ALARM but that is what Mid() is for.

 

[ttbeverley]

You create a calculated field in a query.

Exp2: Mid([abc],InStr([abc],"IP"),18) in this case I called the new field Exp2.

It can also be put as the data source for an unbound text box and in that case you start with an = sign

=Mid([abc],InStr([abc],"IP"),18)

To extract different parts in different ways search around (heaps of stuff) on Mid() (what I used), Left() and Right as well as InStr() (what I also used) and Len().

Len() counts the number of characters in a string either the original string or a string that has been extracted. inStr() find the position in a string of a character, including a space or a group of characters such as "IP". If I had only done "I" then that would given the position of the i in device.

Left() obviously extracts characters from the left and Right is from the right and Mid() is from the middle Mid() differs from the other two in the sense it is given a starting point to extract from and a finishing point.

You won't get ALARM with Right. Well you could by using Right and Left. Right would give ALARM MESSAGE(channel: xx,xx,xx,xx); and then Left could extract ALARM but that is what Mid() is for.

Thanks very much Mike375. You have been a huge help. This should get me going for the time being......

 

[Mike375]

It is well worth the time and effort to get a full handle on those functions as well as the opposite, that is, joining fields. They are among the more common threads on the forum and quite often there is no simple one line answer as it depends on how the data is laid out so ultimately you have to be able to do it yourself.

 

[ttbeverley]

Ok Mike375, i have tried your suggestion and unfortunately i have not had any luck.

When i add the string Mid([abc],InStr([abc],"IP"),18) to the query based on the MsgContext1 box, there was and abc box that appeared, so i changed that to the name of the cell for both abc's and it did not do the split.

When i tried the right query (based onthe same line but removed the 18 (no figure) and changed "IP:" to "):" so that i got the complete alarm message, this did not work either....

The information you have given me i can see should definately be enough, but i think i am skipping a major step, and its confusing me....

Any more help you can give me would be greatly appreciated.

If you need, i can send you a db with the table and my version of the query.

Thanks again

 

[Mike375]

I have attached the little DB I did it in.

You replace the field [abc] with the field name of where your data is stored.

 

[ttbeverley]

AHHH..... thats what i did wrong....

i put it in as a criteria.....

Thanks heaps for that, will give it a shot and see what happens.

 

[Mike375]

What you have done is quite common on the forum.

As a side note. most of the time you see an answer on the forum like

Mid([abc],InStr([abc],"IP"),18)

it is used to make a new field in a query (as was the case here) or used for the data source for an unbound text box.

The query is always

NewFiedName:Code or expression

The unbound text box has it entered with an = sign first such as

=Mid([abc],InStr([abc],"IP"),18)