Access does not recognize " as a valid field name

[Ares6881]

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]

Please post the code of your cross tab queries.

-AJ

[Ares6881]

Here's the code to the crosstab Query

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];

[Ares6881]

Here's the final query after that

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;

[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.

[Ares6881]

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

[ADezii]

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.

[Stewart Ross]

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

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.

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

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

Regards

Stewart