Re: Use crosstab column heading in a calculation

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You must always define the data type of all parameters in crosstab queries.
Select Query->Parameters and enter
[Forms]![frmSwaps]![txtSwapID] datatype

BTW: without reading all your other postings, the solution for your first
posting is to create a query with this sql
TRANSFORM
First([tblSwapParameters].[SpotMonth]*[tblSwapParameters_1].[SpotMonth]) AS
Expr1
SELECT tblSwapParameters.SpotMonth
FROM tblSwapParameters, tblSwapParameters AS tblSwapParameters_1
GROUP BY tblSwapParameters.SpotMonth
PIVOT tblSwapParameters_1.SpotMonth;



--
Duane Hookom
MS Access MVP
--

"Bill R via AccessMonster.com" <u9289@uwe> wrote in message
news:5a83cfa40ea4a@xxxxxx
> PS:
>
> The main form is open in form view and qryParameters runs perfectly using
> the
> same criteria. But any crosstab query I design, whether it uses it's own
> criteria or uses a query that contains the criteria, generates the same
> "...
> does not recognize..." msg.
>
> Bill R wrote:
>>OK, Plan "H":
>>
>>I am now trying to create a temp table based on the crosstab query as
>>follows:
>>
>>TRANSFORM Sum(SpotMonth) AS CalcMonth
>>SELECT SpotMonth, Float, sigma
>>FROM tblSwapParameters
>>WHERE swapid=[Forms]![frmSwaps]![txtSwapID]
>>GROUP BY SpotMonth, Float, sigma
>>PIVOT SpotMonth;
>>
>>I want to save this query as "qryMatrix" and use it in a query called
>>"qryMakeTblMatrix". However, when I run the above crosstab query, I get
>>the
>>following msg:
>>"the Microsoft Jet Engine does not recognize
>>'[Forms]![frmSwaps]![txtSwapID]'
>>as a valid field name or expression"
>>I was trying to do this in a slightly different scenario, using
>>qryParameters
>>which filtered tblSwapParemeters using the same criteria. qryParameters
>>ran
>>just fine and delivered a recordset based on the control txtSwapID on the
>>main form. However, when I ran a crosstab query as above, except using
>>qryParameters and leaving out the WHERE clause, I got the same msg. The
>>above
>>query was a vain attempt to get the records more directly.
>>
>>Any suggestions would be deeply appreciated.
>>
>>>First you need to check the help and see if a crosstab will handle 145
>>>columns.
>>>
>>[quoted text clipped - 7 lines]
>>>>
>>>> Bill
>>
>
> --
> Bill Reed
>
> "If you can't laugh at yoursel, laugh at somebody else"
>
> Message posted via http://www.accessmonster.com


.

Relevant Pages

  • Re: Dynamic Reporting based on Parameterized Crosstab Query
    ... Since the column headings are all dates at a regular interval, ... charge date charged hours are associated with. ... on the first 3 rowwise fields of the crosstab query. ...
    (microsoft.public.access.reports)
  • Re: Cross tab query construction with Subqueries
    ... I constructed a couple of sample Tables and a bunch of Queries based on them, to illustrate the use both of Crosstab Queries to display what I think you want, and of ordinary Select Queries that do the same kind of thing, though less concisely. ... It looks a bit like a Crosstab Query but is a bit lengthier. ... placing only one revenue source on a line. ...
    (microsoft.public.access.queries)
  • Re: Unbound Report
    ... Since your error message stated something about a crosstab, ... crosstab query as the error message suggested? ... > My subreport derives from qry_breast_adj_sum which has 1 ... > the same as our main report... ...
    (microsoft.public.access.reports)
  • Re: Crosstab question
    ... If the crosstab query has criteria such as: ... so the selection on the combo box is what is searched in the ...
    (microsoft.public.access.formscoding)
  • Re: Rows from left to right
    ... Change to a Crosstab query. ... > Access adds Crosstab and Total rows to the grid. ... >the crosstab as the source for a report. ...
    (microsoft.public.access.reports)