Concatenate data from one field to two fields in a table

[FlakyJake]

I have imported a large text file (UNIX) into a table. Some of the fields have shifted etc and i have a lot of sorting to do.

One key field has the email address then a limiter then booking number, as below

xt@abc.fsnet.co.ukþ0000189

I need to ditch the delimiter "þ" then move the email to another field and leave the booking code where it is.

any advice would be appreciated

thx

 

[TheAceMan1]

How are ya FlakyJake . . . . . .

Is the destination field in the same table?
Would a formula do, or do you perfer the destination field be updated or appended instead?

TheAceMan

 

[FlakyJake]

destination field "email" is in same table.

 

[TheAceMan1]

OK FlakyJake . . . . .

Sorry to get back so late.

In a query whick looks at the table, in place of the email field name, or a new field, type the following:

Left(SourceFieldName,InStr(1,"SourceFieldName","SearchString&quot-1)[/blue]

The search string is the "þ" identifier above. Just what this is, is a little mysterious. If its an special ANSI character, you'll have to use Chr(xxx) as the search string(no quotes). By eye it look like lowercase i, dash, lowercase o. I'll leave this for you to deciphier.

TheAceMan

 

[FlakyJake]

Howdi AceMan

Can't help you with the delimiter, it was an export from a unix flat file.

Can't seem to get a grip with what you are doing here. Does this function grab the left characters up until "þ" found. Then I suppose I have to instigate an update function to place it in the relevant field. Or is it executed in the new field grabbing the data from the sourcefield. If so does this function then delete that data in the sourcefield leaving the booking id???

 

[TheAceMan1]

OK FlakyJake . . . . . .

First, the field line was not complete. It should be :

NewFieldName:Left(SourceFieldName,InStr(1,"SourceFieldName","SearchString&quot-1)

Second, yes, the function grabs the left characters up until "þ". The function InStr(1,"SourceFieldName","SearchString&quot returns an index to the delimiting character. While Left(SourceFieldName,Index-1) returns the text up to the delimiter. In this field in the query, the e-mail addresses are displayed.

Yes, its executed in the new field grabbing the data from the sourcefield.

Since your only problem now is identifying the delimiting character, do the following.

In your query, in the first record, determine how many characters the delimiter actually is, and count the position of the first delimiting character. Then (as a test) in a new field (on the field line), enter the following:

AscChr:ASC(Mid(SoureFieldName,DelimiterPosition,1))

Run the query. The ASC equivalent of the delimiter will be returned as a number. Use this in the search string as Chr(Number).

If by chance the delimiter is more than one character, perform the above and get the numbers for each. The SearchString then becomes : Chr(Num) & Chr(Num) . . .

You don't have to update anything, and no, the function does not delete the source.

Hope this helps and clears things up . . . . . .






TheAceMan