Update query to format part numbers

[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

 

[Marshall Barton]

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)

That kind of expression should do what you want, but the
second Mid needs to start at position 7 instead of 6.

A different idea is to use:

Format(PART_NUM, "@@@-@@@-@@@-@@@")

Be sure to add a criteria to the WHERE clause to make sure
the length of the field is 12. If the query is used more
than once, you don't want it to update fields that have
already been updated.

 

[John W. Vinson]

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

If you wish to store a whole bunch of meaningless, redundant and unnecessary
hyphens <g> - you can update it to

Format([PartNumber], "@@@\-@@@\-@@@\-@@@")

Or you could just set an Input Mask to display the hyphens but not store them.

 

[Tom]

the imput mask seems like a better idea. Thanks. I appreciate the input!
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

If you wish to store a whole bunch of meaningless, redundant and unnecessary
hyphens <g> - you can update it to

Format([PartNumber], "@@@\-@@@\-@@@\-@@@")

Or you could just set an Input Mask to display the hyphens but not store them.