Crosstab Qry Nightmare 1

[Daybreak]

Access 97, No VBA. I am having problems with a crosstab query. I have 2 underlaying queries (ex: passthru 1 & passthru 2. The problem: I am receiving the &quot;the microsoft jet engine does not recognize &quot;[start date?]&quot; as a valid field name or expression&quot;. I have the following criteria:<br>
<br>
field=closed date -criteria Between [start date?] and [end date?]<br>
-or Between [start date?] and [end date?]<br>
field=review date -criteria Is Null<br>
-or <br>
field=reason -criteria<br>
-or &quot;redem - our share - adru&quot; or &quot;redem - adru&quot;<br>
<br>
I have tried the above criteria in query 1 same error, deleted criteria from query 1 moved to query 2, same error, moved criteria to crosstab query, same error. The only way to get resuls if I hand code the dates in any of the 3 querys. I also tried the qry parameter to ask for date, but this does not give good date/data results. Why can't jet engine recognize the Between [date] and [end]part. Please, help....I'm at the end of the rope on this one. Thanks for all your previous help.

 

[DougP]

What is this [start date?] <br>
If you want a question mark, it has to go outside the brackets [start date]?<br>
Brackets tell Access the field name because it has spaces in it. <br>
Since there is no field name &quot;start date?&quot; you are getting the error. <p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[Daybreak]

Thanks DougP for responding. To answer your question about the [start date?]. I use this in my query (ex: date_closed is the date filed i want to search on) when I enter the &quot;criteria field&quot; and/or the &quot;Or field&quot; with the Between [Start Date?] and [End Date?], this prompts the user for the dates they wish to pull data from. This sample has worked in all of my other 50 queries. But when I run the crosstab qry I receive the jet engine error. I hope this explains what I am trying to accomplish. any Ideas how to fix this?

 

[famousb]

it may not be recognizing the fields when they contain the ? in them. what you may have to do is create a query that utilizing all of the fields that you need for your crosstab, and rename [start date?] and [end date?] to not include the ?<br>
<br>
example. in the Field line of the query design, enter.<br>
Start Date: [start date?] <br>
for the [start date?] column<br>
End Date: [end date?] <br>
in the one for [end date?] <br>
<br>
then run your crosstab query off of this query that you just created.<br>
<br>
i think this should solve your dilemma <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[DougP]

I use this [Enter Start Date] and leave the question mark out<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[Daybreak]

Tried all the above, still receiving the jet engine error. Here is the SQL:, maybe you can find something here.<br>
<br>
Query passthru 1:<br>
<br>
SELECT TV_MATTER.CLOSED_DATE, TV_MATTER.REVIEWED_DATE, TV_MATTER.MATTER_TEXT_VAL1 AS [Account #], TV_MATTER.MATTER_TEXT_VAL2 AS [Claim #], TV_MATTER.USER_LOOKUP_DESC8 AS REASON, TV_MATTER.MATTER_NAME AS CASE, TV_MATTER.MATTER_TYPE_DESC1, IIf([PARTY_ID08]=0,[ASSIGN_NAME1],[PARTY_NAME08]) AS EXAM<br>
FROM TV_MAILER INNER JOIN TV_MATTER ON TV_MAILER.MAILER_ID = TV_MATTER.ASSIGN_ID2<br>
WHERE (((Left$([MATTER_TYPE_DESC1],1))=&quot;*&quot)<br>
ORDER BY TV_MATTER.ASSIGN_NAME1;<br>
<br>
Query passthru 2<br>
<br>
SELECT [qry ADRU Resolution Summ (pass thru 1)].EXAM, Examiners_Location.Location, [qry ADRU Resolution Summ (pass thru 1)].[Account #], [qry ADRU Resolution Summ (pass thru 1)].[Claim #], [qry ADRU Resolution Summ (pass thru 1)].CLOSED_DATE, [qry ADRU Resolution Summ (pass thru 1)].REVIEWED_DATE, [qry ADRU Resolution Summ (pass thru 1)].REASON, [qry ADRU Resolution Summ (pass thru 1)].CASE, [qry ADRU Resolution Summ (pass thru 1)].MATTER_TYPE_DESC1<br>
FROM [qry ADRU Resolution Summ (pass thru 1)] LEFT JOIN Examiners_Location ON [qry ADRU Resolution Summ (pass thru 1)].EXAM = Examiners_Location.[Examiner Name]<br>
WHERE ((([qry ADRU Resolution Summ (pass thru 1)].CLOSED_DATE) Between [Start date] And [End date]) AND (([qry ADRU Resolution Summ (pass thru 1)].REVIEWED_DATE) Is Null)) OR ((([qry ADRU Resolution Summ (pass thru 1)].CLOSED_DATE) Between [Start date] And [End date]) AND (([qry ADRU Resolution Summ (pass thru 1)].REASON)=&quot;Redemption - Our Share - ADRU&quot; Or ([qry ADRU Resolution Summ (pass thru 1)].REASON)=&quot;Redemption - ADRU&quot);<br>
<br>
Query crosstab<br>
<br>
TRANSFORM Count([qry ADRU Resolution Summ - pass thru 2].[Claim #]) AS [The Value]<br>
SELECT [qry ADRU Resolution Summ - pass thru 2].Location AS Expr1, [qry ADRU Resolution Summ - pass thru 2].REASON AS Expr2, Count([qry ADRU Resolution Summ - pass thru 2].[Claim #]) AS [Total Of Claim #]<br>
FROM [qry ADRU Resolution Summ - pass thru 2]<br>
GROUP BY [qry ADRU Resolution Summ - pass thru 2].Location, [qry ADRU Resolution Summ - pass thru 2].REASON, [qry ADRU Resolution Summ - pass thru 2].CLOSED_DATE, [qry ADRU Resolution Summ - pass thru 2].REVIEWED_DATE, [qry ADRU Resolution Summ - pass thru 2].REASON<br>
PIVOT [qry ADRU Resolution Summ - pass thru 2].EXAM;<br>
<br>

 

[MichaelJ]

Try this line at the beginning of the SQL statement above the SELECT line for the pass through query that asks for the date range. The key here is to include the data type (DateTime) after each date parameter.<br>
<br>
PARAMETERS [Start date] DateTime, [End date] DateTime;<br>