MS Jet DB engine does not recognize 'my query name' as a valid name

[Ruth]

In Access 2003 running Vista.

I have a query that is based on a crosstab query. The crosstab is
based on a filter query that has criteria that comes from a combo box
on a form. The crosstab works fine. When I try to run the query that
my report is based on, I get the error message, "The Microsoft Jet
database engine does not recognize 'qryAllRespPct' as a valid field
name or expression." "qryAllRespPct" is the name of the query that is
trying to run (!).

I have tried removing the ID field that uses the combo box as filter
criterion, but that didn't help.

Here is the SQL. Ideas?

SELECT qxtAllRespCount.SrvName, qxtAllRespCount.QstnGroup,
qxtAllRespCount.QstnLvl1, qxtAllRespCount.QstnText, qxtAllRespCount.
[Total Of RspnsID] AS TotResp, IIf([Never] Is Null,0,[Never]) AS
NeverZ, [NeverZ]/[TotResp] AS [Never%], IIf([Rarely] Is Null,0,
[Rarely]) AS RarelyZ, [RarelyZ]/[TotResp] AS [Rarely%], IIf
([Sometimes] Is Null,0,[Sometimes]) AS SometimesZ, [SometimesZ]/
[TotResp] AS [Somtimes%], IIf([Usually] Is Null,0,[Usually]) AS
UsuallyZ, [UsuallyZ]/[TotResp] AS [Usually%], IIf([Always] Is Null,0,
[Always]) AS AlwaysZ, [AlwaysZ]/[TotResp] AS [Always%], IIf([No
Response] Is Null,0,[No Response]) AS NoResponseZ, [NoResponseZ]/
[TotResp] AS [NoResponse%]
FROM qxtAllRespCount;

I wonder if this could be a Vista issue since this worked last year
running on an XP machine. I didn't make any changes that would affect
the query. I'm stumped!

Thanks,
Ruth

 

[Douglas J. Steele]

Does the error message really say qryAllRespPct, as opposed to
qxtAllRespCount (the query that's in your SQL)?

If so, it sounds as though qxtAllRespCount has an error in it, or else that
your report is referring to qryAllRespPct somewhere.

 

[Ruth]

Hi Doug,
No, that's really what the message box says when I try to run the
query (which I ran independently of the report). I checked the SQL to
see if there was some reference to itself, but didn't find anything.
If I try to open the report that the query is based on, I get the
message "The Microsoft Jet database engine does not recognize '' as a
valid field name or expression."

Also I'm pretty sure that there aren't any other references to
qryAllRespPct anywhere else. The query name originally had % at the
end instead of Pct. That was the first thing I changed as I thought
that the % might be an "offending" character.

Another potential issue was that while adding a new survey results
(for testing new survey for current year), Access shut down (three
times) with only the error message, "Access has stopped working.
Searching for a solution," and then just shut down. I went back to the
backup DB I created before I began current work, and the queries still
work fine using only 2007 data. I imported the new tables to another
copy of the backup to see if that would fix anything. The survey for
2007 still produces the report, but for the 2009 survey, I get the
same results as I did when I originally posted this problem. It makes
me think that I have some reference to the original survey hard coded
somewhere, but I can't see it. If you have any ideas on how I can
trouble shoot that, I would appreciate it.

I guess my next step after this would be to repeat the import process
on my XP machine to see if Vista really is having anything to do with
the error.

Other thoughts?

Thanks so much,
Ruth

 

[Ruth]

I think I'm making some headway. I went back to the filter query that
I set up with the reference to the combo box selection (provides the
records for the crosstab query). The criteria for the combo box
selection was put in both the query grid and the parameters window.
When I delete from the grid and rely on the parameter setting for
filtering, the query crosstab runs but doesn't filter. If I take it
out of the parameters window and leave it only in the query grid, I
get the error message, "The Microsoft Jet database engine does not
recognize '[Forms]![frmReptMenu]![cboSrvID]' as a valid field name or
expression." This is the form field that I was using to filter
results.

I think this is the culprit! Any ideas?

Thanks,
Ruth

 

[Douglas J. Steele]

Especially in Crosstab queries, you need to declare all parameters
explicitly.

Assuming you're using Access 2003 or earlier, while the crosstab query's
open in Design view, select Parameters from the Query menu, enter
[Forms]![frmReptMenu]![cboSrvID] in the Parameter column, and the correct
data type in the Date Type column.

That'll add something like

PARAMETERS [Forms]![frmReptMenu]![cboSrvID] Long;

at the beginning of your query's SQL, before the SELECT statement.

 

[David W. Fenton]

Especially in Crosstab queries, you need to declare all parameters
explicitly.

I've found that with Access 2003 (and possibly earlier) you must
declare as parameters *any* reference to a form control used as
criteria or in the SELECT statement of any query, regardless of
type. It stands to reason that this is good practice, but I've also
found that it's the only way to get reliable results from queries
with form control references.

 

[Ruth]

Hi Doug,
I checked the queries and reports and removed all references to the
form combo box as the criteria. Then I added the combo box as a
parameter as you suggested in the crosstab query (I even double-
checked the data type, which is Integer). The query and report run
without error, but the results aren't filtered. Moving the parameter
to the query for the report, qryAllRespPct, produces the same result.
No error message, but no filtering.

Is there a way to set the filter on the report instead of the query?
I've not had much success on my own in the past trying to use the
Filter property of forms or reports. I always get the syntax wrong, so
I've always relied on putting the filter criteria in a query.

Thanks for your help!
Ruth

 

[Douglas J. Steele]

You can pass a Where clause as part of the OpenReport method.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug,
I checked the queries and reports and removed all references to the
form combo box as the criteria. Then I added the combo box as a
parameter as you suggested in the crosstab query (I even double-
checked the data type, which is Integer). The query and report run
without error, but the results aren't filtered. Moving the parameter
to the query for the report, qryAllRespPct, produces the same result.
No error message, but no filtering.

Is there a way to set the filter on the report instead of the query?
I've not had much success on my own in the past trying to use the
Filter property of forms or reports. I always get the syntax wrong, so
I've always relied on putting the filter criteria in a query.

Thanks for your help!
Ruth