Re: [MS_AccessPros] Crosstab Error: "Database Engine does not recognize field name or expression."

 

adding on...

the field alias needs to be different than any fieldname

cLng(-9999) AS ItemIDnew

or whatever you want the no value to be
it only matters what the names are in the first select for the union query anyway --

On 5/24/2016 4:28 PM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:

perhaps try

UNION SELECT

cLng(0) AS ItemID
?

If ItemID is missing then doesn't it need a value?

On 5/24/2016 1:21 PM, 'Bill Mosca' wrmosca@comcast.net [MS_Access_Professionals] wrote:

Try taking out the alias. You are not changing the field name so maybe the engine is getting confused. Also, re-check your two subqueries to make sure you spelled all the field names correctly.

 

SELECT

qryItemTimeStatus.ItemID,

qryItemTimeStatus.ItemName,

qryItemTimeStatus.TimeSysStatus_FK AS TimeStatus,

qryItemTimeStatus.TotalMinutes

FROM qryItemTimeStatus

UNION SELECT

qryItemTimeStatus_Missing.ItemID,

qryItemTimeStatus_Missing.ItemName,

qryItemTimeStatus_Missing.TimeStatus AS TimeStatus,

qryItemTimeStatus_Missing.TotalMinutes

FROM qryItemTimeStatus_Missing;

 

 

 

Regards,
Bill Mosca,
Founder, MS_Access_Professionals
That'll do IT http://thatlldoit.com
MS Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852

My Nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, May 24, 2016 7:22 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Crosstab Error: "Database Engine does not recognize field name or expression."

 

 

I have a simple union query

 

SELECT 

qryItemTimeStatus.ItemID AS ItemID, 

qryItemTimeStatus.ItemName, 

qryItemTimeStatus.TimeSysStatus_FK AS TimeStatus, 

qryItemTimeStatus.TotalMinutes

FROM 

qryItemTimeStatus

UNION SELECT

qryItemTimeStatus_Missing.ItemID AS ItemID, 

qryItemTimeStatus_Missing.ItemName, 

qryItemTimeStatus_Missing.TimeStatus AS TimeStatus, 

qryItemTimeStatus_Missing.TotalMinutes

FROM 

qryItemTimeStatus_Missing;

 

 

In the original query TotalMinutes is a calculated field

 

I then want to use a crosstab to show the total minutes for each time category

 

TRANSFORM 

  First(qryItemTimeStatus_All.[TotalMinutes]) AS FirstOfTotalMinutes

SELECT 

 qryItemTimeStatus_All.[ItemID], 

 qryItemTimeStatus_All.[ItemName], 

 First(qryItemTimeStatus_All.[TotalMinutes]) AS [Total Of TotalMinutes]

FROM 

 qryItemTimeStatus_All

GROUP BY 

 qryItemTimeStatus_All.[ItemID], 

 qryItemTimeStatus_All.[ItemName]

PIVOT [TimeSysStatus_FK];

 

This gives me an error "The microsoft engine does not recognize the field or expression [itemID]".  If However i make a table from my union query the cross tab works fine.  I have googled this error and it only points to parameter references, which I have none.  Any help would be appreciated.

__._,_.___

---

Posted by: crystal 8 <strive4peace2008@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___