Results 1 to 3 of 3
  1. #1
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    CrossTab Parameter Issue

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You declare the parameters but then don't use them in the WHERE clause.

    Review this article with a warning about using Text type declaration. http://allenbrowne.com/bug-13.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi June

    You Are star

    many many thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 16
    Last Post: 08-23-2013, 01:11 PM
  2. Replies: 7
    Last Post: 03-10-2013, 11:29 AM
  3. Crosstab Query Parameter
    By BLD21 in forum Queries
    Replies: 1
    Last Post: 06-06-2011, 09:08 AM
  4. Crosstab issue
    By teedee in forum Queries
    Replies: 5
    Last Post: 02-07-2011, 10:49 AM
  5. Parameter in Crosstab Query
    By RandyG in forum Queries
    Replies: 4
    Last Post: 09-30-2009, 06:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums