SELECT tblCoilReceiving.crCustomerID, Sum(tblLifts.lCalcNet) AS Shipped, Val(Format([bolShipDate],"yyyy")) AS ByYear
FROM tblCoilReceiving LEFT JOIN (tblBillOfLading RIGHT JOIN tblLifts ON tblBillOfLading.bolBillOfLadingID = tblLifts.lBillOfLadingID) ON tblCoilReceiving.crCoilID = tblLifts.lCoilID
GROUP BY tblCoilReceiving.crCustomerID, Val(Format([bolShipDate],"yyyy"))
HAVING (((Val(Format([bolShipDate],"yyyy")))>0))
ORDER BY tblCoilReceiving.crCustomerID, Val(Format([bolShipDate],"yyyy"));
FROM tblCoilReceiving LEFT JOIN (tblBillOfLading RIGHT JOIN tblLifts ON tblBillOfLading.bolBillOfLadingID = tblLifts.lBillOfLadingID) ON tblCoilReceiving.crCoilID = tblLifts.lCoilID
GROUP BY tblCoilReceiving.crCustomerID, Val(Format([bolShipDate],"yyyy"))
HAVING (((Val(Format([bolShipDate],"yyyy")))>0))
ORDER BY tblCoilReceiving.crCustomerID, Val(Format([bolShipDate],"yyyy"));
...Now, if you remove that field, Shipped, from the Query it does not delete that field from Order By of the Query (see purple oval above) and subsequently, the next time you open the Query or run the Report it is going to prompt you for that value. To fix, just open the Query in Design View and delete it, no more prompt.
OrderBy OR Order By...
When you try to open a report it prompts you for the Parameter Value for tblYourTable.ytYourField. Only problem is that field is not part of the Query or Report, anymore. So why is it asking?
Since you know you removed it AND you just finished checking to make sure it's really removed... let's go check the Query Properties window (see A)...
When you try to open a report it prompts you for the Parameter Value for tblYourTable.ytYourField. Only problem is that field is not part of the Query or Report, anymore. So why is it asking?
Since you know you removed it AND you just finished checking to make sure it's really removed... let's go check the Query Properties window (see A)...
While the SELECT query below is ordered by the Order By...
...If you open the Query and Sort By the Data Sort buttons on the Toolbar (see purple circle to right), the Query will place that sort order in Order By in the Query Properties window (see A), which in this case is Shipped (see purple oval below)...
The difficult I do immediately, the impossible takes a little bit longer.
Queries
| Tips (Main) |
| Home |
| Using Parameters in a Crosstab Query |
| Action Query Errors |
| Memo field is truncated when exported to Excel |
| Query Joins |
| Append and/or Update Data to Table Created Dynamically |