Removing leading characters

[ryangus]

I have a field with a series of records that I wish to edit, e.g.:

1;im-1234
10;im784
123;im989

I wish to remove everything up to and including the ";" returning the
following results:

im-1234
im784
im989

The leading characters I want to remove differ in length, as do the
remaining characters.

Any ideas how to do this?

Regards,
Ryan

 

[Dirk Goldgar]

in message

I have a field with a series of records that I wish to edit, e.g.:

1;im-1234
10;im784
123;im989

I wish to remove everything up to and including the ";" returning the
following results:

im-1234
im784
im989

The leading characters I want to remove differ in length, as do the
remaining characters.

Any ideas how to do this?

Do you mean that you want to do this for all the records in the table? You
would probably use an update query for that, with SQL along these lines:

UPDATE YourTable
SET YourField = Mid(YourField, InStr(YourField, ';') + 1)
WHERE YourField Like '*;*'

 

[ryangus]

in message








Do you mean that you want to do this for all the records in the table?  You
would probably use an update query for that, with SQL along these lines:

    UPDATE YourTable
    SET YourField = Mid(YourField, InStr(YourField, ';') + 1)
    WHERE YourField Like '*;*'

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Thanks, but that didn't work. I want to get rid of all characters up
to and including the ; for all records in a particular field

 

[Dirk Goldgar]

in message

Do you mean that you want to do this for all the records in the table? You
would probably use an update query for that, with SQL along these lines:

UPDATE YourTable
SET YourField = Mid(YourField, InStr(YourField, ';') + 1)
WHERE YourField Like '*;*'

Thanks, but that didn't work. I want to get rid of all characters up
to and including the ; for all records in a particular field

In what way didn't it work? I just tested it on a table of my own and it
worked as expected. I trust you understood to change "YourTable" and
"YourField" to the names of your table and field, and if those names contain
spaces they must be bracketed with the characters "[" and "]"; e.g., "[Your
Table]" and "[Your Field]".

Also, if this is in an ADP or being executed via ADO, you need to use "%" as
the wild card instead of "*".

If none of those considerations is the cause of your problem, you'll have to
give more information about what exactly you tried and what happened,
because it should work in normal circumstances.

 

[Tom van Stiphout]

On Wed, 2 Jan 2008 22:34:07 -0800 (PST), (e-mail address removed) wrote:

Why not? His SQL looks OK.
-Tom.

 

[ryangus]

On Wed, 2 Jan 2008 22:34:07 -0800 (PST), (e-mail address removed) wrote:

Why not?  His SQL looks OK.
-Tom.





- Show quoted text -

Apologies - it works fine. I must have been having a brain freeze.
Thanks for your help!

 

[ryangus]

Apologies - it works fine.  I must have been having a brain freeze.
Thanks for your help!- Hide quoted text -

- Show quoted text -

Thanks for the previous help. I now need to remove the ; and
everything to the right of it, e.g.:

Before:
1;im-1234
10;im784
123;im989

I wish to remove after and including the ";" returning the
following results:

After:
1
10
123

Any suggestions?

 

[John W. Vinson]

Thanks for the previous help. I now need to remove the ; and
everything to the right of it, e.g.:

Before:
1;im-1234
10;im784
123;im989

I wish to remove after and including the ";" returning the
following results:

After:
1
10
123

Any suggestions?

Just like Dirk said earlier in the thread:



John W. Vinson [MVP]

 

[Dirk Goldgar]

Just like Dirk said earlier in the thread:

No, now Ryan wants the reverse of that.

I should point out that, if the previous operation was successful, that
field no longer contains the characters that were before the semicolon. I
trust that you're working on a different copy of the field or table.

To do what is now requested, updating the field for all records in the
table, would take SQL like this:

UPDATE YourTable
SET YourField = Left(YourField, InStr(YourField, ';') - 1)
WHERE YourField Like '*;*'