SQL error

[skwilliams]

I have a crosstab query with a date criteria. Here's the SQL statement:

TRANSFORM Nz(Sum([tblAccum].[COUNT]))+0 AS SumOfCOUNT
SELECT tblAccum.Type, tblAccum.RecDate, Sum(tblAccum.COUNT) AS TOTAL
FROM tblAccum
WHERE ((tblAccum.RecDate) = [Forms]![frmMain]![Text17])
GROUP BY tblAccum.Type, tblAccum.RecDate
PIVOT tblAccum.ProdCode;

I'm receiving this error message.

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

I've verified the correct form and unbound textbox name.

An idea where my SQL is wrong?

 

[dcx693]

Did you go to the query in design view and define your parameters? You need to do this for a pivot report, unlike "regular" parameter queries (where it's optional). Go the Query menu, choose Parameters, enter [Forms]![frmMain]![Text17] and then the data type.

 

[skwilliams]

I followed your instructions, but the query is still returning all records.

 

[dcx693]

I followed your instructions, but the query is still returning all records.

Is it still giving you this error message:

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

 

[skwilliams]

Is it still giving you this error message:

No, it seems to be processing properly, it's just not doing what it's supposed to.

 

[ladydazy]

WHERE ((tblAccum.RecDate) = [Forms]![frmMain]![Text17])

Shouldn't that query be

WHERE ((tblAccum.RecDate) =[Forms]![frmMain].[Text17])

take out the last ! and see if that works. Me thinks you're trying to reference a combo box (Text17) in another form (frmMain)...so it should a . not a ! and also take out any spaces in that side of the query.

Lemme know if that works

 

[skwilliams]

That worked.

Thanks a million!

 

[skwilliams]

The query works fine, but now the report that's generated from the query won't come up. I get the error

The Microsoft Jet Database Engine does not recognize " as a valid field name or expression.

 

[ladydazy]

The query works fine, but now the report that's generated from the query won't come up. I get the error

The Microsoft Jet Database Engine does not recognize " as a valid field name or expression.

Do you have a " some where in that query?

 

[skwilliams]

Here's the SQL statement from the query.

PARAMETERS [Forms]![frmMain]![Text17] Text ( 255 );
TRANSFORM Nz(Sum([tblAccum].[COUNT]))+0 AS SumOfCOUNT
SELECT [tblAccum].[Type], [tblAccum].[RecDate], Sum([tblAccum].[COUNT]) AS TOTAL
FROM tblAccum
WHERE (([tblAccum].[RecDate])=[Forms]![frmMain].[Text17])
GROUP BY [tblAccum].[Type], [tblAccum].[RecDate]
PIVOT [tblAccum].[ProdCode];

The query runs fine, it just when I run the report from the query that I get this error message.

 

[Mile-O]

The form you are referring to is open, isn't it?

 

[skwilliams]

Yes, it is.

 

[aaron.orrell]

Here's the SQL statement from the query.

PARAMETERS [Forms]![frmMain]![Text17] Text ( 255 );
TRANSFORM Nz(Sum([tblAccum].[COUNT]))+0 AS SumOfCOUNT
SELECT [tblAccum].[Type], [tblAccum].[RecDate], Sum([tblAccum].[COUNT]) AS TOTAL
FROM tblAccum
WHERE (([tblAccum].[RecDate])=[Forms]![frmMain].[Text17])
GROUP BY [tblAccum].[Type], [tblAccum].[RecDate]
PIVOT [tblAccum].[ProdCode];

The query runs fine, it just when I run the report from the query that I get this error message.

The parameters declaration also has the bang(!) operator instead of the period before [Text17] Text (255)...

 

[Mile-O]

The parameters declaration also has the bang(!) operator instead of the period before [Text17] Text (255)...

There's nothing wrong with late binding in a query.