update phone number function

[yld53x]

hi

i'm importing a table with phone numbers (home and mobile) eg (07) 555555 and (0412) 00000.

in design query how can i get rid of the () and leave it at 07555555 and 041200000.

thanks

 

[Kozia]

run update query on it
try iff(Mid(homephone,4,1=&quot",Mid(homephone,2,2) & Mid(homephone,6,10), Mid(homephone,2,4) & Mid(homephone,7,10))

if it it has a problem you will have to write something like that in a code and run it on your table

 

[yld53x]

i've have ago at it.

thanks for the quick reply kozia

 

[yld53x]

i've typed in the function and it comes back with "function containing the wrong amount of arguements"

 

[Kozia]

Oh I know what the problem is the coma's try this if it still gives you an error write a subroutine.
iff((Mid(homephone,4,1=&quot&quot),(Mid(homephone,2,2) & Mid(homephone,6,10)), (Mid(homephone,2,4) & Mid(homephone,7,10)))

the subroutine will go something like this
sub updateit()
Dim dbs As Database
Dim rstSet As DAO.Recordset
Dim tmpPhone as string

set dbs=currentdb
set rstSet=dbs.OpenRecordset("tablename&quot

rstSet.MoveFirst

do until rstSet.EOF
rstSet.Edit
tmpPhone=rstSet![phonefield]
if Mid(tmpPhone,4,1=&quot&quot then
rstSet![homephone]=Mid(tmpPhone,2,2) & Mid(tmpPhone,6,10)
else
rstSet![homephone]=Mid(tmpPhone,2,4) & Mid(tmpPhone,7,10)
rstSet.Update
rstSet.MoveNext
loop

end sub

This should work

 

[Kozia]

oh oh I found another error
iff((Mid(homephone,4,1)=&quot&quot,(Mid(homephone,2,2) & Mid(homephone,6,10)), (Mid(homephone,2,4) & Mid(homephone,7,10)))
and in the subroutine change line
if Mid(tmpPhone,4,1=&quot&quot then
to
if Mid(tmpPhone,4,1)=&quot" then

this should work

 

[yld53x]

thanks kozia

it works now

thanks again