Results 1 to 14 of 14
  1. #1
    JL2309 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    18

    "The MS Access database engine does not recognize 'FEMA' as a valid field name or expression"

    I've been getting this error when trying to run a report whose record source is a crosstab query. The query runs fine on its own - I get the error message when attempting to run the report that uses it. Including the code for both query and report below - not too experienced so any help is much appreciated!!

    CROSSTAB QUERY:

    TRANSFORM Max(IIf([ValType]="UsualValue",[UsualValue],[MaxValue])) AS ByCatVal
    SELECT QRY_FEMA_UseLevels_20160429.FEMAno, GRAS3_27PrimNames.PrimaryName
    FROM CompULfrm_ValTypeCol, GRAS3_27PrimNames INNER JOIN QRY_FEMA_UseLevels_20160429 ON GRAS3_27PrimNames.[FEMAno] = QRY_FEMA_UseLevels_20160429.FEMAno
    GROUP BY QRY_FEMA_UseLevels_20160429.FEMAno, GRAS3_27PrimNames.PrimaryName


    PIVOT [ValType] & [FoodCategory];



    REPORT RECORD SOURCE:

    SELECT CompareUL_UsualMaxVals_ByCat_20160502.FEMAno, CompareUL_UsualMaxVals_ByCat_20160502.PrimaryName, CompareUL_UsualMaxVals_ByCat_20160502.CASno, CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueBaked Goods], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueBeverages, Alcoholic], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueBeverages, Non-alcoholic], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueBreakfast Cereals], CompareUL_UsualMaxVals_ByCat_20160502.MaxValueChee ses, CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueChewing Gum], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueCondiments and Relishes], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueConfections and Frostings], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueEgg Products], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueFats and Oils], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueFish Products], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueFrozen Dairy], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueFruit Ices], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueGelatins and Puddings], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueGranulated Sugar], CompareUL_UsualMaxVals_ByCat_20160502.MaxValueGrav ies, CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueHard Candy], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueImitation Dairy Products], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueInstant Coffee and Tea], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueJams and Jellies], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueMeat Products], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueMilk Products], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueNut Products], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueOther Grains], CompareUL_UsualMaxVals_ByCat_20160502.MaxValuePoul try, CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueProcessed Fruits], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueProcessed Vegetables], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueReconstituted Vegetables], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueSeasonings and Flavors], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueSnack Foods], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueSoft Candy], CompareUL_UsualMaxVals_ByCat_20160502.MaxValueSoup s, CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueSugar Substitutes], CompareUL_UsualMaxVals_ByCat_20160502.[MaxValueSweet Sauces], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueBaked Goods], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueBeverages, Alcoholic], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueBeverages, Non-alcoholic], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueBreakfast Cereals], CompareUL_UsualMaxVals_ByCat_20160502.UsualValueCh eeses, CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueChewing Gum], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueCondiments and Relishes], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueConfections and Frostings], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueEgg Products], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueFats and Oils], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueFish Products], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueFrozen Dairy], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueFruit Ices], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueGelatins and Puddings], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueGranulated Sugar], CompareUL_UsualMaxVals_ByCat_20160502.UsualValueGr avies, CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueHard Candy], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueImitation Dairy Products], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueInstant Coffee and Tea], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueJams and Jellies], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueMeat Products], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueMilk Products], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueNut Products], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueOther Grains], CompareUL_UsualMaxVals_ByCat_20160502.UsualValuePo ultry, CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueProcessed Fruits], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueProcessed Vegetables], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueReconstituted Vegetables], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueSeasonings and Flavors], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueSnack Foods], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueSoft Candy], CompareUL_UsualMaxVals_ByCat_20160502.UsualValueSo ups, CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueSugar Substitutes], CompareUL_UsualMaxVals_ByCat_20160502.[UsualValueSweet Sauces] FROM CompareUL_UsualMaxVals_ByCat_20160502;

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I don't see anything wrong with the queries. Check the controls on your report - one of them may have "FEMA" as the control source.

  3. #3
    JL2309 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    18
    Unfortunately that isn't it; all of the report controls are listed as "FEMAno" (there is no "FEMA") and there is no group/sort that could be screwing it up. Thank you though for your quick reply! Any other thoughts?

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Have you got any other VBA code in your report?

    I note that there are some extraneous blanks and missing square brackets in the report source SQL, but I suspect they are just transcriptions errors, and I don't think they would cause that particular error message.

  5. #5
    JL2309 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    18
    I don't believe so. I don't know any VBA and this was all the code in the SQL view of the report.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If the query runs OK, then does your report use grouping and/or sorting? if so, check the settings there

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Did you try running the report record source by itself, as a query? You should be able to cut-and-paste the report SQL into the SQL view of a new query.

    (Don't copy from what you have above, though - as I pointed out, it has errors in it)

  8. #8
    JL2309 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    18
    Hi! No grouping or sorting. There are no parameters set on the crosstab and I have a defined column heading with two row headings; my searching suggests that it may be something to do with using the crosstab as a source record for the report but I can't figure out where or how. There are also two subreports embedded in the report, but neither use "FEMA" as a field name or grouping/sort.

  9. #9
    JL2309 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    18
    Hi John - when I fix the errors in the code and paste into the query SQL view, it populates correctly. That must mean it is something within the report...?

  10. #10
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Re: extra spaces in the report sql: this is due to the forum adding a space at the 50 character point. Use code tags to prevent it.
    As John_G states, try running the report sql as a query. If it works, your report or one of its sub-reports likely has a control on it whose name is FEMA - assuming you are correct that no report/sub report control has a source whose field name is FEMA.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it may be because your report control sources do not match the columns generated by your crosstab - in which case you need to provide column headings - modify your crosstab to something like

    PIVOT [ValType] & [FoodCategory] IN (1,2,3)

    where 1,2,3 are the [ValType] & [FoodCategory] column headings you want to display.

    If you add new [ValType] & [FoodCategory], you will need to add a new control for them

    if [ValType] & [FoodCategory] are text then use

    IN ('1','2','3')

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    additional point on readability - use aliases rather than long table/query names


    e.g. FROM CompareUL_UsualMaxVals_ByCat_20160502 AS C

    or just

    FROM CompareUL_UsualMaxVals_ByCat_20160502 C


    then all your field name names would be declared as

    SELECT C.FEMAno, C.PrimaryName, C.CASno,....etc

    much easier to read

  13. #13
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    How are you running the report? Are you running it by clicking a button on a form? If so, what is the code in the On-Click property of the form?

  14. #14
    JL2309 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    18
    Micron, you were correct about the control names - both subreports had "FEMA" listed in the master link fields and needed to be updated. Thank you all for your responses, this was extremely helpful!

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

Similar Threads

  1. Replies: 4
    Last Post: 05-22-2015, 02:29 AM
  2. Replies: 1
    Last Post: 05-06-2015, 03:33 PM
  3. Replies: 2
    Last Post: 07-14-2014, 10:34 AM
  4. Replies: 5
    Last Post: 01-07-2014, 03:41 PM
  5. Replies: 1
    Last Post: 07-21-2009, 03:01 PM

Tags for this Thread

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