Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Error when usomg field name as criteria

0 views
Skip to first unread message

Craig Smith

unread,
Mar 30, 2000, 3:00:00 AM3/30/00
to
I am trying to run a simple crosstab query that uses a field on a form the filter the data. But I have been getting the following error :
 
 
"The Microsoft Jet database enginer does not recognize 'Forms]![qryStatsList]![StatsCode]' as a valid field name or expression."
 
I have checked another database of mine that uses a similar field name expression and that works fine.
 
Any help is gratefully received.
 
Thanks,
 
Craig Smith

John Smith

unread,
Mar 30, 2000, 3:00:00 AM3/30/00
to
If that is an exact copy from your query, you are missing an [ before Forms.
Alternatively, you could take out all of the brackets, which would also work.
If the original does not lack the bracket I can only suggest that you double
check that there is a control called StatsCode on a Form called qryStatsList in
your database. Could you have renamed or mis-spelt something?

"Craig Smith" <csm...@dixon-minsup.com.au> wrote in message
news:38e2e...@kastagir.senet.com.au...

Sandra Daigle

unread,
Mar 30, 2000, 3:00:00 AM3/30/00
to
Post the SQL of your actual query. It sounds as though you might have the form control name in quotes - just a guess but seeing the actual query might point out something else.
 
Sandra Daigle
I am trying to run a simple crosstab query that uses a field on a form the filter the data. But I have been getting the following error :
 
 
"The Microsoft Jet database enginer does not recognize 'Forms]![qryStatsList]![StatsCode]' as a valid field name or expression."
 
I have checked another database of mine that uses a similar field name expression and that works fine.
 
Any help is gratefully received.
 
Thanks,
 
Craig Smith

Craig Smith

unread,
Mar 31, 2000, 3:00:00 AM3/31/00
to
As you could probably tell from the subject line, I was very tired when posting this message.
 
I did mis-spell the field name in the posted message - it would help if I wasn't an idiot.
 
The following reference is the EXACT criteria I am trying to use :
Like [Forms]![frmPreviewSales]![Text4] & "*"
 
This works perfectly in an append query in the same database, but will not work in a crosstab query. I arrived at this formula by using the "build..." option when right clicking in the QBE grid and double clicking on the field from the forms section. I'm satisfied that the field name is correct.
 
SQL code is below using the above reference in my crosstab query :
 
TRANSFORM Sum(ProductData.Qty) AS SumOfQty
SELECT ProductData.StatsCode, ProductData.ItemNo, ProductData.Description
FROM ProductData LEFT JOIN statsdescription ON ProductData.Stats2 = statsdescription.StatsCode
WHERE (((ProductData.StatsCode) Like [Forms]![frmPreviewSales]![Text4] & "*") AND ((ProductData.CustNo) Not Like "dix*" And (ProductData.CustNo) Not Like "tra*"))
GROUP BY ProductData.StatsCode, ProductData.ItemNo, ProductData.Description
ORDER BY ProductData.StatsCode, ProductData.ItemNo
PIVOT ProductData.period;
This query when run brings up the same error (exactly as typed) :
 
The Microsoft Jet database engine does not recognize '[Forms]![qryStatsList]![StatsCode]' as a valid field name or expression.(Error 3070).
 
I have made crosstab queries based on this type of reference in Access 2, so this has me stumped.
 
Regards,
 
Craig Smith

Sandra Daigle

unread,
Mar 31, 2000, 3:00:00 AM3/31/00
to
I created an test to duplicate your problem and sure enough, I got the same error message. I fiddled around a bit and then discovered that for crosstab queries, you must declare any parameters. In the QBE, under Query choose Parameters, type in the form control reference and give it a datatype of text. That should solve your problem.
 
HTH,
 
Sandra Daigle

Craig Smith

unread,
Apr 1, 2000, 3:00:00 AM4/1/00
to
Oh you clever thing !!!!
 
Thankyou thankyou thankyou !!.    I was absolutely pulling my hair out with this problem.
 
Your suggestion work perfectly.
 
Thanks again.
 
Craig
Sandra Daigle <SMDa...@mindspring.com> wrote in message news:eOppU7wm$GA.254@cppssbbsa04...
0 new messages