By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,604 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Access 2003: crosstab query won't use reference to form

P: n/a
DFS
I've been working around this for years (I believe), so I figured someone
here might know:

Why won't a crosstab query accept a value from a form reference?

TRANSFORM Format(First(QtrAvg),'Fixed') AS FirstQtrAvg
SELECT PropertyCode, Survey, Question
FROM SurveyData
WHERE PropertyCode = Forms.MainForm.Subform.Form.PropertyCode
GROUP BY PropertyCode, Survey, Question
PIVOT [Month] & '-' & [Year];
This crosstab query won't run at all. The error is "Jet db engine doesn't
recognize 'form reference' as a valid field name or expression.

Thanks

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
I'm not seeing that problem in A2003.

Double-check that the names are correct, by pressing Ctrl+G to open the
immediate window, and entering:
? Forms.MainForm.Subform.Form.PropertyCode
You may find that the Name of your subform control is not the same as the
name of the form that it loads (its SourceObject).

If that checks out, try declaring the parameter in the query.
In query design view, choose Parameters from the Query menu.
In the dialog, enter:
Forms.MainForm.Subform.Form.PropertyCode Long Integer
or whatever type is appropriate.

If you are still stuck, make sure that the Name AutoCorrect boxes are
unchecked under:
Tools | Options | General
and then compact the database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DFS" <no****@nospam.com> wrote in message
news:10*************@corp.supernews.com...
I've been working around this for years (I believe), so I figured someone
here might know:

Why won't a crosstab query accept a value from a form reference?

TRANSFORM Format(First(QtrAvg),'Fixed') AS FirstQtrAvg
SELECT PropertyCode, Survey, Question
FROM SurveyData
WHERE PropertyCode = Forms.MainForm.Subform.Form.PropertyCode
GROUP BY PropertyCode, Survey, Question
PIVOT [Month] & '-' & [Year];
This crosstab query won't run at all. The error is "Jet db engine doesn't
recognize 'form reference' as a valid field name or expression.

Nov 13 '05 #2

P: n/a
DFS
Allen Browne wrote:
I'm not seeing that problem in A2003.

Double-check that the names are correct, by pressing Ctrl+G to open
the immediate window, and entering:
? Forms.MainForm.Subform.Form.PropertyCode
You may find that the Name of your subform control is not the same as
the name of the form that it loads (its SourceObject).
Names are fine. Still won't work.

If that checks out, try declaring the parameter in the query.
In query design view, choose Parameters from the Query menu.
In the dialog, enter:
Forms.MainForm.Subform.Form.PropertyCode Long Integer
or whatever type is appropriate.
Parameters is grayed out in the Query menu, but I added the PARAMETERS line
to the beginning of the SQL statement and it worked.

My usual workaround is a function in the where clause, such as WHERE
PropertyCode = getPropertyCode()

which worked fine with no PARAMETERS statement.

But I was wondering (and still do actually) why the form reference in the
WHERE clause won't work (without a PARAMETER statement, which standard
SELECTs don't require). Guess it's just an Access foible.

Thanks Allen


If you are still stuck, make sure that the Name AutoCorrect boxes are
unchecked under:
Tools | Options | General
and then compact the database.
"DFS" <no****@nospam.com> wrote in message
news:10*************@corp.supernews.com...
I've been working around this for years (I believe), so I figured
someone here might know:

Why won't a crosstab query accept a value from a form reference?

TRANSFORM Format(First(QtrAvg),'Fixed') AS FirstQtrAvg
SELECT PropertyCode, Survey, Question
FROM SurveyData
WHERE PropertyCode = Forms.MainForm.Subform.Form.PropertyCode
GROUP BY PropertyCode, Survey, Question
PIVOT [Month] & '-' & [Year];
This crosstab query won't run at all. The error is "Jet db engine
doesn't recognize 'form reference' as a valid field name or
expression.

Nov 13 '05 #3

P: n/a

"DFS" <no****@nospam.com> wrote in message
news:10*************@corp.supernews.com...
But I was wondering (and still do actually) why the form reference in the
WHERE clause won't work (without a PARAMETER statement, which standard
SELECTs don't require). Guess it's just an Access foible.


This has been so since at least Access 97. It is well documented. See for
example KB articles 209778 and 91710.
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.