jet db engine does not recognize… (Access97)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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]

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

No, in such a case you must declare the parameters explicitly in the underlying query:

• Open the underlying query in design view.
• Select Query/Parameters… (the last item in the Query menu)
• Enter [Forms]![frmSelectData]![Combo6] in the parameter column and select Date/time in the data type column of the first row.
• Enter [Forms]![frmSelectData]![Combo8] in the parameter column and select Date/time in the data type column of the second row.
• Click OK.
  [/list]

Reply | Quote

Thank you all, problem solved.

Reply | Quote

You can do it in the crosstab query also.

Reply | Quote