insert into field

[didacticone]

i have a field in a table that contains an account number. our numbers are 9 digits. (ex. 999-999-999) after exporting them into access from one of our programs they show without the hyphen (ex. 999999999) i was wondering if there is any way, like a query or something that i can use to add the missing hyphens. they will always be after the 3rd and 6th digits. any ideas? thanks!

[ajalwaysus]

Try this,

Left(AccNum,3) & '-' &  Mid(AccNum,4,3) & '-' & Right([AccNum,3) AS AccountNumber
 

But I would use this for display purposes only, it is always best to store this as a number in the table.

-AJ

[ChipR]

Why would you store a phone number as a number rather than text?

[ajalwaysus]

If I remember correctly, it has something to do with numbers store better than text, and is easier to index. I could be wrong but that is what I have based what I've done on.

I would assume they will be doing searches on the Account Number.

-AJ

[ChipR]

Sorry, just noticed that it was an account number and not a phone number. Regardless, I think it is a mistake to use a number field to hold something you can't use math on. What if the account number is 000000001?

[ajalwaysus]

Great point! That did not occur to me, didacticone, I believe ChipR makes a good point, you should make the appropriate changes.

But I think the rule still applies to phone number, I will have to research this more, but I know I heard a pro and con discussion about this before and number storing was the way to go.

Thanks for keeping me honest ChipR. =)

-AJ

[ChipR]

As long as you don't need country codes, extensions, etc. :)

[NeoPa]

Just to add my two pennies worth here :
I can only think of calculations as a reason for storing formatted numbers as numbers rather than as strings. I'd be interested in seeing where anyone contests otherwise. It would be good to read what they had to say ... before I told them how wrong they were!! :D

Actually, because of the inherent problems with numbers no matter how they're held, I would always advise not using any value that can be ambiguous for identification fields. Account numbers as "02151" for instance is a recipe for bucket loads of confusion down the line. Even if you have a few level-headed IT people who can handle it you are absolutely guaranteed to find people in the organisation for whom a number stored as text is a thought too far. Trust me when I say this will eventually come back to you, no matter how well you handle the data yourself.