Access does not recognize " as a valid field name

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Ares6881
    New Member
    • Jul 2007
    • 20

    Access does not recognize " as a valid field name

    This error has been driving me a little bit nuts, I have 2 reports going off the same query, the query pulls in data from a crosstab query. One of the reports is working just fine, but whenever I try to run the fourth one I get the error (the microsoft office access database engine does not recognize " as a valid field name or expression). The strangest part is that the query that feeds into the report runs just fine on its own. The only big difference between the two is they are sorted by different fields.
  • ajalwaysus
    Recognized Expert Contributor
    • Jul 2009
    • 266

    #2
    Please post the code of your cross tab queries.

    -AJ

    Comment

    • Ares6881
      New Member
      • Jul 2007
      • 20

      #3
      Here's the code to the crosstab Query
      Code:
      PARAMETERS [forms]![frmReports]![terryear] Short;
      TRANSFORM Sum(Int([forecast]*[Ypercent])*[minofnprice]) AS Plants
      SELECT qryLastYearPercents.Territory AS cterr, tblMonthlyForecast.PlantID, tblMonthlyForecast.FYear, qryLastYearPercents.YPercent, qryLastYearPercents.DiscPercent, qryPlantPrices.MinOfnprice, Sum([Forecast]*[minofnprice]) AS SumofForecast
      FROM (tblMonthlyForecast INNER JOIN qryPlantPrices ON tblMonthlyForecast.PlantID = qryPlantPrices.citem) LEFT JOIN qryLastYearPercents ON tblMonthlyForecast.PlantID = qryLastYearPercents.citem
      WHERE (((tblMonthlyForecast.FYear)=[forms]![frmReports]![terryear]))
      GROUP BY qryLastYearPercents.Territory, tblMonthlyForecast.PlantID, tblMonthlyForecast.FYear, qryLastYearPercents.YPercent, qryLastYearPercents.DiscPercent, qryPlantPrices.MinOfnprice
      ORDER BY Right("0" & [FMonth],2) & " " & [monthname]
      PIVOT Right("0" & [FMonth],2) & " " & [monthname];

      Comment

      • Ares6881
        New Member
        • Jul 2007
        • 20

        #4
        Here's the final query after that
        Code:
        SELECT qryForecastByTerritoryPrices.cterr, qryForecastByTerritoryPrices.PlantID, qryForecastByTerritoryPrices.FYear, qryForecastByTerritoryPrices.YPercent, qryForecastByTerritoryPrices.DiscPercent, qryForecastByTerritoryPrices.MinOfnprice, qryForecastByTerritoryPrices.SumofForecast, qryForecastByTerritoryPrices.[01 January], qryForecastByTerritoryPrices.[02 February], qryForecastByTerritoryPrices.[03 March], qryForecastByTerritoryPrices.[04 April], qryForecastByTerritoryPrices.[05 May], qryForecastByTerritoryPrices.[06 June], qryForecastByTerritoryPrices.[07 July], qryForecastByTerritoryPrices.[08 August], qryForecastByTerritoryPrices.[09 September], qryForecastByTerritoryPrices.[10 October], qryForecastByTerritoryPrices.[11 November], qryForecastByTerritoryPrices.[12 December], dbo_icitem.cdescript
        FROM qryForecastByTerritoryPrices LEFT JOIN dbo_icitem ON qryForecastByTerritoryPrices.PlantID = dbo_icitem.citem
        WHERE (((dbo_icitem.citemno) Like "*st4-72*"))
        ORDER BY dbo_icitem.cdescript;

        Comment

        • Ares6881
          New Member
          • Jul 2007
          • 20

          #5
          I seem to have narrowed it down to two text boxes in the report, one is

          =IIf(IsNull([moddisc]),"Average discount for " & [Fyear]-1,"Modified Discount")

          the other is

          =IIf(IsNull([moddisc]),[DiscPercent],[moddisc]*100 & "%")

          the strange thing is I have these exact same boxes in another report that works fine. I have run a compact and repair, but that didn't do anything.

          Comment

          • Ares6881
            New Member
            • Jul 2007
            • 20

            #6
            I found the error, moddisc was part of a table I removed, by re-adding the table and the field it fixed the error. Still wish the error was more descriptive, but what can you do, it's MS ;P

            Comment

            • ADezii
              Recognized Expert Expert
              • Apr 2006
              • 8834

              #7
              Originally posted by Ares6881
              I seem to have narrowed it down to two text boxes in the report, one is

              =IIf(IsNull([moddisc]),"Average discount for " & [Fyear]-1,"Modified Discount")

              the other is

              =IIf(IsNull([moddisc]),[DiscPercent],[moddisc]*100 & "%")

              the strange thing is I have these exact same boxes in another report that works fine. I have run a compact and repair, but that didn't do anything.
              Unless I browsed through this too quickly, it appears to me that the Field [moddisc] is not even Referenced in either Query.

              Comment

              • Stewart Ross
                Recognized Expert Moderator Specialist
                • Feb 2008
                • 2545

                #8
                I note that you have a reference to a form control in the WHERE clause of the crosstab:

                Code:
                WHERE (((tblMonthlyForecast.FYear)=[forms]![frmReports]![terryear]))
                A known problem with Access crosstab queries is that references to form control values in WHERE clauses can and do fail, resulting in the error message you quoted - 'Access does not recognise xxxx as a valid field name or expression'. It often arises when adapting a SELECT query to become a CROSSTAB query - the Select WHERE clause works fine from the Query editor window, but change the query type to Crosstab and the same WHERE clause often fails.

                This lack of recognition of form control references also occurs when running non-crosstab queries from VBA code, though in that case VBA returns a different error message: 'too few parameters - expected 1'.

                It may well be that the field you mentioned which was missing - even though it appears to have nothing to do with your crosstab - is the sole cause of the failure of your report, in which case you don't need to do anything at all about your use of the form control reference. But, if you continue to experience the failure you mentioned in post #1 I'd replace the direct form control value, as I explain below.

                I use a custom VBA function to return form control values, as function references in WHERE clauses do not cause 'field name not recognised' errors in crosstabs.

                Code:
                Public Function FormFieldValue(FormName As String, FieldName As String)
                    FormFieldValue = Forms(FormName).Controls(FieldName)
                End Function
                You can place the function in any public code module (one visible in the Modules tab of the database window).

                Using the function above to return the value of the terryear control, your WHERE clause becomes

                Code:
                WHERE (((tblMonthlyForecast.FYear)=FormFieldValue("frmReports", "terryear")))
                Regards

                Stewart

                Comment

                Working...