Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    jet db engine does not recognize... (Access97)

    I have a query that retrieves data based on the start date and end date the user selects in combo boxes.
    frmSelectData.combo6 = Start Date and frmSelectData.combo8 = End Date.
    I use this query as the foundation for several other queries on which reports are based. Some of these are crosstab queries. For some reason, when I made this latest crosstab query where I Average and Sum some returned values by quarter, I get a message that states:

    The Microsoft Jet database engine does not recognize '[Forms].[frmSelectData].[Combo6]' as a valid field name or expression.

    I have replaced the "." with the "!"symbol with the same results. (ie. [Forms]![frmSelectData]![Combo6])
    Can anyone think of any reason why some of my queries work and this one does not?

    Many thanks,
    aap2

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: jet db engine does not recognize... (Access97)

    In the design view, have you change the total line for your date fields to Where ?
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: jet db engine does not recognize... (Access97)

    I have changed the query Total line to "Where" as you suggested and had the same result.

    In the query design grid of the query that does not work, the only reference to date is as follows:
    Field: Expr1: "Qtr " & Format([qryComparisonSearchStep1].[Date],"q")
    Table:
    Total: Group By
    Crosstab: Column Heading

    This query is based on another query in which my combo boxes are referenced as:
    Field: Date
    Table: tblTransactionDates
    Total: Group By (I changed this to Where, as you suggest and had the same result)
    Sort:
    Criteria: Between [forms]![frmSelectData]![Combo6] And [forms]![frmSelectData]![combo8]

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: jet db engine does not recognize... (Access97)

    I was thinking that the selection of the dates were made in the crosstab query.
    Do your first query select anything else than the dates ?
    You could add the date field to the crosstab query and it is there in the crosstab query that I mean that you have to change the Group By to Where, not in the first query.

    Otherwise you can try these:
    In the design view of the crosstab query, select the menu Query, Parameters and in the window enter in the first column, the full name of the textboxes ( [forms]![frmSelectData]![Combo6] and [forms]![frmSelectData]![combo8] ) in the column parameter and Date/time in the column Data Type. On two line, of course.
    I'm not sure if you have to do this in the crosstab query or in the first select query. Try first in the crosstab query.
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: jet db engine does not recognize... (Access97)

    When you start stacking queries like this, criteria referring to form controls will be OK until you get to the crosstab. It doesn't like criteria like that in underlying queries unless the criteria are also in the query parameters for the same underlying query. In other words, it isn't enough to have the reference to the comboboxes in the criteria line of your query, you also have to put them into the query's parameters. Then the crosstab should stop complaining ... at least about the parameters.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: jet db engine does not recognize... (Access97)

    Thanks for your assistance. Do you mean that in the underlying queries I should add a field like this?

    Field: StartDate:[forms]![frmSelectData]![Combo6]
    and
    Field: EndDate:[forms]![frmSelectData]![Combo8]
    Is this what you mean when you said to add them to the query parameters? And are you referring to the underlying queries or the final crosstab or both?

    Thanks,
    aap2

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

    Re: jet db engine does not recognize... (Access97)

    No, in such a case you must declare the parameters explicitly in the underlying query:
    <UL><LI>Open the underlying query in design view.
    <LI>Select Query/Parameters... (the last item in the Query menu)
    <LI>Enter [Forms]![frmSelectData]![Combo6] in the parameter column and select Date/time in the data type column of the first row.
    <LI>Enter [Forms]![frmSelectData]![Combo8] in the parameter column and select Date/time in the data type column of the second row.
    <LI>Click OK.[/list]

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: jet db engine does not recognize... (Access97)

    You can do it in the crosstab query also.
    Francois

  9. #9
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: jet db engine does not recognize... (Access97)

    Thank you all, problem solved. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •