Update Query MS Access?

deleting characters

[ukgimp]

I have a large access db that I am trying to clean. I have lots of columns that have for some god only knows reason asterisks or other characters at the beginning or end. Can you delete characters from a field down a whole column without getting into heavy VB. If you can, care to share. I am in access xp or whatever it is called.

Cheers

[aspdaddy]

I did some of this kind of stuff yestersday.

Splitting postcodes into two parts, trimming whitespaces, change texts to proper case.

I used left, right, and I think instr, in conjuntion with the UPDATE.

I diddnt use replace but I bet that will do what you need:

Update tablename
SET fieldname= replace(fieldname,'*','')

or

Update tablename
SET fieldname= left(fieldname, len(fieldname)-1)
where field1name like '**'

// * is an access wilcard meaning start of string

[edited by: aspdaddy at 10:01 am (utc) on Sep. 4, 2003]

[deejay]

Low tech: edit find and replace. Just don't put anything in the replace box.

If the characters are truly stray characters that aren't used anywhere else in the field/column, that works fine.

<add> bugger. just realised you said asterisks - they're a wildcard in access, so be careful or you'll 'find' the whole field.

[edited by: deejay at 10:03 am (utc) on Sep. 4, 2003]

[aspdaddy]

Yeah much better idea - forgot about that in access :)

[deejay]

hehe... I actually just now read your answer aspdaddy... great minds and all that - even if we reach the same destination by different vehicles.

[ukgimp]

works, great.

Thanks :)

easy as that :)

[ukgimp]

Next challenge, since the last one was so easy for you :)

I have two felds that need combining, only trouble is that where one has null values the other has values and vise versa.

Is this about the right lines.

Append query of field1 + feild2 into newfield? I want to avoid overwriting full fields with empty values.

Cheers

[aspdaddy]

Maybe I'm misunderstading but couldnt you just clear all the nulls from field1 and then remove field2?

UPDATE table
SET field1=field2
WHERE field1 IS NULL;

ALTER TABLE table
DROP COLUMN field2;