Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014
    Posts
    98

    Unanswered: "Microsoft does not recognize as valid field or expression".

    I am using this big crosstab query and trying to build a report based on it but when I run the report it gives me error "Microsoft does not recognize as valid field or expression". I have stated in the crosstab query the parameters of start date and end date. Query works absolutely fine. Its only the report which gives me this error. One thing which I noticed while making another crosstab is that when some of the fields doesn't have value report gives me this error.


    Code:
    PARAMETERS [Forms]![frmSummaryofOpenIssue]![startDate] DateTime, [Forms]![frmSummaryofOpenIssue]![ENDDate] DateTime;
     TRANSFORM Count([Ucondition].TagID) AS CountOfTagID
     SELECT ManagerReport.Center, ManagerReport.Department, ManagerReport.ClosedIssuesByCenter.[CountOFAudit], ManagerReport.OpenIssueByCenter.[CountOfAudit], ManagerReport.CountOfHighPriority, ManagerReport.CountOfMediumPriority, ManagerReport.CountOfLowPriority
     FROM ManagerReport INNER JOIN [UCondition] ON (ManagerReport.Center = [UCondition].Center) AND (ManagerReport.Department = [UCondition].Department)
     WHERE ((([UCondition].Date) Between [Forms]![frmSummaryofOpenIssue]![startDate] And [Forms]![frmSummaryofOpenIssue]![EndDate]))
     GROUP BY ManagerReport.Center, ManagerReport.Department, ManagerReport.ClosedIssuesByCenter.[CountOfAudit], ManagerReport.OpenIssueByCenter.[CountOfAudit], ManagerReport.CountOfHighPriority, ManagerReport.CountOfMediumPriority, ManagerReport.CountOfLowPriority
     PIVOT IIf(DateDiff("d",[TargetDate],Date())<0,"On Target",IIf(DateDiff("d",[TargetDate],Date())<8,"7 Days Past Due",IIf(DateDiff("d",[TargetDate],Date())<22,"21 Days Past Due",IIf(DateDiff("d",[TargetDate],Date())>30,"30+ Days Past Due","On Target"))));

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    I think its the PIVOT and the IIF() 's

    why not create a Query that add the "On Target" , "7 Days Past Due" , "21 Days Past Due" to a new feild in a query

    then do the TRANSFORM on that new query using PIVOT [newfeildinquery]
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Or instead of using iif
    either do that formatting in the query consumer, ie the report or form that is using this
    or join to another table and pull the text value from there.

    ...temprementally id be minded yo use a table, especially if there is more than one data consumer for this query. I also prefer allowing users the option to rejig things like this as they see fit with no further developer intervention required.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •