We help IT Professionals succeed at work.

Crystal Reports Command SQL Query Syntax Error

RUA Volunteer2?
on
590 Views
Last Modified: 2012-08-14
Was placing the Sytax below in to the Crystal Reports Add Command window and got a failure. Can you identify what is wrong? We are running this off of a MS Access Database.

SELECT `CardAmt` AS `CardAmt`, `CashAmt` AS `CashAmt`, `CheckAmt` AS `CheckAmt`, `TransDate` AS `TransDate`, `TransNum` AS `TransNum`, `ValueType01` AS `Account`, `ValueAmt01` AS `Amount`, `UserID` AS `UserID` FROM `Transaction` WHERE ( ( `CardAmt` <>0 ) OR ( `CashAmt` <> 0 ) OR ( `CheckAmt` <> 0 ) AND  (`ValueType01` <>0))

Please help
Comment
Watch Question

Top Expert 2004

Commented:
Does this work directly in Access?  This would be the firsth thing to test.
Second, you may want to create a second report, add the transaction table instead of a command, and then put some of the fields on the report.  You can then go to the Report menu, SQL Query to see the format that Crystal generated the SQL in.
From what I can see, you can create the report easily without even using a command.  Is there a reason you need to use a command?
What are those characters wrapped around your column names and alisaes for?
SELECT CardAmt, CashAmt, CheckAmt, TransDate, TransNum, ValueType01 AS Account, ValueAmt01 AS Amount, UserID
FROM Transaction WHERE ( ( CardAmt <>0 ) OR ( CashAmt <> 0 ) OR ( CheckAmt <> 0 ) AND  (ValueType01 <>0))
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.

Author

Commented:
I got "DAO too few parameters expected 1" as and error message when I put that in? Any ideas
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.

Author

Commented:
That was directed at mlmcc?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
No ideas..

When do you get the error?  When you add the command or when you preview the report?

mlmcc
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.

Author

Commented:
When I Click OK on the Add Command
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
If you take the where clause off do you get the error/

mlmcc
Top Expert 2004

Commented:
Why are you using a command?  You can do this by adding the table to a report, placing the desired fields on the report, and defining the record selection.
If you do it that way, you won't need to deal with the syntax and it is easier to maintain in the future.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.

Author

Commented:
bbreed35 I am actually have a series of commands like:

SELECT `CardAmt` AS `CardAmt`, `CashAmt` AS `CashAmt`, `CheckAmt` AS `CheckAmt`, `TransDate` AS `TransDate`, `TransNum` AS `TransNum`, `ValueType01` AS `Account`, `ValueAmt01` AS `Amount`, `UserID` AS `UserID` FROM `Transaction` WHERE ( ( `CardAmt` <>0 ) OR ( `CashAmt` <> 0 ) OR ( `CheckAmt` <> 0 ) AND  `ValueType01` <>0)
UNION ALL
SELECT `CardAmt` AS `CardAmt`, `CashAmt` AS `CashAmt`, `CheckAmt` AS `CheckAmt`, `TransDate` AS `TransDate`, `TransNum` AS `TransNum`, `ValueType02` AS `Account`, `ValueAmt02` AS `Amount`, `UserID` AS `UserID` FROM `Transaction` WHERE ( ( `CardAmt` <>0 ) OR ( `CashAmt` <> 0 ) OR ( `CheckAmt` <> 0 ) AND  `ValueType02` <>0)
UNION ALL
SELECT `CardAmt` AS `CardAmt`, `CashAmt` AS `CashAmt`, `CheckAmt` AS `CheckAmt`, `TransDate` AS `TransDate`, `TransNum` AS `TransNum`, `ValueType03` AS `Account`, `ValueAmt03` AS `Amount`, `UserID` AS `UserID` FROM `Transaction` WHERE ( ( `CardAmt` <>0 ) OR ( `CashAmt` <> 0 ) OR ( `CheckAmt` <> 0 ) AND  `ValueType03` <>0)

There are 10 in all I only using the first segment now as follows:
I am now testing for this and it worked. So how do I get the where clause back in and how do I create a UNION between each set?
SELECT CardAmt, CashAmt, CheckAmt, TransDate, TransNum , ValueType01 AS Account, ValueAmt01 AS Amount,  UserID FROM Transaction
Top Expert 2004

Commented:
Trying adding in one piece of the Where clause at a time until it errors out to try and narrow down the problem.
Does it work with the full Where clause directly in Access?
Top Expert 2004

Commented:
Add this first and then see if it accepts the command:
Where (CardAmt <> 0)

If that works, append the next part:
Where (CardAmt <> 0) or (CheckAmt <> 0)

And so on.
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.

Author

Commented:
I just got this sequence to work. Man you guys are great. I am going to have to split the points between the 3 of you. Hope that is cool. Man this is great. Thanks for helping me keep the little ones fed! I have been really struggling with this stuff.

SELECT CardAmt, CashAmt, CheckAmt, TransDate, TransNum , ValueType01 AS Account, ValueAmt01 AS Amount,  UserID FROM Transaction
Where (CardAmt <> 0) or (CheckAmt <> 0) or (CashAmt <> 0) and (ValueType01 <>0)
UNION
SELECT CardAmt, CashAmt, CheckAmt, TransDate, TransNum , ValueType02 AS Account, ValueAmt02 AS Amount,  UserID FROM Transaction
Where (CardAmt <> 0) or (CheckAmt <> 0) or (CashAmt <> 0) and (ValueType02 <>0)

I was jsut curios when you create an alias with a SQL statement above "Why could I not have used (Account <>0) instead of (ValueType02 <>0). Can you not use it in the same statement that it was declared???
Top Expert 2004
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.