Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Invalid field name/expression (Access 2000)

    This is driving me insane!

    I have a form (FSelectDates), where a user can select a start date and an end date.
    I have a query (Q1-part1) that uses the two list boxes on FSelectDate via a 'between..and' criterion to extract records.
    I have a crosstab query (Q1) that uses Q1-part1 as its source.

    When I run Q1, Access says: "The Microsoft Jet database engine does not recognize '[Forms]![FSelectDate].[StartDate]' as a valid field name or expression."

    If I change Q1 to a select query, it runs fine.

    How can I make this work without changing my crosstab query?

    Thanks in advance,

    Brandon

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid field name/expression (Access 2000)

    Open Q1-part1 in design view.
    Select Query | Parameters...
    Enter the start date parameter exactly as it occurs in the criteria: [Forms]![FSelectDate].[StartDate]
    Set the data type to Date/Time.
    In the next row, enter the end date parameter exactly as it occurs in the criteria: probably [Forms]![FSelectDate].[EndDate]
    Set the data type to Date/Time.
    Click OK.
    Save Q1-part1.
    Now try your crosstab query again.

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid field name/expression (Access 2000)

    Once again, you've saved me some wrinkles and hair.

    I would ask why Access can recognize the date fields in the Select format but not in the Crosstab format, but I'm not sure I really want to know...

    Thanks again,

    Brandon.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid field name/expression (Access 2000)

    See MSKB article ACC2000: Error When Running Crosstab Query with a Parameter (thanks to <!profile=SteveH>SteveH<!/profile> for pointing to this article in a recent 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
  •