Seperate text in a field

[fgcity]

Hi all.

I have a question about something i'm doing.

i have a table with a text field that contains "34 XKY45"

I need to somehow automatically Seperate "34" and "XKY45" and place the text "XKY45" in a field with an ID "34" on a different table.

How can i do that ?

Please help out.

 

[DCrake]

Simple Software Solutions

Assuming that you want to seperate the first two characters from the remainder of the text you need the following:

Suffix = Trim(Left(YourField,2))
Prefix = Trim(Mid(YourField,3))

CodeMaster:

 

[georgedwilkinson]

Is there always a space delimiter? Is it always the first space in the overall string?

The sentence explaining what you want to do makes no sense, but, assuming my 2 questions above are answered "yes", here is the general SQL syntax for separating the data:

select trim(left(myfield,instr(" "))) as TheNumber, trim(right(myfield,len(myfield) - instr(" "))) as TheRestOfIt
from mytableorquery;

 

[Mike375]

In a query of your table you add two new calculated fields. To do that you type in a field name (whatever you want to call it) and the name is followed by a colon and expression. The [xyz] is the name of the field in your table that has the data you wish to split

NewFieldNameforLeftofData:Left([xyz],InStr(1,[xyz]," ")-1)

NewFieldNameforRightofData:Right(Trim([xyz]),Len(Trim([xyz]))-InStr(1,[xyz]," "))

The first one will give the 34 and the second one the XKY45

Both of the above assume the data has a space between them that is the reason for the " "

 

[fgcity]

Tnx guys. this really helped.

I think i'll go with the "SQL" option.

select trim(left(myfield,instr(" "))) as TheNumber, trim(right(myfield,len(myfield) - instr(" "))) as TheRestOfIt
from mytableorquery;

 

[fgcity]

In general here is what i want to do:

I have an SMS software that communicates with Access and enters Incoming messages into "Messagesin" table

and outgoing messages are placed in the "messagesout", as soo as they are entered then the SMS message is sent.

What i need is this:

When a message comes i need to place it into a new table together with any other message that will come but with noduplicate senders (that's one of the "Incoming messages" fields)

this should be updated every 1 - 5 seconds,

In the context of each message there is the above described text "34 XKY45"

"34" is the ID number of a field in a seperate Table with a database of something.

So i need to actually place "XKY45" in one of the values of this field (YES the format os always the same (number + space + RegCode "XKY45" or any other short text)

in total for now i have 50 of this fields in the database.

So in the end i need to make a Text file that will contain the Fields that are in use (34, 35, 66) and the Reg Number in a format as below:

00
OPH345
34
KT45
12
KK45


this file is then used somewhere else not assiciated with Access.

So in general how would i do this ?

Note all this should be made automatically every 1-5 seconds