Scientific Notation - I wish MS Gave us a switch to turn it off

[Perico]

Microsoft really needs a feature to turn off Scientific Notation. It's
causes us no end of aggravation and we give them a ton of businsess.

I'm importing a spreadsheet into Access 2003 from Excel 2003. There are
account numbers such as 0123456789 or 0123E456987 that undesirably convert to
scientific notation upon import. Users enter these values in Excel, so the
column is formatted as text (if it were General, the lead 0 would drop out.)
I then, using automation from Access running Excel macros, append an "x" to
the front of the cell values and convert the format to General, then use
TransferSpreadsheet to bring into Access. That's a hassel.

Is there an algorithm I can us in Access to convert scientific notation back
to text or will the fact some accounts start with a 0 create a problem?
We've had this issue for over a year withou satisfactory resolution.

 

[Ed Ferrero]

Hi Perico,

I hope that I have read your question correctly.

This type of problem usually occurs because when Access imports an Excel
file, it reads the first few rows of the Excel worksheet and sets data types
for each column based on its own algorithm. Unfortunately Access can make
mistakes at times.

Here is a little trick I use to import worksheets into Access.

Set up the Excel worksheet with column headings, then three hidden lines,
then user data.
In the three hidden lines enter dummy data that is in the correct format for
each column. i.e. for your account numbers enter "xxxx" to force Access to
recognise this as a text column.

In Access, set up a link to the Excel worksheet.
Write a make table query that copies the linked Excel data to a new table.
Use a criteria in this query to exclude the dummy data in the worksheet.
e.g. in the above example WHERE [account number] <> 'xxxx'.

Now you can just run the make table query each time you wish to import the
data.

Ed Ferrero

 

[Perico]

Interesting, Ed. (I still wish MS made it easier.) I use the
transferspreadsheet method to pull the data into Access. Would your
technique work with that? What does you code look like setting up and
breaking the link?

Hi Perico,

I hope that I have read your question correctly.

This type of problem usually occurs because when Access imports an Excel
file, it reads the first few rows of the Excel worksheet and sets data types
for each column based on its own algorithm. Unfortunately Access can make
mistakes at times.

Here is a little trick I use to import worksheets into Access.

Set up the Excel worksheet with column headings, then three hidden lines,
then user data.
In the three hidden lines enter dummy data that is in the correct format for
each column. i.e. for your account numbers enter "xxxx" to force Access to
recognise this as a text column.

In Access, set up a link to the Excel worksheet.
Write a make table query that copies the linked Excel data to a new table.
Use a criteria in this query to exclude the dummy data in the worksheet.
e.g. in the above example WHERE [account number] <> 'xxxx'.

Now you can just run the make table query each time you wish to import the
data.

Ed Ferrero

Microsoft really needs a feature to turn off Scientific Notation. It's
causes us no end of aggravation and we give them a ton of businsess.

I'm importing a spreadsheet into Access 2003 from Excel 2003. There are
account numbers such as 0123456789 or 0123E456987 that undesirably convert
to
scientific notation upon import. Users enter these values in Excel, so
the
column is formatted as text (if it were General, the lead 0 would drop
out.)
I then, using automation from Access running Excel macros, append an "x"
to
the front of the cell values and convert the format to General, then use
TransferSpreadsheet to bring into Access. That's a hassel.

Is there an algorithm I can us in Access to convert scientific notation
back
to text or will the fact some accounts start with a 0 create a problem?
We've had this issue for over a year withou satisfactory resolution.

 

[Ed Ferrero]

Hi Perico,

Interesting, Ed. (I still wish MS made it easier.) I use the
transferspreadsheet method to pull the data into Access. Would your
technique work with that? What does you code look like setting up and
breaking the link?

With my method there is no need to use code. I just leave the link in Access
and replace the Excel Workbook as necessary.

Yes, the method would work if you use the TransferSpreadsheet method

Code would be;


DoCmd.TransferSpreadsheet acImport, 8, "ExcelLink", "C:\myPath\myBook.xls",
True, ""
DoCmd.OpenQuery "qryDelDummy"

Where the query qryDelDummy is something like

DELETE *
FROM ExcelLink
WHERE ExcelLink.[account number] Like "xxxx*"

Hope this helps.

If you wish to continue this thread, perhaps the access newsgroup might be a
better place?

Ed Ferrero