Change 111-222-333 to 222-111-333

[keun]

That title was just an example of the problem. I have a field where the data should be formatted a certain way:

StoreID-EmployeeID-Date

However, some records are entered as:

EmployeeID-StoreID-Date

How can I write a query to change the wrong records and reformat them properly?

Thanks!

 

[PHV]

How do you know when a number is an EmployeeID instead of a StoreID ?

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

 

[Remou]

You can easily split the field and re-assemble it, however, how can you tell an employee id from a store id?

 

[keun]

Store ID is letters, employee ID is numbers.

 

[PHV]

You may use an update query like this (SQL code):

UPDATE yourTable
SET yourField=Mid(yourField,InStr(yourField,'-')+1,InStrRev(yourField,'-')-InStr(yourField,'-')) & Left(yourField,InStr(yourField,'-')) & Mid(yourField,InStrRev(yourField,'-')+1)
WHERE yourField Like '#*-*-*'

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

 

[BobRodes]

The question that's begging to be asked is why do you have such a field in the first place, rather than breaking it into multiple fields?

 

[keun]

Unfortunately, the data was given to me this way! It's a flat file that SHOULD have been a relational database and it's use is short-lived, I just need to cull some data.

 

[BobRodes]

Well, there you go. We've all been there!

 

[keun]

OK, I thougt that with my question, I would be able to do this, but I cannot. I needed to be more specific with the details of this problem.

Here is the EXACT way the info needs to be presented:

TA-IL-0982-000007
TA-IL-0982-000008
TA-IL-0982-000009
TA-IL-0982-000010

However, some of the records are entered like:

0982-TA-IL-000007
0982-TA-IL-000008
0982-TA-IL-000009
0982-TA-IL-000010


I am sorry that I did not ask exactly as I needed this to work.

I tried the simple solution of opening the table and doing Replace ALL TA-IL-0982 with 0982-TA-IL - but that did not work. There are 1,500,000 records (if that matters).

If you can give me the query again I would appreciate it!

 

[PHV]

You may consider this:
UPDATE yourTable
SET yourField=Mid(yourField,6,6) & Left(yourField,5) & Mid(yourField,12)
WHERE yourField Like '####-??-??-#*'

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

 

[keun]

Very very cool. Thank you so much!