Crystal Reports - spaces in columns

Hi -
I am using CR10 and trying to create a report that has spaces in the column name

In CR the sql statement looks something like this

SELECT
table_name1.LastName
,table_name2.User id
FROM table_name1
INNER JOIN table_name2
ON table_name1.id=table_name2.User id

This gives a error in SQL so it should look something like this:
SELECT
table_name1.LastName
,table_name2.[User id]
FROM table_name1
INNER JOIN table_name2
ON table_name1.id=table_name2.[User id]

Is there any way to change this in CR10 so that it sends the proper format to the sql server

Thanks in advance
doctor069Asked:
Who is Participating?
 
doctor069Connect With a Mentor Author Commented:
Had to change the column names to have no spaces in order for it to work
0
 
mlmccCommented:
Try this

SELECT
table_name1.LastName
, 'table_name2'.'User id'
FROM table_name1
INNER JOIN table_name2
ON table_name1.id= 'table_name2'.'User id'

mlmcc
0
 
doctor069Author Commented:
thanks mlmcc but how is that done in crystal reports.

I just drop the field onto the report and crystal creates the sql
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
Did you select the tables and just putting the fields on the report?
If so, Crystal should add the appropriate deliiters to the SQL.

mlmcc
0
 
mlmccCommented:
Is the issue with Crystal or the Export?

What version of Crystal?

mlmcc
0
 
doctor069Author Commented:
Crystal 10
Yes, I select the tables and put the fields on the report.

When I view the SQL (Database -> Show SQL Query) in Crystal it has not added the anything to accommodate for the space.

table_name2.User id needs to be table_name2.[User id]

So I get a SQL error when running the report
0
 
mlmccCommented:
Try a VERIFY DATABASE.

Another idea would be to delete that table from the report then run the report and then add the table back.

mlmcc
0
 
doctor069Author Commented:
Database verify says "Up To Date"

I removed the table, ran report (no issues) then error again when I added.
0
 
mlmccCommented:
No idea.   I generally use fieldnames with no spaces or use underscores for them.

When you select the tables you can't change the SQL that is generated.  Almost sounds like there is a problem with your Crystal install.

Is it a full version of Crystal 10 or one of the versions that came with Visual Studio?

Do you have Visual studio 2003, 2005, or 2008 installed?
If so is Crystal for Visual Studio installed?

mlmcc
0
 
doctor069Author Commented:
No answer given
0
All Courses

From novice to tech pro — start learning today.