removing extra characters from data

  • Thread starter Thread starter jay
  • Start date Start date
J

jay

Field alpha contains data which appears as follows:
04-24053-jf
03-12345

Q1. What is the syntax if any for an update query that
would remove the characters starting with the second dash
so that 04-24053-jf would now be 04-24053

Cousin question

Field beta contains data which appears as follows:
12345678
1234567
87654321-ab
7654321-bd

Q2. What is the syntax if any for an update query that
would remove the characters starting with the dash so
that the data would look like 87654321 or 7654321

I am familiar with using access 2000, but not familiar
with code or sql.
 
For Q1. if alpha length string must be of 8 characters
then try using the len() and Left$() functions

For Q2. if you always have a string of numbers (variable
length) and then the "-xx" at the end try the Val()
function.
 
UPDATE For #1:

UPDATE YourTable
SET Alpha =
Left(Alpha,Instr(Instr(1,Alpha,"-"),Alpha,"-")-1)
WHERE Alpha Like "*-*-*"

UPDATE For #2
UPDATE YourTable
SET Beta =
Left(Beta,Instr(1,Beta,"-")-1)
WHERE Beta Like "*-*"
 
Back
Top