Unwanted Parameter Fields in Crystal using Union Query

[jpalmer150]

I'm connecting Crystal to an Access Union Query. Everything is working fine and my data is displaying correctly. However the SQL coding is adding unwanted Parameter fields to my Crystal Report. I'm not sure why...

Here is a portion of the SQL code from the Union Query:
__________________________
SELECT [Personnel No],[Cost Center],[Account],[OCT] as [AMOUNT], "10/31/"&[Fiscal Year] As [MONTH]
FROM [Tbl_Travel_Expense_Data]
UNION ALL SELECT [Personnel No],[Cost Center],[Account],[NOV], "11/30/"&[Fiscal Year]
FROM [Tbl_Travel_Expense_Data]
UNION ALL SELECT [Personnel No],[Cost Center],[Account],[DEC], "12/31/"&[Fiscal Year]
FROM [Tbl_Travel_Expense_Data]
__________________________

Cystal is automatically creating the corresponding Parameter fields:

[10/31/]
[11/31/]
[12/31/]

Any suggestions regarding how I can get rid of these unwanted Parameters in Crystal or what I should have done differently?

JP

 

[synapsevampire]

Not sure why it's interpreting it as a parameter, try changing the & to a +.

-k

 

[IdoMillet]

JP,

The Query runs in Access without prompting for any parameters?

Then, when you use that Query as the data source in Crystal you get prompted (or are you trying to create a CR 9.0 "Command" using that SQL syntax)?

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[jpalmer150]

synapsevampire,

Good idea...I gave it a try but unfortunately the results were the same.


IdoMillet,

Access doesn't prompt me for any parameters, and I haven't added any to Access. In Crystal I am not doing anything special other than adding the Query to the report. I seems that Crystal is confused by the Concatenate within the code and can't assign a value. Therefore it's asking for one.

Do you think this is a limitation of the ODBC driver?

 

[jpalmer150]

Found the answer:

http://support.crystaldecisions.com/library/kbase/articles/c2008149.asp

Error: "Too Few Parameters, Expected 2" When Report Based on Union Query

The information in the article refers to:
Seagate Crystal Reports 7


Applies to:

Reported version only
Access 97 Union Query
Quotes Surrounding Character Literals



Synopsis

A report based on a Microsoft Access 97 query connecting through ODBC generates the error:

"ODBC Error:
Too few parameters, expected 2."


Solution

The Microsoft Access query contains single character literals, such as:

SELECT "W" as result_name, "H" as customer_name
FROM tablename

These single character literals are being recognized as parameter fields by the Microsoft Jet enngine because they are enclosed in double quotes. Using single quotes to delimit the literals will eliminate the error.

In Microsoft Access, single quotes are used to delimit text literals and double quotes are used to delimit identifiers that need special handling.