Hi Everyone
I have been having an issue with a cross tab query for a while now that I just cant seem to get my head around
the problem is that the criteria set in the parameters does not work and shows all results
this is the SQL of my Query
Code:
PARAMETERS [forms]![frmTestSheetDetails]![ProductionOrderID] Short, [forms]![frmTestSheetDetails]![TestSheetID] Short, [forms]![frmTestSheetDetails]![ProductionOrdertxtbox] Text ( 255 );
TRANSFORM Sum(Nz([tbltestsresults.TestResult],0)) AS Expr1
SELECT tblTestsResults.TestSheetID, tblTestsResults.ReelNumber AS [Reel Number], tblTestsResults.RemainingWeight AS [Reel Weight], tblTestsResults.TestDate AS [Tested On], tblTestsResults.PartNumber AS [Part Number], tblTestsResults.ProductionOrderNumber AS [Production Number], tblProductionOrders.Customer, tblTestSheet.Description, tblTestSheet.Material, tblTestSheet.Specification, tblTestSheet.RevisionNumber AS [Revision Number], tblTestsResults.Area AS [Production Process]
FROM (tblTestsResults INNER JOIN tblProductionOrders ON tblTestsResults.ProductionOrderID = tblProductionOrders.ProductionOrderID) INNER JOIN tblTestSheet ON tblTestsResults.TestSheetID = tblTestSheet.TestSheetID
WHERE (((tblTestsResults.ReelStatus) Like "Prime Reel") AND ((tblTestsResults.Archived)=False))
GROUP BY tblProductionOrders.ProductionOrderID, tblTestsResults.ProductionOrderNumber, tblTestsResults.TestSheetID, tblTestsResults.ReelNumber, tblTestsResults.Archived, tblTestsResults.PartNumberID, tblTestsResults.RemainingWeight, tblTestsResults.TestDate, tblTestsResults.PartNumber, tblTestsResults.ProductionOrderNumber, tblProductionOrders.Customer, tblTestSheet.Description, tblTestSheet.Description, tblTestSheet.Material, tblTestSheet.Specification, tblTestSheet.RevisionNumber, tblTestsResults.Area, tblTestsResults.ReelStatus
PIVOT tblTestsResults.TestName;
And this is the text that's entered into the parameters screen
Parameter |
Data Type |
[forms]![frmTestSheetDetails]![ProductionOrderID] |
Integer |
[forms]![frmTestSheetDetails]![TestSheetID] |
Integer |
[forms]![frmTestSheetDetails]![ProductionOrdertxtbox] |
Short Text |
The Problem that I have is that when the query runs by clicking the run tab in the query designer, I get asked to enter the parameters but when doing so I get shown all the result's, the parameters are not filtering the results by the parameters set
Any ideas, I guess I'm not declaring the parameter correctly but I cant for the life of me work out what I'm doing wrong
it appears that the only criteria that is working is this
Code:
WHERE (((tblTestsResults.ReelStatus) Like "Prime Reel") AND ((tblTestsResults.Archived)=False))
because if I change this line to
Code:
WHERE (((tblTestsResults.ReelStatus) Like "IR Reel") AND ((tblTestsResults.Archived)=False))
this criteria works as expected by just showing a the IR Reel (Internal Rejects) but all ProductionOrderID,TestSheetID andProductionOrdertxtbox results are shown no matter what is entered
any help would be wonderful
Many thanks
Steve