The Microsoft Jet database engine does not recognize

[skwilliams]

I have a database with a query pulling data from a table. The criteria is WkNo = Forms!frmManRun!Text2. The week number is stored in the WkNo field in the table.

Text2 is a form field on an unbound form with a button to run the query.

Therefore, I can enter a week number in the Text2 field and click the button to run a query which returns results for just that week.

The problem is when I run the query, I receive this error:

The Microsoft Jet database engine does not recognize '[Forms]![frmManRun]![Text2]' as a valid field name or expression.

 

[Rich]

Define the Parameters of the criteria

 

[skwilliams]

Here's the parameter I'm using in the query.

TRANSFORM Nz(Sum([tblAccum].[COUNT]))+0 AS SumOfCOUNT
SELECT tblAccum.Type, tblAccum.RecDate, Sum(tblAccum.COUNT) AS TOTAL
FROM tblAccum
WHERE (((DatePart("yyyy",[RecDate]))="2004") AND ((tblAccum.DOW)="2") AND ((tblAccum.WkNo)=[Forms]![frmManRun]![Text2]))
GROUP BY tblAccum.Type, tblAccum.RecDate, tblAccum.DOW, tblAccum.WkNo
ORDER BY tblAccum.RecDate
PIVOT tblAccum.ProdCode;

 

[Rich]

There are no Parameters defined as shown in the SQL you've posted

 

[skwilliams]

I set this parameter:
PARAMETERS [Forms]![frmManRun]![Text2]

Now when I input a week number in Text2 and run the query, I get this:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variable.

 

[R6Flyer]

Change the first line of your SQL to:

TRANSFORM Nz(Sum([tblAccum].[COUNT]),0) AS SumOfCOUNT