Splitting 10 characters into 3 parts 2

[l3reacher]

I have a column with 10 digit numbers (1234567890) w/o any spaces in between in my table called "NUM". And in my Query, I want to split these 10 into 3 parts
4-3-3(1234-567-890). Thanks!! I want the first part to be in a field called "ID", second part in a field called "CusID", and third part in a field called "TimID".

How would I b able to do this??

I Hate Spammers and Flammers!!!

 

[Golom]

Assuming that these "Numbers" have a numeric data type, you will need to convert them to strings and then pull out the substrings like this
[blue][tt]
Select Left ( Format ( myField, "0000000000" ), 4 ) As ID,
Mid ( Format ( myField, "0000000000" ), 5, 3 ) As CustID,
Right( Format ( myField, "0000000000" ), 3 ) As TimID
[/tt][/blue]

 

[l3reacher]

I tried using this code within the SQL View, but when I try to run it, it gives me a message saying "The SELECT statement includes a reserved word or an argument that is misspelled or missing, or the punctuation is incorrect. I converted these numbers into a text.

I Hate Spammers and Flammers!!!

 

[Golom]

It may be that your field name and/or the new field names are reserved words
[blue][tt]
Select Left ( Format ( [myField], "0000000000" ), 4 ) As [ID],
Mid ( Format ( [myField], "0000000000" ), 5, 3 ) As [CustID],
Right( Format ( [myField], "0000000000" ), 3 ) As [TimID]
[/tt][/blue]

 

[l3reacher]

I did change my field name to other words that are not reserved, but it still pops up the error message.

I Hate Spammers and Flammers!!!

 

[Golom]

Can you post the SQL?

 

[l3reacher]

Select Left ( Format ( [MyNumbers], "0000000000" ), 4 ) As [ID],
Mid ( Format ( [MyNumbers], "0000000000" ), 5, 3 ) As [CustID],
Right( Format ( [MyNumbers], "0000000000" ), 3 ) As [TimID]

I Hate Spammers and Flammers!!!

 

[Golom]

That's some of it. Where's the FROM clause?

 

[l3reacher]

Oh... I don't know the code for this sql stuff..

I Hate Spammers and Flammers!!!

 

[Golom]

That's OK. You need a FROM clause to follow your Select that tells SQL the table to look in for the fields. Your SQL should look something like
[blue][tt]
Select Left ( Format ( [MyNumbers], "0000000000" ), 4 ) As [ID],
Mid ( Format ( [MyNumbers], "0000000000" ), 5, 3 ) As [CustID],
Right( Format ( [MyNumbers], "0000000000" ), 3 ) As [TimID]

FROM tbl
[/tt][/blue]
Where "tbl" is the name of the table that contains the field "myNymbers".

 

[l3reacher]

You make it look easy... It works great!! Thanks alot!!!!

I Hate Spammers and Flammers!!!