Need a little database help

[DnetMHZ]

ok heres what I need to do.. I have an access 97 database which is part of some phone accounting software we run.
there is a table which has about 1000 phone numbers in it in the format 1xxxxxxxxxx what i need to do is trim off the 1's before the area code from every number. Exporting the data to another app. is not an option. Is there an easy way to change all these numbers?

Thanks
DnetMHZ

 

[Mitzi]

Dunno if Access supports real SQL queries but you can do it with the following SQL query...

UPDATE table_name SET column_name = LTRIM(column_name, '1');

 

[HomeBrewerDude]

you could also export the datafile to an csv format and trim in EXCEL using the built in functions. then reimport to Access.

 

[Descartes]

Not sure if what Mitzi said will work in Access, as every ltrim() function I've seen simply left-trimmed the spaces. That's the way it is in SQL Server anyway...

You could do this, however...

update yourtable set yourphonenumberfield = mid(yourphonenumberfield, 2, len(yourphonenumberfield))

 

[Mitzi]

<< Not sure if what Mitzi said will work in Access, as every ltrim() function I've seen simply left-trimmed the spaces. That's the way it is in SQL Server anyway... >>



LTRIM trims spaces if you don't provide a different set of extraction characters. Thats the SQL99 standard and how Oracle does not - not used SQL Server though.

 

[DnetMHZ]

Thanks guys!

Think i got it.

DnetMHZ