Horrible Scientific Notation is ruining a perfectly good table (1 Viewer)

[CarlaKingery]

Thanks for the help in advance,

I have linked an excel table in Access. The excel table has a phone number field which is a number field in both Excel and Access. All of the numbers returned except for numbers with an extension. Those numbers returned in scientific notation. How do I get it to stop doing that? And even better, can I get the numbers to show up in Access in phone number format, ie (xxx) xxx-xxxx?

Thanks, nothing seems to ever work perfectly does it?

Carla

 

[Sohaila Taravati]

You can go to the properties of your field and use input mask to automatically do this (xxx) xxx-xxxx. As far as the extension the only thing I can think of is make a query and play around with it to seporate the #'s with the extension.

 

[CarlaKingery]

I tried the input mask but it will not allow one in a number field only a text or date field and Access will not allow me to update anything in the Design view of a linked table. Any other ideas? Thanks!!

Carla

 

[cogent1]

Do you mean that your linked table uses a number type for the telephone field instead of a text type? That should be changed if you have the ability to edit the linked table at its source.

You can always, at a pinch, copy this data to another table, convert it there and set up a one-to-one relationship with your linked table.

 

[CarlaKingery]

I'm close to tears with this, you guys have been wonderful and if you could just help me a little bit more . .

I changed my phone number field to a text field in Excel and even deleted and relinked it. The majority of the Excel table was created by the Vlookup function in Excel but when the phone numbers were changed by hand some appear and some don't in Access. I tried to change the Access field to a text field but it wouldn't work, I don't know what to do now. Also I have two phone numbers that have extensions and they continue to flip into scientific notation no matter what I try.

C

 

[cogent1]

Nil Desperandum

Life's too short to get upset by such things. Don't despair, there is a solution.

1) Have you definitely changed the field type in Excel to be Text, using FORMAT/CELLS /Text while the column is selected and then saved the Excel file?

2) Why do you link the table, why not import it? is the Excel file supposed to reflect changes you make in Access or is it a passive data source?

3)Give me an example of your phone data with an extension number. Any intervening symbols which might lead Acess to believe that exponentiation is necessary?

4)If you are really at your tether's end send me the db and its associated Excel file.

 

[CarlaKingery]

Cogent1,
You're absolutely right, I pretended to know Access for a temp job and just when I think I figure it out it provokes me to tears yet again.
I definately have changed the field to a text field in Excel but it refuses to change over in Access. Even when I tried to relink the worksheet after I had saved it as text.
The scientific notation occurs for only two entries they look like this: 2152459100x6460
They used to link into Access although whatever I have done has now caused them just to appear as Num#.
I had reconciled myself to either ignoring them or creating an "extension" field, although that seems a pity with only two entries out of 600+
However what is really puzzling me is a handful of the phone number entries in the Excel worksheet even though they are in text format and are entered like this 9999999, they refuse to 1)Switch formats to say a Special phone number format and 2) they refuse to be linked into excel and return Num#Do you have any idea why this would happen and how I could fix it? I need to link them because my office works in Excel and they need to update constantly.
Thank you for all you help, you have been wonderful. I wish I could send you the db but it is too big to zip and email for some reason and the office won't let it out (I tried in desperation a couple of weeks ago ) Carla

 

[CarlaKingery]

Whoopie!!!

I think I did it!! Slightly illegal methodology but hell, it worked I think!
Because Phone numbers was a Vlookup field in Excel I think it was messing up the numbers that were not Vlookup, or else they were just being stubborn anyway I copied the list and pasted them into the Excel field as text this time they imported even the one's with extension! They aren't in pretty phone number format, so do you know how to work the input mask for an extension option?

Thanks again!!
CARLA

 

[cogent1]

Well done, old sport!


you can't apply an input mask to a linked table as far as
I know, (now watch someone humiliate me..) but you can set up a telephone format in Excel under

FORMAT>CELLS>Special>Telephone Number

or use

FORMAT>CELLS>Custom to specify your own

Regards

 

[CarlaKingery]

Thank you for all your help! My confidence has been restored and now I'm sitting with nothing to do dangerously inventing new ideas that will simply sabotage my beautiful db.
Carla

 

[David R]

That's the spirit!

Invention is the mother of necessity...or something.