Crosstab Limited by Form Dates

[Jimmy]

Hello,

Thank you for helping.

I have designed a crosstab query that runs correctly
until I attempt to limit the records contained in the
base query by dates displayed on a form. The specific
error message that I receive is:

"The Microsoft Jet database engine does not
recognize '[FORMS]![frmReporting]![dteBegin]' as a valid
field name or expression."

Any ideas will be greatly appreciated.

TIA,

Jimmy

 

[Roger Carlson]

You need to add your parameter to the Parameters list. In SQL, the
Parameters statement comes before the Transform. The Parameter must include
the prompt and the datatype expected. For example:

PARAMETERS [Enter Author] Text ( 255 );
TRANSFORM First(Authors.AuID) AS FirstOfAuID
SELECT Authors.AuName, First(Authors.AuID) AS [Total Of AuID]
FROM Authors
WHERE (((Authors.AuName)=[Enter Author]))
GROUP BY Authors.AuName
PIVOT Authors.AuPhone;

Now, in the QBE grid (query builder) you can specify the parameter by going
to Query > Parameters... in the menu bar of the Design View. This will
allow you to enter the parameter specifications. After that, it should work.

 

[Dejan]

Cross tab query can not have any parameters.

Try creating a query before cross tab, and save it under
name qryBeforeXtab. That query should use parameter
[FORMS]![frmReporting]![dteBegin].

Then use qryBeforeXtab as a base for xcross tab.

 

[Jimmy]

Wow, I never would have thought of that.

I will give it a try. Thank you for helping.

Jim

-----Original Message-----
You need to add your parameter to the Parameters list. In SQL, the
Parameters statement comes before the Transform. The Parameter must include
the prompt and the datatype expected. For example:

PARAMETERS [Enter Author] Text ( 255 );
TRANSFORM First(Authors.AuID) AS FirstOfAuID
SELECT Authors.AuName, First(Authors.AuID) AS [Total Of AuID]
FROM Authors
WHERE (((Authors.AuName)=[Enter Author]))
GROUP BY Authors.AuName
PIVOT Authors.AuPhone;

Now, in the QBE grid (query builder) you can specify the parameter by going
to Query > Parameters... in the menu bar of the Design View. This will
allow you to enter the parameter specifications. After that, it should work.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hello,

Thank you for helping.

I have designed a crosstab query that runs correctly
until I attempt to limit the records contained in the
base query by dates displayed on a form. The specific
error message that I receive is:

"The Microsoft Jet database engine does not
recognize '[FORMS]![frmReporting]![dteBegin]' as a valid
field name or expression."

Any ideas will be greatly appreciated.

TIA,

Jimmy

.

 

[Jimmy]

Thank you for helping.

I have already tried this technique, with no luck.

It really is weird. Any other ideas?

Thanks,

Jim

-----Original Message-----
Cross tab query can not have any parameters.

Try creating a query before cross tab, and save it under
name qryBeforeXtab. That query should use parameter
[FORMS]![frmReporting]![dteBegin].

Then use qryBeforeXtab as a base for xcross tab.

-----Original Message-----
Hello,

Thank you for helping.

I have designed a crosstab query that runs correctly
until I attempt to limit the records contained in the
base query by dates displayed on a form. The specific
error message that I receive is:

"The Microsoft Jet database engine does not
recognize '[FORMS]![frmReporting]![dteBegin]' as a valid
field name or expression."

Any ideas will be greatly appreciated.

TIA,

Jimmy
.

.

 

[Jimmy]

Roger,

Entering the parameters did the trick. Thank you very
much for the help.

Dejan,

I entered the parameters in the query that my crosstab
was based on. So, yes, you were correct. The parameters
should not have been entered in the crosstab itself.

Thank you, both,

Jim