Formatting part number 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

 

[Rick Brandt]

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)

Since Mid([PART_NUM],4,3) gives you characters 4,5, and 6 the next part
should start at 7, not 6 as you have it. Other than that what do you
get? This strategy ought to work.

 

[Tom]

This is what I get when I use seven

MC5-553-005329 there are no more dashes after that


here is another result from te same
190-007-69000A

 

[John Spencer]

I would just use the format function as long as the field is always 12
characters in length.

Format([Part_Num],"@@@-@@@-@@@-@@@")

UPDATE YourTable
SET Part_Num = Format([Part_Num],"@@@-@@@-@@@-@@@")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Tom]

That is exactly what I needed ...you are a life saver thanks

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

Rick brant pointed one flaw in your counting, and your code doesn't
insert a dash after the third group.

I've occasionally had problems with right() getting confused by
spaces at the end of a field, so try

mid([PART_NUM], 1,3) & "-"
& Mid([PART_NUM], 4,3) & "-"
& Mid([PART_NUM], 7,3) & "-"
& mid([PART_NUM],10,3)

all on one line in the query
123456789ABC will then be 123-456-789-ABC

 

[Tom]

Thanks again for all your valuable help

I would just use the format function as long as the field is always 12
characters in length.

Format([Part_Num],"@@@-@@@-@@@-@@@")

UPDATE YourTable
SET Part_Num = Format([Part_Num],"@@@-@@@-@@@-@@@")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

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