Part number formatting help

[Tom]

I am importing part numbers from an Excel Spreadsheet into Access 2007 . the
field name is PART_NUM

The partunmber field is text and is 12 digits in length

I want to do an update query that will place dashes into the part number
after every three letters or numbers for example 000-000-000-000 or
123-abc-768-def

I have tried several combinations but none of them work properly

Left([PART_NUM],3) & "-" & Mid([PART_NUM],4,3) & "-" & Mid([PART_NUM],6,3) &
Right([PART_NUM],3)



I even tried using the len field to no avail


Left([PART_NUM],Len([PART_NUM])-6) & "-" & Mid([PART_NUM],4,3) & "-" &
Right([PART_NUM],Len([PART_NUM])-6)

Any suggestions would be much appreciated

Tom

 

[Lou]

I am importing part numbers from an Excel Spreadsheet into Access 2007 . the
field name is PART_NUM

The partunmber field is text and is 12 digits in length

I want to do an update query that will place dashes into the part number
after every three letters or numbers for example 000-000-000-000 or
123-abc-768-def

I have tried several combinations but none of them work properly

Left([PART_NUM],3) & "-" & Mid([PART_NUM],4,3) & "-" & Mid([PART_NUM],6,3) &
Right([PART_NUM],3)

I even tried using the len field to no avail

Left([PART_NUM],Len([PART_NUM])-6) & "-" & Mid([PART_NUM],4,3) & "-" &
Right([PART_NUM],Len([PART_NUM])-6)

Any suggestions  would be much appreciated

Tom

I tried this and it seemed to work.

SELECT Mid( '0123456789AB', 1,3 ) & '-' & Mid( '0123456789AB', 4,3 ) &
'-' & Mid( '0123456789AB', 7,3 ) & '-' & Mid( '0123456789AB', 10 )
from table1

Of course I was using the literal '0123456789AB'. It might fail if
not any Part Numbers had fewer than 10 characters.