Problem using parameters in crosstab queries

[Tim Bocek]

Hello,

I have a problem with parameters in crosstab queries -
well, actually queries that form the basis of crosstab
queries. I have several products, a table containing
every transaction, and need a summary of how much of each
product was sold on each day in a user-specified period.

The first step is a query that returns a product
description, the amount sold, and the date it was sold.
The date is limited to user-inputted starting and ending
dates. The condition field of the query builder reads ">=
[Enter Starting Date] And <=[Enter Ending Date]". This
query works fine.

However, then I used the crosstab wizard to create a query
with the products as the rows and the dates as the
columns. However, when I try to run this query I get the
error message "The Microsoft Jet database engine does not
recognize [Enter Starting Date] as a valid field or
expression." This is especially strange because it is
only once the last query is used in the crosstab that
Access complains about it!

I'm using Access 2003. Does anyone have any idea what's
going wrong, and how I can work around this?

Thanks!

 

[homer]

You need to create alias for those date columns.

 

[Guest]

I'm not sure what you mean - this is my first experience
with crosstabs. Could you explain better?

Thanks.

-----Original Message-----
You need to create alias for those date columns.

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

I have a problem with parameters in crosstab queries -
well, actually queries that form the basis of crosstab
queries. I have several products, a table containing
every transaction, and need a summary of how much of each
product was sold on each day in a user-specified period.

The first step is a query that returns a product
description, the amount sold, and the date it was sold.
The date is limited to user-inputted starting and ending
dates. The condition field of the query builder reads ">=
[Enter Starting Date] And <=[Enter Ending Date]". This
query works fine.

However, then I used the crosstab wizard to create a query
with the products as the rows and the dates as the
columns. However, when I try to run this query I get the
error message "The Microsoft Jet database engine does not
recognize [Enter Starting Date] as a valid field or
expression." This is especially strange because it is
only once the last query is used in the crosstab that
Access complains about it!

I'm using Access 2003. Does anyone have any idea what's
going wrong, and how I can work around this?

Thanks!
.

.

 

[Van T. Dinh]

You need to declare the DataType of the Parameters in CrossTab Query.

See the MSKB:

http://support.microsoft.com/?id=209778

 

[ramazan yetkin]

I'm not sure what you mean - this is my first experience
with crosstabs. Could you explain better?

Thanks.

-----Original Message-----
You need to create alias for those date columns.

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

I have a problem with parameters in crosstab queries -
well, actually queries that form the basis of crosstab
queries. I have several products, a table containing
every transaction, and need a summary of how much of each
product was sold on each day in a user-specified period.

The first step is a query that returns a product
description, the amount sold, and the date it was sold.
The date is limited to user-inputted starting and ending
dates. The condition field of the query builder reads ">=
[Enter Starting Date] And <=[Enter Ending Date]". This
query works fine.

However, then I used the crosstab wizard to create a query
with the products as the rows and the dates as the
columns. However, when I try to run this query I get the
error message "The Microsoft Jet database engine does not
recognize [Enter Starting Date] as a valid field or
expression." This is especially strange because it is
only once the last query is used in the crosstab that
Access complains about it!

I'm using Access 2003. Does anyone have any idea what's
going wrong, and how I can work around this?

Thanks!
.

.