SQL error

skwilliams

Registered User.
Local time
Today, 05:46
Joined
Jan 18, 2002
Messages
516
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?
 
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.
 
I followed your instructions, but the query is still returning all records.
 
skwilliams said:
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.
 
dcx693 said:
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.
 
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
 
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.
 
skwilliams said:
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?
 
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 form you are referring to is open, isn't it?
 
skwilliams said:
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)...
 
aaron.orrell said:
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.
 

Users who are viewing this thread

Back
Top Bottom