Test string looks strange

I have a field in a table that is a text field.   But when I run a query using the table as a record source the following strange thing happens.

For example, 0101210E103 looks like this in the query result:


What is going on?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveL13Author Commented:
I just noticed something.  When I just view the query results it looks fine.  But when I do this vis VBA code it doesn't:

DoCmd.TransferText acExportDelim, , "qryPRODUCTS_PartsMasters", strCSV & Environ("userProfile") & "\Desktop\" & "Product Master " & Format(Now, "mm-dd-yyyy_hh-nn") & ".csv", True
The problem is that you are probably just double clicking on the .csv file to open it and the default viewer is Excel.  Excel is not as smart as it thinks it is.  It takes long numeric STRINGS and assumes they are numbers and then converts them to scientific notation because actual numbers cannot be that long.  Other bad things happen when you have numeric strings that start with leading zeros.  Again, Excel assumes the string is a number and so removes the leading zeros.

To correctly open these files using Excel, you need to open Excel and go to the Data tab and from there import the .csv file.  You will get a dialog similar to what you see in Access where you can specify that these "numeric" fields are actually text and then Excel will display them correctly.

To solve the problem with your export, you need to convince Excel that the long numbers are actually text.  That requires exporting the file ONCE manually so you can create an Export spec.  In the Export spec, you specifically define these fields as text.  That will cause Access to surround them with quotes and that should be enough to convince Excel that they are text.

Right click on the query you want to export.  Go through the dialog and properly define each column.  On the last page of the dialog, press the Advanced button.  Make sure that you choose  quotes to surround text fields and coma separation.  Save the spec with a short name.  Then in the TransferSpreadsheet, reference the spec name to reuse your definition.
SteveL13Author Commented:
Pat, I must be missing something.  If I right click on the query I don't see the dialog.  ??
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SteveL13Author Commented:
Correction.  I can go through the dialog but I never get to where I can surround text fields with quotes.
OMG Excel has completely hosed this process.  Access correctly exports text numeric stringe enclosed in quotes per the .csv standard and Excel, because it is smarter than everyone else, overrides that and assigns a numeric data type.


I did find that if you opened Excel and then used the Data tab to import from Excel, you get a dialog but the only way to get it to import the long numbers correctly is to change the Data Type Detection to "do not detect".  That of course hoses all the other data types if you had currency and date data requiring you to add them back in.

I believe this is a BUG in Excel and should be reported to Microsoft.  Access exports the data correctly as you can see when you open the .csv file with notepad.  Both my text fields are enclosed in double quotes and that is supposed to define them as text.CSVtoEXCEL.JPG

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
You should win an award for discovering this!!!!  Thank you.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you should open the csv in Excel using the From Text feature under Data > Get External Data

if necessary, format the type of your field when necessary.

Excel will assign a default data type based on the values of that column.
That's the dialog I expected to see.  It's gone!!!!!
CSVtoExcel2.JPGChoosing the  From Text/CSV option, you get the dialog I posted.  Choosing the GetData option and then From File, and then From Text/CSV, you get the same dialog I posted.

I'm using O365 with Office 2016  Version 1709 (build 8528.2139 click-to-run) Monthly channel

The dialog you posted is similar to what you see from Access with the exception that you don't have an option to save your spec which of course means that you have to set the spec EVERY SINGLE TIME you open a file.
I also just noticed something else.  Using the "do not detect" option makes the header rows a data field!!!!!!!!!!!!!!!!!!!!!!!!!!

I just reported this to MS using the Excel back stage option  -  File/Feedback  So PLEASE, if any of you who read this think this is a much of a problem as I do, let MS know.  They will never fix this problem if they don't hear from us.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.