Delete first 2 digits off a number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jont - 24 May 2007 15:14 GMT

Hi can anyone help. I have a field in a table in my database which is
used for fax numbers our old system used to have all fax numbers
prefixed with 9, ie 9,01234 546785 our new system no longer needs
the 9, (new format 01234 546785) and I need to remove it. many of the
numbers in the field already have the 9, missing and are also of
varying lengths. Can anyone tell me how to do this. Thanks

Reply to this Message

Dale Fye - 24 May 2007 15:29 GMT

If all of your FAX numbers that have the "9," prefix actually have both of
these characters, then you could write an update query.

Update yourTable
Set [Fax_Num] = MID([Fax_Num], 2)
WHERE LEFT([Fax_Num], 2) = "9,"

*Note: Always backup your table/database before you try something that could
drastically change your data.

HTH

Signature

Email address is not valid.
Please reply to newsgroup only.

> Hi can anyone help. I have a field in a table in my database which is
> used for fax numbers our old system used to have all fax numbers
> prefixed with 9, ie 9,01234 546785 our new system no longer needs
> the 9, (new format 01234 546785) and I need to remove it. many of the
> numbers in the field already have the 9, missing and are also of
> varying lengths. Can anyone tell me how to do this. Thanks

Reply to this Message

Ofer Cohen - 24 May 2007 15:30 GMT

Try something like

Right([FieldName],Len([FieldName])-Instr([FieldName],","))

You don't have to delete the digits, you can use select query with the above
to display a new field.

If you still want to update the records *****BACKUP YOUR DATA *****
and then run update query with the above

Signature

Good Luck
BS"D

Reply to this Message