Expr1003 appears instead of field name

[Michael's Knight]

I have windows 7 access 2013,
i had a query made, and wanted to add fields to it, so i went into the SQL and added several fields. Only the first one i entered didn't work and on the query is called "Expr1003" when in datasheet view. Its right in layout view, and even when i tried adding the same field in layout view, it made it "Expr1004". spelling is correct, in both source table and query, i don't know why this field doesn't work especially when the others did.

 

[Uncle Gizmo]

Post the SQL of your query, see instructions here:- http://www.accessmvp.com/JConrad/accessjunkie/ng.html#sql

 

[Michael's Knight]

SELECT BookDetailsTable.BookTitle, BookDetailsTable.AuthorID, BookDetailsTable.FormatID, BookDetailsTable.Edition, BookDetailsTable.Edition, BookDetailsTable.DateOfEditionRelease, BookDetailsTable.Impression, BookDetailsTable.DateOfImpression, BookDetailsTable.PrinterCompanyID, BookDetailsTable.PublisherOfRecordID, BookDetailsTable.USRetail, BookDetailsTable.USWholesale, BookDetailsTable.PrintCost, BookDetailsTable.CanadaRetail, BookDetailsTable.CanadaWholesale, BookDetailsTable.UKRetail, BookDetailsTable.UKWholesale, BookDetailsTable.Edition, BookDetailsTable.[13DigitISBN], BookDetailsTable.[10DigitISBN], BookDetailsTable.BookSizeID, BookDetailsTable.ColorID, BookDetailsTable.DateOfCompletion, BookDetailsTable.[DatePublished/StreetDate], BookDetailsTable.CopyrightHolder, BookDetailsTable.ProjectStatus, BookDetailsTable.EuropeRetail, BookDetailsTable.EuropeWholesale, BookDetailsTable.USWholesaleDiscount, BookDetailsTable.CanadaWholesaleDiscount, BookDetailsTable.UKWholesaleDiscount, BookDetailsTable.EuropeWholesaleDiscount, BookDetailsTable.USManufactureCost, BookDetailsTable.EuropeManufactureCost, BookDetailsTable.UKManufactureCost, BookDetailsTable.CanadaManufactureCost, BookDetailsTable.Notes
FROM BookDetailsTable
ORDER BY BookDetailsTable.BookTitle, BookDetailsTable.Edition, BookDetailsTable.Edition, BookDetailsTable.Impression;

 

[Michael's Knight]

BookDetailsTable.Edition doesn't appear as "Edition" in the datasheet view. i added the highlighted sql, and the reason there are 2 "BookDetailsTable.Edition" is from trying to add one throught the layout view afterwards.

 

[Uncle Gizmo]

Remove one of the "BookDetailsTable.Edition," and see what happens.

 

[Michael's Knight]

When i initially changed it in SQL, i had the Expr... problem, so then i tried from the layout view, and got another Expr..., having now removed one it still gives me the Expr...

 

[Michael's Knight]

same happens after i've removed both and tried just from the layout view. there are no relationships directly to that field, i don't know what else could be confusing it.

 

[Uncle Gizmo]

Try removing one of the BookDetailsTable.Edition from the order by clause

 

[Michael's Knight]

how do i do that? i am unfamiliar with the clause.

 

[Uncle Gizmo]

'Change:-
ORDER BY BookDetailsTable.BookTitle, BookDetailsTable.Edition, BookDetailsTable.Edition, BookDetailsTable.Impression;

'To:-
ORDER BY BookDetailsTable.BookTitle, BookDetailsTable.Edition, BookDetailsTable.Impression;

 

[Michael's Knight]

i moved it, and now it says Expr1001, should i try remaking the field in the Table, and then try all this again in the query?

 

[Uncle Gizmo]

Extract your modified SQL again as per previous linked to instructions, BUT this time Place the code within Code Tags. See the advanced message reply box, look for a button >>> # <<< for which the tools tips say:- Wrap CODE TAGS around selected Text.

Recreating it from scratch is also a good idea, but I would like to see what's wrong.

 

[Michael's Knight]

here is the sql

SELECT BookDetailsTable.BookTitle, BookDetailsTable.Edition, BookDetailsTable.Impression, BookDetailsTable.AuthorID, BookDetailsTable.FormatID, BookDetailsTable.DateOfEditionRelease, BookDetailsTable.DateOfImpression, BookDetailsTable.PrinterCompanyID, BookDetailsTable.PublisherOfRecordID, BookDetailsTable.USRetail, BookDetailsTable.USWholesale, BookDetailsTable.PrintCost, BookDetailsTable.CanadaRetail, BookDetailsTable.CanadaWholesale, BookDetailsTable.UKRetail, BookDetailsTable.UKWholesale, BookDetailsTable.Edition, BookDetailsTable.[13DigitISBN], BookDetailsTable.[10DigitISBN], BookDetailsTable.BookSizeID, BookDetailsTable.ColorID, BookDetailsTable.DateOfCompletion, BookDetailsTable.[DatePublished/StreetDate], BookDetailsTable.CopyrightHolder, BookDetailsTable.ProjectStatus, BookDetailsTable.EuropeRetail, BookDetailsTable.EuropeWholesale, BookDetailsTable.USWholesaleDiscount, BookDetailsTable.CanadaWholesaleDiscount, BookDetailsTable.UKWholesaleDiscount, BookDetailsTable.EuropeWholesaleDiscount, BookDetailsTable.USManufactureCost, BookDetailsTable.EuropeManufactureCost, BookDetailsTable.UKManufactureCost, BookDetailsTable.CanadaManufactureCost, BookDetailsTable.Notes
FROM BookDetailsTable
ORDER BY BookDetailsTable.BookTitle, BookDetailsTable.Edition, BookDetailsTable.Edition, BookDetailsTable.Impression;

 

[Uncle Gizmo]

You haven't changed the Order By clause.....

HERE:-
ORDER BY BookDetailsTable.BookTitle, BookDetailsTable.Edition, BookDetailsTable.Edition, BookDetailsTable.Impression;

See previous instructions.

 

[Michael's Knight]

Like this... still Expr1001

SELECT BookDetailsTable.BookTitle, BookDetailsTable.Edition, BookDetailsTable.Impression, BookDetailsTable.AuthorID, BookDetailsTable.FormatID, BookDetailsTable.DateOfEditionRelease, BookDetailsTable.DateOfImpression, BookDetailsTable.PrinterCompanyID, BookDetailsTable.PublisherOfRecordID, BookDetailsTable.USRetail, BookDetailsTable.USWholesale, BookDetailsTable.PrintCost, BookDetailsTable.CanadaRetail, BookDetailsTable.CanadaWholesale, BookDetailsTable.UKRetail, BookDetailsTable.UKWholesale, BookDetailsTable.Edition, BookDetailsTable.[13DigitISBN], BookDetailsTable.[10DigitISBN], BookDetailsTable.BookSizeID, BookDetailsTable.ColorID, BookDetailsTable.DateOfCompletion, BookDetailsTable.[DatePublished/StreetDate], BookDetailsTable.CopyrightHolder, BookDetailsTable.ProjectStatus, BookDetailsTable.EuropeRetail, BookDetailsTable.EuropeWholesale, BookDetailsTable.USWholesaleDiscount, BookDetailsTable.CanadaWholesaleDiscount, BookDetailsTable.UKWholesaleDiscount, BookDetailsTable.EuropeWholesaleDiscount, BookDetailsTable.USManufactureCost, BookDetailsTable.EuropeManufactureCost, BookDetailsTable.UKManufactureCost, BookDetailsTable.CanadaManufactureCost, BookDetailsTable.Notes
FROM BookDetailsTable
ORDER BY BookDetailsTable.BookTitle, BookDetailsTable.Edition, BookDetailsTable.Impression;

 

[Uncle Gizmo]

You've got edition in there twice...

 

[Uncle Gizmo]

Counting BookDetailsTable.BookTitle, as the first term, BookDetailsTable.Edition, as the second term, etc.....

Then the 2nd term and the 17th term are the same. Delete one....

 

[Michael's Knight]

Genius! That was it! I'll take this as a lesson to be thoroughly meticulous in the future... Thank you so much for your time!