Re: CrossTab Query with Parameter


Two possibilities (looking at it without seeing the app.):
- The parameters are not recognised as parameter, because in xTab1 I do not
see the PARAMETER keyword. If this is copy-pasted from your application,
then it seems to be missing. (Solve this in the query design, and choose
Query-Parameters from the menu. In the little box, type
[Forms]![repExecSum]![cboDept] in the left column, and the correct datatype
in the right column)

- In the cross-tab report, the parameter is not filled properly in the
'underlying query'. You'd have to look through the code of the report to
solve this (read it, understand it, and then you know how and what).
Possibly, this second one is caused by the first.

If this is not the case, I'd have to see the application to say something
meaningful about it.

As I allways tell my customers about computers, "Anything is possible, It's
just a matter of how much time (=money) you'd want to put in"

Good luck,

Bas Hartkamp.


<tizmagik@xxxxxxxxx> schreef in bericht
news:1148416897.599188.118790@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am having a lot of difficulty generating a CrossTab Query based
report. I have looked online for several tutorials and whatnot but I
have not been able to really find what I'm looking for, nor have I been
able to adapt other people's solutions/tips to fit what I need. If
anyone could please help me with the following it would be really
appreciated, thank you!

I need to generate a Report (say: repCrossTab) that grabs it's data
from the CrossTab Query (say: xTabFinal) which utilizes a previous
Query (say: xTabSum) that Joins two other Queries (say: xTab1 and
xTab2). xTab1 contains two parameters that will be filled out by the
user on a form (say: [Forms]![repExecSum]![cboDept] and
[Forms]![repExecSum]![cboRelease])

The following is working SQL code to generate each query, the only
problem is with xTabFinal. Specific errors with that after the code:

xTab1 =
SELECT Initiatives.InitiativeNumber, Initiatives.InitiativeName,
Initiatives.PDD, Initiatives.Scope, Initiatives.InitiativeUniqueNumber,
InitiativeImpactedDepts.DeptNumber, Initiatives.ReleaseNumber
FROM InitiativeImpactedDepts INNER JOIN Initiatives ON
InitiativeImpactedDepts.InitiativeUniqueNumber =
Initiatives.InitiativeUniqueNumber
WHERE
(((InitiativeImpactedDepts.DeptNumber)=[Forms]![repExecSum]![cboDept])
AND ((Initiatives.ReleaseNumber)=[Forms]![repExecSum]![cboRelease]) AND
((Initiatives.DroppedStatus)=0) AND
((Initiatives.NotSupportedStatus)=0));

xTab2 =
SELECT PhaseActivity.PhaseActivity, App, Impacted, Description,
InitiativeUniqueNumber
FROM ImpactApp INNER JOIN PhaseActivity ON
PhaseActivity.PhaseActivity=ImpactApp.PhaseActivity;

xTabSum =
SELECT InitiativeNumber, InitiativeName, PDD, Scope, App, Impacted,
Description, DeptNumber, ReleaseNumber
FROM xTab1 INNER JOIN xTab2 ON
xTab1.InitiativeUniqueNumber=xTab2.InitiativeUniqueNumber;

xTabFinal =
TRANSFORM Count(xTabSum.Impacted) AS Expr1
SELECT xTabSum.PDD, xTabSum.InitiativeNumber, xTabSum.InitiativeName,
Count(xTabSum.Impacted) AS [Total Impacts]
FROM xTabSum
GROUP BY xTabSum.PDD, xTabSum.InitiativeNumber, xTabSum.InitiativeName
ORDER BY xTabSum.PDD, xTabSum.InitiativeNumber, xTabSum.InitiativeName
PIVOT xTabSum.App;

The error occrus when running the xTabFinal Query (which is the
CrossTab Query). I get the following error: "The Microsoft Jet database
engine does not recognize 'Forms!repExecSum!cboDept' as a valid field
name or expression". Of cours, I assume that is an expected result
because CrossTab Queries (from what I know so far) require that all
information be present (read: hard-coded) in order for results to be
returned successfully.

Basically, I'm wondering if there is any way around this? I don't mind
using VBA Code if need be, but please try to provide me with as much
VBA Code as possible as I'm completely new to using SQL Queries and
Reports (especially CrossTab Queries) in VBA code. But I am extremely
comfortable with the Visual Basic language and other functions dealing
with Events/Forms/Controls etc in VBA (including within Microsoft
Access).

I'm sorry for the ridiculously long question, but I really don't know
where else I can find informatino or help about this particular
situation. Honestly, I don't even expect anyone to be able to help me
with the extent that I need, but it makes me feel a little bit better
to at least ask. :)

Thanks and God Bless,
-Jeremy

Of course, if you need any further information or clarification, please
let me know!



.

Relevant Pages

  • Re: Query Word 2003
    ... Just in case there is any misunderstanding, the code I showed isn't VBA code - it's the code in a "Word field." ... As far as DATABASE fields are concerned, ... I select Query Option s, Filter Records, Sort Records and Select Fields, when I remove the fields from the select records option, and leave only the field I wand in my report, when I complete the report all the fields are showing up in the report. ...
    (microsoft.public.word.docmanagement)
  • Re: Is Report actually printed?
    ... > Using VBA code: ... The below code will add the date you sent the report to the printer to ... Private Sub ReportHeader_Format(Cancel As Integer, ... I have used an Append query to add a new record with the current date ...
    (microsoft.public.access.modulesdaovba)
  • Re: Clarification: Re: 6 Tables, 1 Report, W/O 6 Qrys
    ... report bound to a query. ... The VBA code would place the ...
    (microsoft.public.access.reports)
  • Re: Query Help for Inventory Tracking
    ... > way I can accommodate cross-tabbing the data. ... standard query. ... You could write the vba code in a report, ... you double-click the report which is similar. ...
    (comp.databases.ms-access)
  • Re: Query Help for Inventory Tracking
    ... > way I can accommodate cross-tabbing the data. ... standard query. ... You could write the vba code in a report, ... you double-click the report which is similar. ...
    (comp.databases.ms-access)