Crosstab error when using a [start date] criteria 1

[knuckelhead]

This is a 3 query deal. The first query collects the records based on a [start date] and [end date] of invoice date. The 2nd Qry works fine and displays the result. I then tried to make a Crosstab off of the 2nd Qry.

However, i get "microsoft Jet engine does not recognize "[start date]" as a valid field name or expression."

So the crosstab Qry conks out. Any ideas short of doing a Make Table in the 2nd Qry and making the crosstab work off of the Make table?

thanks

 

[PHV]

Simply add the parameter clause in your crosstab query:
PARAMETERS [start date] DateTime;
TRANSFORM ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[knuckelhead]

I am not sure what to do. In this 3 qry deal, the 1st qry is a get's all the INVOICES based on a date range, Start and End dates. Each Qry1 record has a customerID and formulaID. I am trying to make this Qry1 as the starting point for many other Qry's.

Then, Qry2 collapses Qry #1 to just formulaID. It does not need CustomerID. Hence, the invoice date field is not used in Qry2 after the "totals" collape. Qry2 adds a few more fields from other tables, most notably 3 Price Levels records for each Formula.

I next wanted to do a Qry3 crosstab off of the Qry2. I wanted to have the crosstab row heading as the formulaID and the 3 Price Levels as the crosstab columns going across.
Once again, the invoice date is not needed in Qry2 or Qry3 but technically comes from the Qry1 [start date] and [end date] criteria.

am i doing this well?
thanks

 

[PHV]

When editing your crosstab query (Qry3) go to the SQL view pane and declare all parameters used in all queries the crosstab depends on:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[knuckelhead]

Holy Cow. Now i get it. I never go to the Sql.

I now have the following in my Sql:

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Avg([3655Qry].UnitSellPrice) AS AvgOfUnitSellPrice
SELECT [3655Qry].FormulaID, [3655Qry].FormulaName, Avg([3655Qry].UnitSellPrice) AS [Total Of UnitSellPrice]
FROM 3655Qry
GROUP BY [3655Qry].FormulaID, [3655Qry].FormulaName
PIVOT [3655Qry].PriceLevel;

I now get passed the "Dates" issue. But it now stops at my final criteria line in Qry1. which is the LIKE statement that you helped me on last week. it is in the PackTypeID field of Qry1.

Like "*" & [Enter PackTypeID or Enter for ALL]

So the crosstab Qry3 stops and says:
"the microsoft Jet engine does not recognize "[enter PacktypeID or Enter for ALL]as a valid field or expression"

So i guess i need another item in my SQL ??
This is interesting. When my Qry3 is back in normal design mode, a person would not know that the "hidden" Sql is making the qry3 work.

Please advise on this last deal. thanks

 

[PHV]

PARAMETERS [start date] DateTime, [end date] DateTime
, [enter PacktypeID or Enter for ALL] Text(255);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[knuckelhead]

Holy cow, it woiks! You are a genius. I will put a note in the Qry property to remind me that there is special Sql programming. For some reason, nothing unusual shows in the plain Qry mode.

I will donate to the club for this wonderful information.
Knucklehead

 

[knuckelhead]

fyi thanks to PHV: Here is what worked for me in Sql mode. this is just for documentation for anyone else.

In my Qry in the Sql mode, I have:

PARAMETERS [start date] DateTime, [end date] DateTime, [enter PacktypeID or Enter for ALL] Text ( 255 );
TRANSFORM Avg([3655Qry].UnitSellPrice) AS AvgOfUnitSellPrice
SELECT [3655Qry].FormulaID, [3655Qry].FormulaName, Avg([3655Qry].UnitSellPrice) AS [Total Of UnitSellPrice]
FROM 3655Qry
GROUP BY [3655Qry].FormulaID, [3655Qry].FormulaName
PIVOT [3655Qry].PriceLevel;


knucklehead