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

[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

[Allen Browne]

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.

[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.

[Brian]

"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.