Crosstab parameter datatype definition

  • Thread starter Thread starter Bill R via AccessMonster.com
  • Start date Start date
B

Bill R via AccessMonster.com

I am aware from previous posts of mine that parameter data types must be
defined in crosstab queries. I had understood that data derived from a
control in an open form required this definition. However, the following
query, at this stage of development, is using data directly derived from
underlying queries, along with numbers inserted for the calculation. Yet I
still get the "Data type mismatch in criteria expression" error.

I hope someone can spot the cause of the error.

Here is the sql:

Underlying queries:

qryHtRtCurves (qryCurve1 and qryCurve2 are simple select statements on the
same table):
SELECT qryCurve1.SpotMonth, qryCurve1.Volatility AS PwrVol, qryCurve1.Float
AS PwrPrice, qryCurve1.Factor AS Hrs, qryCurve2.Volatility AS GasVol,
qryCurve2.Float AS GasPrice
FROM qryCurve2 INNER JOIN qryCurve1 ON qryCurve2.SpotMonth = qryCurve1.
SpotMonth;

Params3:
SELECT qryHtRtCurves.SpotMonth AS CalcMonth, Sqr([PwrVol]*[PwrVol]+([GasVol]*
[GasPrice]/(7.5*[GasPrice]+0))^2-2*0.75*[PwrVol]*[GasVol]*[GasPrice]/(7.5*
[GasPrice]+0)) AS SpreadVol
FROM qryHtRtCurves;

Crosstab:
TRANSFORM IIf(First([SpotMonth]/IIf([CalcMonth]=0,1,[CalcMonth]))<1,0,
(PwrPrice-(7.5*GasPrice))*Exp(-0.15*CalcMonth/12)+12*(1-Exp(-0.
15*CalcMonth/12))+1.645*SpreadVol*Sqr((1-Exp(-2*0.15*CalcMonth/12))/(2*0.15)))
AS Expr1
SELECT Params.SpotMonth, Params.PwrVol, Params.PwrPrice, Params.Hrs, Params.
GasVol, Params.GasPrice, Params3.SpreadVol
FROM qryHtRtCurves AS Params, Params3
WHERE (((Params3.CalcMonth)>0))
GROUP BY Params.SpotMonth, Params.PwrVol, Params.PwrPrice, Params.Hrs, Params.
GasVol, Params.GasPrice, Params3.SpreadVol
PIVOT Params3.CalcMonth;
 
OK. I added a Parameter line to my SQL:

PARAMETERS Params3.SpreadVol IEEEDouble;
TRANSFORM IIf(First([SpotMonth]/IIf([CalcMonth]=0,1,[CalcMonth]))<1,0,
(PwrPrice-(7.5*GasPrice))*Exp(-0.15*CalcMonth/12)+12*(1-Exp(-0.
15*CalcMonth/12))+1.645*SpreadVol*Sqr((1-Exp(-2*0.15*CalcMonth/12))/(2*0.15)))
AS Expr1
SELECT Params.SpotMonth, Params.PwrVol, Params.PwrPrice, Params.Hrs, Params.
GasVol, Params.GasPrice, Params3.SpreadVol
FROM qryHtRtCurves AS Params, Params3
GROUP BY Params.SpotMonth, Params.PwrVol, Params.PwrPrice, Params.Hrs, Params.
GasVol, Params.GasPrice, Params3.SpreadVol
PIVOT Params3.CalcMonth;

This seems to have gotten me past the "Data Type..." error, however, I am now
getting the "You tried to execute a query that does not include the specified
expression (the calculation in the Transform statement) as part of an
aggregate function.". I am trying to understand how my calculation could
violate the rule. The only field in the calculation that is not included in
the Group By line is CalcMonth, but that is transposed in the Pivot line and
can't be grouped. If I try to include the calculation in the group by line, I
get "Cannot have aggregate function in GROUP BY clause.", which would seem
to indicate that it is "part of an aggregate function".

I'm baffled!

Bill said:
I am aware from previous posts of mine that parameter data types must be
defined in crosstab queries. I had understood that data derived from a
control in an open form required this definition. However, the following
query, at this stage of development, is using data directly derived from
underlying queries, along with numbers inserted for the calculation. Yet I
still get the "Data type mismatch in criteria expression" error.

I hope someone can spot the cause of the error.

Here is the sql:

Underlying queries:

qryHtRtCurves (qryCurve1 and qryCurve2 are simple select statements on the
same table):
SELECT qryCurve1.SpotMonth, qryCurve1.Volatility AS PwrVol, qryCurve1.Float
AS PwrPrice, qryCurve1.Factor AS Hrs, qryCurve2.Volatility AS GasVol,
qryCurve2.Float AS GasPrice
FROM qryCurve2 INNER JOIN qryCurve1 ON qryCurve2.SpotMonth = qryCurve1.
SpotMonth;

Params3:
SELECT qryHtRtCurves.SpotMonth AS CalcMonth, Sqr([PwrVol]*[PwrVol]+([GasVol]*
[GasPrice]/(7.5*[GasPrice]+0))^2-2*0.75*[PwrVol]*[GasVol]*[GasPrice]/(7.5*
[GasPrice]+0)) AS SpreadVol
FROM qryHtRtCurves;

Crosstab:
TRANSFORM IIf(First([SpotMonth]/IIf([CalcMonth]=0,1,[CalcMonth]))<1,0,
(PwrPrice-(7.5*GasPrice))*Exp(-0.15*CalcMonth/12)+12*(1-Exp(-0.
15*CalcMonth/12))+1.645*SpreadVol*Sqr((1-Exp(-2*0.15*CalcMonth/12))/(2*0.15)))
AS Expr1
SELECT Params.SpotMonth, Params.PwrVol, Params.PwrPrice, Params.Hrs, Params.
GasVol, Params.GasPrice, Params3.SpreadVol
FROM qryHtRtCurves AS Params, Params3
WHERE (((Params3.CalcMonth)>0))
GROUP BY Params.SpotMonth, Params.PwrVol, Params.PwrPrice, Params.Hrs, Params.
GasVol, Params.GasPrice, Params3.SpreadVol
PIVOT Params3.CalcMonth;
Click to expand...

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via AccessMonster.com
 
I appreciate that this is a very complex query, but I hope it is possible to
accomplish what I'm setting out to do.

The crux of the issue is that the value "SpreadVol" must be calculated
vertically, such that for each SpotMonth in "Params" there is one SpreadVol.

However, this value must be used in a matrix of horizontal calculations, such
that although subsequent calculations in the Pivot that creates the matrix
are based on the vertical values by SpotMonth (PwrPrice, PwrVol, etc.), the
SpreadVol (calculated in the same record) used in the calculation for each
CalcMonth must be dependent upon the horizontal CalcMonth header (the same
set of month#'s pivoted by the crosstab query).

It's difficult to describe this in words, but the query on which all
calculations are based consists of 121 records, each containing SpotMonth (1-
121), PwrPrice, PwrVol, Hrs, GasPrice, and GasVol. Then, I query the same
datasource but this time only take the SpotMonth and a calculation, SpreadVol,
based on the values in each record. In the Crosstab query I place the
original query as "Params" and the 2nd query as "Params3", with no join.
This produces a matrix of 121 vertical records with 121 corresponding
horizontal "CalcMonth" calculations. The problem is the SpreadVol has to
remain constant for all the calculations in each CalcMonth field, such that
each of the 121 calculations in (i.e.) CalcMonth26 uses the SpreadVol for
SpotMonth26 even as the other variables in the calculations in the column
derive from the changing values in each of the 121 SpotMonth records being
calculated in CalcMonth26.

I believe I was getting the "Data Type mismatch..." error because I was not
including the SpreadVol in the group by statement. I can't include it because
it isn't "grouped by" SpotMonth. If I do that, then SpreadVol would have to
be constant horizontally and inconsistent vertically, the exact opposit of
what I need the calculation to do.

Hopefully, somebody out there will take up the challenge, or at least let me
know if this is possible.

Thanks,

Bill

Bill said:
I am aware from previous posts of mine that parameter data types must be
defined in crosstab queries. I had understood that data derived from a
control in an open form required this definition. However, the following
query, at this stage of development, is using data directly derived from
underlying queries, along with numbers inserted for the calculation. Yet I
still get the "Data type mismatch in criteria expression" error.

I hope someone can spot the cause of the error.

Here is the sql:

Underlying queries:

qryHtRtCurves (qryCurve1 and qryCurve2 are simple select statements on the
same table):
SELECT qryCurve1.SpotMonth, qryCurve1.Volatility AS PwrVol, qryCurve1.Float
AS PwrPrice, qryCurve1.Factor AS Hrs, qryCurve2.Volatility AS GasVol,
qryCurve2.Float AS GasPrice
FROM qryCurve2 INNER JOIN qryCurve1 ON qryCurve2.SpotMonth = qryCurve1.
SpotMonth;

Params3:
SELECT qryHtRtCurves.SpotMonth AS CalcMonth, Sqr([PwrVol]*[PwrVol]+([GasVol]*
[GasPrice]/(7.5*[GasPrice]+0))^2-2*0.75*[PwrVol]*[GasVol]*[GasPrice]/(7.5*
[GasPrice]+0)) AS SpreadVol
FROM qryHtRtCurves;

Crosstab:
TRANSFORM IIf(First([SpotMonth]/IIf([CalcMonth]=0,1,[CalcMonth]))<1,0,
(PwrPrice-(7.5*GasPrice))*Exp(-0.15*CalcMonth/12)+12*(1-Exp(-0.
15*CalcMonth/12))+1.645*SpreadVol*Sqr((1-Exp(-2*0.15*CalcMonth/12))/(2*0.15)))
AS Expr1
SELECT Params.SpotMonth, Params.PwrVol, Params.PwrPrice, Params.Hrs, Params.
GasVol, Params.GasPrice, Params3.SpreadVol
FROM qryHtRtCurves AS Params, Params3
WHERE (((Params3.CalcMonth)>0))
GROUP BY Params.SpotMonth, Params.PwrVol, Params.PwrPrice, Params.Hrs, Params.
GasVol, Params.GasPrice, Params3.SpreadVol
PIVOT Params3.CalcMonth;
Click to expand...
 
Back
Top