Adding a dash to existing table field?

[HelloWorldGuy]

Hello, I'm pretty new to Access and am trying out some new things. I want to add a dash to a table field data like :
Before:

CS220061000
T5325204000
14W47163000
TBR20131000

After:

CS2-20061000
T53-25204000
14W-47163000
TBR-20131000

the field name is ITEM_NUMBER

I'm thinking about a Query with a SQL statment kinda like

UPDATE MyTable SET ITEM_NUMBER = Format(ITEM_NUMBER, "@@@-@@@@@@@@")
WHERE Len(ITEM_NUMBER) = 11

A little unsure on the SQL syntax...
Thanks!

-Guy

 

[PHV]

The syntax seems correct to me.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HelloWorldGuy]

That syntax was corret, however I was wrong about functionality. What I am actually looking for is a SQL query statement that will

Before
CS220061000
T5325204000
14W47163000
TBR20131000
05226R52000

After:

061-CS220
204-T5325
163-14W47
131-TBR20
R52-05226

After a search I'm not seeing anything specifically for this. Any suggestions would be welcomed.
Thanks a bunch!

 

[PHV]

Like this ?
Mid(ITEM_NUMBER,6,3) & "-" & Left(ITEM_NUMBER,5)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HelloWorldGuy]

So it would be like this?

UPDATE MyTable SET ITEM_NUMBER = Format(Mid(ITEM_NUMBER,6,3) & "-" & Left(ITEM_NUMBER,5)
WHERE Len(ITEM_NUMBER) = 11

Implementation should just be ?

1. New query
2. Design view
3. input statement

Would you advise trying it on a copy tbl?

 

[PHV]

I'd use this:
UPDATE MyTable SET ITEM_NUMBER = Mid(ITEM_NUMBER,6,3) & "-" & Left(ITEM_NUMBER,5)
WHERE Len(ITEM_NUMBER) = 11

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HelloWorldGuy]

Not sure why but it says it's updating 0 rows...

 

[PHV]

What is the result of this query ?
SELECT Count(*) FROM MyTable WHERE Len(ITEM_NUMBER) = 11

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HelloWorldGuy]

The result is the table view of MyTable with a field Expr1000 where cell A1 has a 0.

My process is to create a new Query, go to SQL design, enter statement, then run.

 

[Golom]

If you ran your first update query then everything that was 11 characters long is now 12 characters long so a WHERE clause like

[blue]WHERE Len(ITEM_NUMBER) = 11[/blue]

will not return any records and that's what the zero in PHV's query is telling you.

You should be doing this kind of experimentation on a copy of your table so that you can always get back to the original if a change doesn't work as intended.

 

[HelloWorldGuy]

No worries, I started using copies.

 

[HelloWorldGuy]

Great eyes, Golom. Thank you PHV! You guys are awesome. Everything is running great! Going to do some data validation just to be safe but it looks awesome!

 

[HelloWorldGuy]

after some consideration I have one issue and am not sure what's causing it.

with:
UPDATE tblSnapshotMASTER SET ITEM_NUMBER = Mid(ITEM_NUMBER,7,3) & "-" & Left(ITEM_NUMBER,6)
WHERE Len(ITEM_NUMBER) = 12;

where my output:
R51-[highlight #FCE94F][/highlight] T5820

why is the space there I wonder? The "&" doesn't equal a space, right? Thanks ahead!

 

[PHV]

Seems like you have some ITEM_NUMBER with leading space ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HelloWorldGuy]

Ah, yes. That is exactly what is happeneing. Best solution, new import specification?

 

[Golom]

Or an UPDATE query

UPDATE tblSnapshotMASTER SET ITEM_NUMBER = Trim$(ITEM_NUMBER)

 

[HelloWorldGuy]

This is all around handy. I could use this often in the future. I'm still new to SQL so this really helps. Thank you Golom!