Union Query Throwing enter parameter value box

[TGirl]

I am having trouble with my union query because it keeps asking for a parameter value. However, if I click on OK it will go on and show me all the data. Ideas?

 SELECT dbo_Envio_Product_Sales_ORM.SalesOrderProcessNumber, dbo_Envio_Product_Sales_ORM.CommodityName, dbo_Envio_Product_Sales_ORM.VarietyDesc, dbo_Envio_Product_Sales_ORM.EnteredQuantity, dbo_Envio_Product_Sales_ORM.EnteredPrice, dbo_Envio_Product_Sales_ORM.BillTo_AccountCode, First(Str(Year([ShipOnDate]))) AS [Year], First(Trim(Str(Year([ShipOnDate]))) & "-" & Format(Month([ShipOnDate]),"00")) AS [Month], First(Str(DateValue([ShipOnDate]))) AS [Date], dbo_Envio_Product_Sales_ORM.Pack, dbo_Envio_Product_Sales_ORM.Type, dbo_Envio_Product_Sales_ORM.Size, dbo_Envio_Product_Sales_ORM.SKU, [EnteredQuantity]*[EnteredPrice] AS SalesAmt
FROM dbo_Envio_Product_Sales_ORM
GROUP BY dbo_Envio_Product_Sales_ORM.SalesOrderProcessNumber, dbo_Envio_Product_Sales_ORM.CommodityName, dbo_Envio_Product_Sales_ORM.VarietyDesc, dbo_Envio_Product_Sales_ORM.EnteredQuantity, dbo_Envio_Product_Sales_ORM.EnteredPrice, dbo_Envio_Product_Sales_ORM.BillTo_AccountCode, dbo_Envio_Product_Sales_ORM.Pack, dbo_Envio_Product_Sales_ORM.Type, dbo_Envio_Product_Sales_ORM.Size, dbo_Envio_Product_Sales_ORM.SKU, [EnteredQuantity]*[EnteredPrice]


UNION 


SELECT dbo_Envio_Product_Sales_ORM1.SalesOrderProcessNumber, dbo_Envio_Product_Sales_ORM1.CommodityName, dbo_Envio_Product_Sales_ORM1.VarietyDesc, dbo_Envio_Product_Sales_ORM1.EnteredQuantity, dbo_Envio_Product_Sales_ORM1.EnteredPrice, dbo_Envio_Product_Sales_ORM1.BillTo_AccountCode, First(Str(Year([ShipOnDate]))) AS [Year], First(Trim(Str(Year([ShipOnDate]))) & "-" & Format(Month([ShipOnDate]),"00")) AS [Month], First(Str(DateValue([ShipOnDate]))) AS [Date], dbo_Envio_Product_Sales_ORM1.Pack, dbo_Envio_Product_Sales_ORM1.Type, dbo_Envio_Product_Sales_ORM1.Size, dbo_Envio_Product_Sales_ORM1.SKU, [EnteredQuantity]*[EnteredPrice] AS SalesAmt
FROM dbo_Envio_Product_Sales_ORM1
GROUP BY dbo_Envio_Product_Sales_ORM1.SalesOrderProcessNumber, dbo_Envio_Product_Sales_ORM1.CommodityName, dbo_Envio_Product_Sales_ORM1.VarietyDesc, dbo_Envio_Product_Sales_ORM1.EnteredQuantity, dbo_Envio_Product_Sales_ORM1.EnteredPrice, dbo_Envio_Product_Sales_ORM1.BillTo_AccountCode, dbo_Envio_Product_Sales_ORM1.Pack, dbo_Envio_Product_Sales_ORM1.Type, dbo_Envio_Product_Sales_ORM1.Size, dbo_Envio_Product_Sales_ORM1.SKU, [EnteredQuantity]*[EnteredPrice];

[aytee111]

You are using that name, ShipOnDate, but the query doesn't recognize it as a valid field name. It must exist as a field on both tables.

[June7]

It's not finding a field called ShipOnDate in either dbo_Envio_Product_Sales_ORM or dbo_Envio_Product_Sales_ORM1. Use Null as placeholder for the table that doesn't have the field.

Why do you use DateValue() function to make sure ShipOnDate is a date then convert it to a string with Str()? Why convert extracted year to a string?

Would be wary of using First or Last aggregate functions, maybe you really need Min or Max.

Year and Date are reserved words. Should not use reserved words as names. Better would be ShipDate and ShipYear.

[TGirl]

ShipOnDate is in both the dbo_Envio_Product_Sales_ORM1 and dbo_Envio_Product_Sales_ORM.

I changed the Date name to ShippedDate and now it has two pop ups. One is the same as before and the other is asking for a date.

the ShipOnDate field is date and time but I need it to be just the date. What formula would you recommend?

[June7]

Did you change the alias names in both SELECT?

Only need to specify alias field names in the first SELECT line. Example:

SELECT field1 AS [somename], field2 AS [anothername] FROM table1
UNION SELECT field1, field2 FROM table2;

Why do you have two tables with same kind of data? Why not 1 table? Then would not need UNION.

DateValue() returns just the date part. Why do you then convert it to a string? Same for the Year extract.

You might need UNION ALL because just UNION won't allow duplicate records.

Why the query is not finding ShipOnDate fields is a mystery. I don't know your db to determine cause.

[roaftech]

...

Why the query is not finding ShipOnDate fields is a mystery.
...

One observation that might be relevant ...
I have spent hours trying to find missing field names, finally discovering that they were mentioned in the "conditional format" spec. Another possibility is in the "grouping and sorting" sub-menu.
Also applies (where appropriate) to Excel.