Query results different when exporting to Excel

So I have this query that is enclosed. When  i run the query the results for state are the two digit abbreviation from the Link to the Tblstates table, however after the module i wrote creates the excel spreadsheet the two digit ID is rendered in the excel?

*******************************Query************************************************************************

SELECT First(Provider.Reviewer) AS FirstOfReviewer, Provider.DoctorOffice, Provider.Street, Provider.Address2, Provider.City, Provider.State, Provider.Zip, Provider.County, Provider.Phone, Provider.Fax, Provider.[E-mail Address], Provider.Fee, Provider.ProviderType, Provider.Type, Provider.Speciality, Provider.Salutation, Provider.FirstName, Provider.LastName, Provider.Comments, Provider.ShippingAddress, Provider.ShippingAddress2, Provider.ShippingCity, Provider.ShippingState, Provider.ShippingZIP, Provider.ShippingCompany
FROM Provider LEFT JOIN tblStates ON (Provider.ShippingState = tblStates.ID) AND (Provider.State = tblStates.ID)
GROUP BY Provider.DoctorOffice, Provider.Street, Provider.Address2, Provider.City, Provider.State, Provider.Zip, Provider.County, Provider.Phone, Provider.Fax, Provider.[E-mail Address], Provider.Fee, Provider.ProviderType, Provider.Type, Provider.Speciality, Provider.Salutation, Provider.FirstName, Provider.LastName, Provider.Comments, Provider.ShippingAddress, Provider.ShippingAddress2, Provider.ShippingCity, Provider.ShippingState, Provider.ShippingZIP, Provider.ShippingCompany;

************************************Procedure ******************************************************

Private Sub Command71_Click()

 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qryproviderrpt_all", "C:\EBILteST\Provider_" & Format(Now(), "yyyymmddhhnnss") & ".xlsx", True
 
 FollowHyperlink "C:\EBILteST"

End Sub
seamus9909Asked:
Who is Participating?

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

x
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.

Fabrice LambertConsultingCommented:
There is Provider.State and Provider.ShippingState in your select statement, according to your join, these values are also equal to tblStates.ID.
This is why it is present in the output file.
seamus9909Author Commented:
But the query return the abbreviation. (NY, PA).  The Excel returns the ID
Fabrice LambertConsultingCommented:
Check the fields configuration, are there list of choices associated with it ? (you can see that with table creation mode).
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

PatHartmanCommented:
But the query return the abbreviation. (NY, PA).  The Excel returns the ID

Yet another problem caused by using table level lookups.  

Lookups DO NOT belong on tables.  They belong on forms.  Remove the lookup from the table.  Then rather than exporting the table, create a query that joins the table to the state table and select the abbreviation from there.  Table level lookups simply hide the true value of the stored data. Thye abbreviation is not what is stored in the table.  It is the numeric ID value and every time you use a query or write code, you have to be aware of that and know what value you actually need.  Once you realize that you need to create a query with the join to get the lookup value, you realize that this abomination hasn't actually saved you any work but it has caused confusion.
seamus9909Author Commented:
im so confused.  The query is returning the State Abbreviation.  So why when i create the excel does it revert back to the Id value?
Hamed NasrRetired IT ProfessionalCommented:
im so confused.  The query is returning the State Abbreviation.  So why when i create the excel does it revert back to the Id value?
I am surprised.
Try:
  • Copy records from the query in access and paste in excel.
  • Create a table from the query, then export the created access table to excel.

If it copies/exports as you expect, then one should investigate the query. To do that, try to recreate the issue with a demo database and upload.
I am not aware of any special restrictions on exporting queries.
Fabrice LambertConsultingCommented:
im so confused.  The query is returning the State Abbreviation.  So why when i create the excel does it revert back to the Id value?
PatHartman gave you the reason:
MS Access give priority to table level lookup when returning query results, but not when exporting.
Either:
- Check your tables design, and remove lookup values.
- Adjust your query so it also pull the values associated with ID.

Optionally, give us or explain your data model so we can help with the query.
seamus9909Author Commented:
hi here is a sample of the table (tblprovider) and the previusly stated query.  You will see the results are the state abbr. and not the ID.  

This may help

1.   Run the Query
2.   click on "export to excel" on toolbar
3.   Select "export with formatting and layout" on popup
Excel worksheet shows the state Abbreviaton

if  run steps 1 and 2 above not step 3 then the excel worksheet shows the Lookup IDs of the state.

Is there  a way in the procedure I wrote to also export with formatting and layout"

THank you everyone!  I know i am a novice here
Hamed NasrRetired IT ProfessionalCommented:
Sample is missing from comment.
PatHartmanCommented:
im so confused.  The query is returning the State Abbreviation.  So why when i create the excel does it revert back to the Id value?
I told you.  You have a table level lookup.  The export is exporting the ACTUAL table contents NOT what Access is showing you.  This happens in many situations when you use table level lookups and is one of the major reasons that experts recommend against them.  

Remove the table level lookup.  View the data.  You will see that that is what is being exported to Excel.  Read my original suggestion again to see the solution.

I included an example.  tblCompany has two state codes.  One with a lookup and one without.  There are two error queries that show typical problems encountered with table level lookups.  Export qLookup1 and see that the spreadsheet results are different from what you see when you view the query.
TableLevelLookups.accdb

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
Hamed NasrRetired IT ProfessionalCommented:
What you get in Excel is the right thing. In query you display the required values to be displayed, say field_name instead of field_id..
View the Lookup properties for the ID  field and check the column widths. The ID field width might be set to 0. Setting the column widths to non-zero value, displays the ID values.

To output the field_name, you need to include it in the query, by joining the relevant code table, or by displaying the description instead of the code in the query.
So what access is outputting is right, but you are confused by what is displayed in the query,
seamus9909Author Commented:
Thank you Pat.
PatHartmanCommented:
You're welcome.
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.