Removing leading characters

  • Thread starter Thread starter ryangus
  • Start date Start date
R

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
 
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?
Click to expand...


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 '*;*'
 
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 -
Click to expand...

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 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 '*;*'
Click to expand...
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
Click to expand...


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.
 
On Wed, 2 Jan 2008 22:34:07 -0800 (PST), (e-mail address removed) wrote:

Why not? His SQL looks OK.
-Tom.
 
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 -
Click to expand...

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

- Show quoted text -
Click to expand...

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?
 
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?
Click to expand...

Just like Dirk said earlier in the thread:



John W. Vinson [MVP]
 
John W. Vinson said:
Just like Dirk said earlier in the thread:
Click to expand...


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 '*;*'
 
Back
Top