Results 1 to 9 of 9

The Microsoft Access database engine does not recognize as a valid field name or expression. (Error

  1. #1
    hemantsogani is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3

    The Microsoft Access database engine does not recognize as a valid field name or expression. (Error

    When using a Form field as a criteria in a query, It works in one query but not in aoother
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,888
    A PDF of form is not useful to analyze issue. Provide queries and sample data. If you want to provide db, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,847
    Is the Form referenced (TrackNestlings) open when you run the Query...it needs to be or you'll get this error!And I'm not aware of any way to "run the form TrackNestlings with the parameters below" from a graphic displayed in a PDF file...is this something new?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,881
    Note the bit on parameters:

    http://allenbrowne.com/ser-67.html
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    244
    Another possibility is your combo box is not named Bird.

  6. #6
    hemantsogani is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3

    The database used

    I am attaching the database to be used.
    Attached Files Attached Files

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,881
    I can't open the file right now, on a mobile device. Is there a crosstab query involved? There appeared to be, and if so see post 4.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,888
    Advise not to use spaces in naming convention. So Start Date and End Date should be StartDate and EndDate.

    The CROSSTAB opens on its own. The SELECT with criteria opens on its own. But the query that joins them fails. Will need to use PARAMETERS clause in the SELECT query.

    The data range criteria is not set up yet. Date/time field has time component. I expect you will get inconsistent results unless you account for the time component.
    Between [Forms]![TrackNestlings]![StartDate] And [Forms]![TrackNestlings]![EndDate] & " 23:59:59"
    If there are multiple records for different times in same day, the CROSSTAB will summarize for each date and time - is that what you want? Or should summarize by date?

    PARAMETERS Forms!TrackNestlings!Bird Text ( 255 ), Forms!TrackNestlings!StartDate DateTime, Forms!TrackNestlings!EndDate DateTime;

    There is no need to actually open the two separate queries, just open the last one.

    Instead of opening queries, really should build a report. Users should not interact with tables and queries. Although, building a stable report based on CROSSTAB can be tricky.

    Why does it matter whether there are photos or comments when selecting records?

    Why even use Temp table?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    hemantsogani is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3
    Quote Originally Posted by June7 View Post
    Advise not to use spaces in naming convention. So Start Date and End Date should be StartDate and EndDate.

    The CROSSTAB opens on its own. The SELECT with criteria opens on its own. But the query that joins them fails. Will need to use PARAMETERS clause in the SELECT query.

    The data range criteria is not set up yet. Date/time field has time component. I expect you will get inconsistent results unless you account for the time component.
    Between [Forms]![TrackNestlings]![StartDate] And [Forms]![TrackNestlings]![EndDate] & " 23:59:59"
    If there are multiple records for different times in same day, the CROSSTAB will summarize for each date and time - is that what you want? Or should summarize by date?

    PARAMETERS Forms!TrackNestlings!Bird Text ( 255 ), Forms!TrackNestlings!StartDate DateTime, Forms!TrackNestlings!EndDate DateTime;

    There is no need to actually open the two separate queries, just open the last one.

    Instead of opening queries, really should build a report. Users should not interact with tables and queries. Although, building a stable report based on CROSSTAB can be tricky.

    Why does it matter whether there are photos or comments when selecting records?

    Why even use Temp table?
    Thanks. Using the PARAMETER clause resolved the problem.
    I had to use Temp table because I was getting the same error when I was creating a Crosstab query using the query. I don't have to use the table if I add PARAMETER clause in that query as well.
    I want to have one row for every Date & Time that is why I have included Date and time in the same field

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

Similar Threads

  1. Replies: 6
    Last Post: 11-07-2018, 04:38 AM
  2. Replies: 6
    Last Post: 02-23-2018, 11:16 AM
  3. Replies: 4
    Last Post: 01-24-2017, 08:32 AM
  4. Replies: 13
    Last Post: 05-03-2016, 07:44 AM
  5. Replies: 4
    Last Post: 05-22-2015, 01:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums