Results 1 to 5 of 5
  1. #1
    Beginner72 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    12

    Getting error - Microsoft database engine does not recognize " as a valid field or expression


    This one is stumping me. I have a database set up in which one table tracks inter-store transfers between departments. I have a form created that will open a report based on a crosstab query of the data. The query is also structured in such a way that I have to input date criteria to pull the values from a range of dates. Everything works perfectly until I try to pull data for 2017. I suspected that there may be a problem with some records for 2017 but could not find anything that looked out of the ordinary. The other thing that I had noticed is that I can run the query and get the data but it just simply fails to generate the report for values with a date field of 2017. It is working fine for records with the date field being 2016 but not for those of 2017. The error message I get is - The Microsoft Access Database Engine does not recognize " as a valid field or expression. Does anyone have any thoughts on this one?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Sounds like something is null.
    either there are no records for 2017,
    or the criteria is reading null instead of 2017.

    since I don't know how you are running the query ,I can't say.
    are you using a form for the criteria?
    param query?

  3. #3
    Beginner72 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    12
    I am using a form to enter the criteria.

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    You state that you can run the query and get the data; A report can have calculations triggering - so the logic problem can be at the report level. I tend to agree with R256 in that the data itself has problems - either formats, null, divide by 0, etc. But in terms of troubleshooting if the error occurs at the report level and not its record set then you should copy that report and tactically dissect it to understand what is the cause.

  5. #5
    Beginner72 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    12
    Thanks for the suggestions. I certainly do appreciate it. I have come to learn that the majority of the problems I have had usually come on account of null values. After reading some of the replies I realized that in the query I did not have columns totalled, and that is why the query would run fine. In my report, however, I had column totals at the bottom. When Access tried to add the column values, it could not because some of the values were null. I adjusted my column totals using the Nz function and now everything works the way it should.

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

Similar Threads

  1. Replies: 13
    Last Post: 05-03-2016, 08:44 AM
  2. Replies: 2
    Last Post: 02-01-2016, 08:27 AM
  3. Replies: 4
    Last Post: 05-22-2015, 02:29 AM
  4. Replies: 1
    Last Post: 05-06-2015, 03:33 PM
  5. Replies: 1
    Last Post: 07-21-2009, 03:01 PM

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