469,631 Members | 1,480 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

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.
Aug 21 '09 #1
7 22473
266 Expert 100+
Please post the code of your cross tab queries.

Aug 21 '09 #2
Here's the code to the crosstab Query
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [forms]![frmReports]![terryear] Short;
  2. TRANSFORM Sum(Int([forecast]*[Ypercent])*[minofnprice]) AS Plants
  3. SELECT qryLastYearPercents.Territory AS cterr, tblMonthlyForecast.PlantID, tblMonthlyForecast.FYear, qryLastYearPercents.YPercent, qryLastYearPercents.DiscPercent, qryPlantPrices.MinOfnprice, Sum([Forecast]*[minofnprice]) AS SumofForecast
  4. FROM (tblMonthlyForecast INNER JOIN qryPlantPrices ON tblMonthlyForecast.PlantID = qryPlantPrices.citem) LEFT JOIN qryLastYearPercents ON tblMonthlyForecast.PlantID = qryLastYearPercents.citem
  5. WHERE (((tblMonthlyForecast.FYear)=[forms]![frmReports]![terryear]))
  6. GROUP BY qryLastYearPercents.Territory, tblMonthlyForecast.PlantID, tblMonthlyForecast.FYear, qryLastYearPercents.YPercent, qryLastYearPercents.DiscPercent, qryPlantPrices.MinOfnprice
  7. ORDER BY Right("0" & [FMonth],2) & " " & [monthname]
  8. PIVOT Right("0" & [FMonth],2) & " " & [monthname];
Aug 21 '09 #3
Here's the final query after that
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM qryForecastByTerritoryPrices LEFT JOIN dbo_icitem ON qryForecastByTerritoryPrices.PlantID = dbo_icitem.citem
  3. WHERE (((dbo_icitem.citemno) Like "*st4-72*"))
  4. ORDER BY dbo_icitem.cdescript;
Aug 21 '09 #4
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.
Aug 21 '09 #5
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
Aug 21 '09 #6
8,800 Expert 8TB
Unless I browsed through this too quickly, it appears to me that the Field [moddisc] is not even Referenced in either Query.
Aug 22 '09 #7
Stewart Ross
2,545 Expert Mod 2GB
I note that you have a reference to a form control in the WHERE clause of the crosstab:

Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. Public Function FormFieldValue(FormName As String, FieldName As String)
  2.     FormFieldValue = Forms(FormName).Controls(FieldName)
  3. 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

Expand|Select|Wrap|Line Numbers
  1. WHERE (((tblMonthlyForecast.FYear)=FormFieldValue("frmReports", "terryear")))

Aug 22 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by NerdRunner | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.