first time crystal user getting error

[barbola]

I broke down and decided to give Crystal another chance although I have never been successful creating a useful report.

I have a list of employees from a SQL database and want to create a new field on my report that takes the left character of another field.

I created a SQL Expression as follows:

LEFT(mytable.myfield,1)

I get an error saying there is something wrong with it. I also tried the default function that gives me this:

{fn LEFT(mytable.myfield,1 )}

where I double click on LEFT(, ) then doubleclick my field name and enter the 1.

I also looked in the help document and it shows this format:

Left({customer.FAX}, 3)

so I tried it:

Left({mytable.myfield}, 1)

I get "Error in compling SQL Expression: ."
then
"Error in initializing SQL Expression parser"
then
"Error in creating SQL Expression COM parser"

I need to be able to do many calculations and filtering on this data, so maybe Crystal isn't my best option after all.

thanks
b

 

[Turkbear]

Hi,
You do not need to use a Sql Expression to do what you want, just a simple Crystal formula..

@FirstLetter
If Trim({table.field} <> "" then
Left({table.field},1)

Always test for blank fields or NULL values....


editorial:
Blaming the tool for a lack of knowledge of how to use it seems a little rash...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

You probably need to take a class on Crystal.

I guess by SQL database you mean SQL Server, SQL is a database programming language.

The first syntax looked fine, but posting "I get an error saying there is something wrong with it." isn't useful, don't bother posting with such things, you need to post the actual error.

I'm starting to understand why you had bad luck with crystal, patience and being thorough are key, and it isn't the most intuitive program when getting started, but if you understand SQL you'll be fine, hang in there.

In SQL Server you can use:

left(table.field,1)

or you can use

substring(table.field,1,1)

-k

 

[barbola]

I'm not new to SQL Server or database programming or how to post in this forum. (I did post the actual errors if you kept reading). I just thought I'd try Crystal for a change.

A course won't help me get my answer today. I've had enough headaches from taking courses to last a lifetime which is why I posted here.

I will try using a formula instead.

thanks.

 

[synapsevampire]

You don't need a formula, but that's how most would approach it.

You showed 3 formulas, then later you showed a group of errors, my point was that you should post specifics, so perhaps you did post the actual error, but how would we know that from what you posted? At the end as an afterthought you paste in a set of errors.

You posted "I get an error saying there is something wrong with it.", which I guess was intended to differentiate it from those errors where something is right...

I still have no idea what the first SQL Expression error resulted in, it should work and does here, perhaps it's the data type, perhaps the database type (is it SQL Server???).

-k

 

[barbola]

Yes it is SQL Server.

I get the same 3 errors with all the different expressions that I tried.

The first error pops up and when I respond I get the second error, then the third.

I get no errors when I connect to this same database table from any other application including VB, VBA, Access, and Excel.