I can't get my crosstab query to work in a form

[MSmoger]

When I run this query

TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt
SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
FROM [rpt LEAD TYPE SUCCESS RATE2]
GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;

I get valid results.

When I run the form it prompts me 3 times for each start date and end date. Then I get the following error :The Microsoft Access database engine does not recognize '' as a valid field name or expression.

I am running MS Access 2010 on a Window 7 pc.

Matt

 

[pbaldy]

Certainly this is part of the problem:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209778

 

[MSmoger]

I have added the [Start Date? mm/dd/yyyy] and the [Start Date? mm/dd/yyyy] parameters to the rtp LEAD TYPE SUCCESS RATE2 query. When I run the query it is prompting me to enter the Start Date once and the End Date twice. Why?

When I run the report it prompts me 4 time for start date and 8 times for end date.

Matt

 

[pbaldy]

Would the query be returning different field names than the report is built for? Forms or reports built on crosstabs typically require some manipulation to handle the fact that the query can return different field names each time it's run.

 

[MSmoger]

I figured out the duplicate issue of the end date. (I had two spaces between end and date).

Now the report give me an error "Wrong data type for parameter 'End Date? mm/dd/yyyy'.

I have the parameter set to date/time.

Matt

 

[pbaldy]

I assume the data type of the fields are date/time as well? Can you post the db here by chance?

 

[MSmoger]

The field is date/time and I can't copy the db. Sorry.

 

[pbaldy]

What's the SQL of the query that gives you that error?

 

[MSmoger]

Here is the sql from my main query
PARAMETERS [Start Date? mm/dd/yyyy] DateTime, [End Date? mm/dd/yyyy] DateTime;
SELECT LD_SRC.LD_DESC AS Lead, [Start Date? mm/dd/yyyy] AS Start_Date, [End Date? mm/dd/yyyy] AS End_Date, RESULTS.R_DESC AS Results, Count("A"="A") AS rec_cnt, ALL_CALLS_UNION.Internal_Rep
FROM (ALL_CALLS_UNION INNER JOIN RESULTS ON ALL_CALLS_UNION.Result = RESULTS.R_CODE) INNER JOIN LD_SRC ON ALL_CALLS_UNION.Lead_Srce = LD_SRC.LD
WHERE (((RESULTS.R_DESC) In ("Sale","No Sale","Transferred","InDirect Sale")) AND ((CDate(Format([Call_Date],"mm/dd/yyyy"))) Between [Start Date? mm/dd/yyyy] And [End Date? mm/dd/yyyy]))
GROUP BY LD_SRC.LD_DESC, [Start Date? mm/dd/yyyy], [End Date? mm/dd/yyyy], RESULTS.R_DESC, ALL_CALLS_UNION.Internal_Rep
ORDER BY LD_SRC.LD_DESC;

here is the sql for my crosstab query
PARAMETERS [Start Date? mm/dd/yyyy] DateTime, [End Date? mm/dd/yyyy] Text ( 255 );
TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt
SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
FROM [rpt LEAD TYPE SUCCESS RATE2]
GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;

These both run fine.

I get the error when I try to run the report from the second query above.

Matt

 

[JHB]

You are using CDate in the first query, but the CDate function equerries a valid date expression and (but) the Format function returns string, I would change the CDate to DateValue.

Then in the crosstab query you are have parameters, but I can't see you are not using them.
And one parameter is declarer as Datetime, the other as Text

 

[pbaldy]

If the queries run fine but not the report, did you address my question in post 4?