Originally Posted by
NightWalker
I am not seeing what I have wrong in second set of code.
Originally Posted by
NightWalker
I have changed to this but when I run it I get an "Enter parameter value" box that says qry_343sTested_Step1
Code:
SELECT
tbl_ProductionItem.ProductionItem,
tbl_ProductionItem.ProductionItemPartNumber,
qryTotalMetricsUnion.Total,
qryTotalMetricsUnion.Failed
FROM tbl_ProductionItem INNER JOIN qryTotalMetricsUnion ON tbl_ProductionItem.ProductionItemPartNumber = qryTotalMetricsUnion.PN
WHERE [qry_343sTested_Step1].[TestedDate] Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31))
AND Month([qry_343sTested_Step1].[TestedDate]) = [Enter month]
ORDER BY tbl_ProductionItem.ProductionItem, tbl_ProductionItem.ProductionItemPartNumber, qryTotalMetricsUnion.Date;
"qry_343sTested_Step1" is in the WHERE clause but NOT in the FROM clause....therefore you cannot use "qry_343sTested_Step1" in the WHERE clause.
"DATE" is a reserved word in Access and shouldn't be used as an object name. Also "Date" is an Access built in function.
----------------------------
First query, maybe........ (Untested!!)
Code:
SELECT
qry_SelDailyTested_Step1.TestedDate AS [Date],
qry_SelDailyTested_Step1.ProductionItemPartNumber AS PN,
qry_SelDailyTested_Step1.[Total Tested] AS Total,
IIf(IsNull([CountOfNumber of Fail]),0,[CountOfNumber of Fail]) AS Failed
FROM qry_SelDailyTested_Step1 LEFT JOIN qry_SelDailyTested_Step3 ON (qry_SelDailyTested_Step1.ProductionItemPartNumber = qry_SelDailyTested_Step3.ProductionItemPartNumber) AND (qry_SelDailyTested_Step1.TestedDate = qry_SelDailyTested_Step3.TestedDate)
WHERE Month(qry_SelDailyTested_Step1.TestedDate) = [Enter start Month (1 -12)] And Year(qry_SelDailyTested_Step1.TestedDate) = [Enter Year]
ORDER BY qry_SelDailyTested_Step1.ProductionItemPartNumber
UNION ALL
SELECT qry_343sTested_Step1.TestedDate AS [Date],
qry_343sTested_Step1.ProductionItemPartNumber AS PN,
qry_343sTested_Step1.[Total Tested] AS Total,
IIf(IsNull([CountOfPassFail]),0,[CountOfPassFail]) AS Failed
FROM qry_343sTested_Step1 LEFT JOIN qry_343sTested_Step3 ON (qry_343sTested_Step1.ProductionItemPartNumber = qry_343sTested_Step3.ProductionItemPartNumber) AND (qry_343sTested_Step1.TestedDate = qry_343sTested_Step3.TestedDate)
WHERE Month(qry_343sTested_Step1.TestedDate) = Enter start Month (1 -12)] And Year(qry_343sTested_Step1.TestedDate) = [Enter Year]
ORDER BY date;
I don't like parameter queries. Much better to have two text boxes on a form to get the month and year - easier to validate..
Again, shouldn't use "DATE" as a field/column name. Reserved word and an Access built in function.
Plus "Date" is not very descriptive - date of what???