format right number in field

[luca90]

I just have a jet ado conn opned.
my db is access
my table is named Mytable
Into field CODE have a value similar:

0500-1
6875-45
...

How to update, with a sql query to format the right item from "-" in #000000 for all value in column field.
and have a result similar

0500-000001
6875-000045
...

[RhinoBull]

The following sql will work with MS Access; kim that you may need some additional conditions iin the where clause:

update table1
set field2 = left$(field2, instr(1,field2,"-")) + format$(mid$(field2, instr(1,field2,"-")+1), "000000")
where field2 is not null
and instr(1,field2,"-") > 0