only select sales order when ALL comments do NOT start with Q 1

[KelleyO]

I have a sales order with 6 comment lines available to use. I only want to select a sales order if ALL 6 of the comment lines do NOT start with the letter Q. How can I do this in Crystal 8?

 

[IdoMillet]

WHERE
LEFT(comment1, 1) <> 'Q' AND
LEFT(comment2, 1) <> 'Q' AND
LEFT(comment3, 1) <> 'Q' AND
...
LEFT(comment6, 1) <> 'Q'

Cheers,
- Ido
ixm7@psu.edu

 

[KelleyO]

Would that it were that easy. In each record in the comment table, there's a field for sales order number, and a field for the comment text. So if there are no comments on a particular order, there are no entries for that sales order in the table. A typical SO has comment, blank line, comment, comment, blank line, comment. The Q comment is in the first line if it's there at all. However, the comment text fields aren't labeled comment1, comment2, etc. So, when my criteria was &quot;socomment does not start with Q&quot;, it selected all orders where ANY of the comment lines didn't start with Q, not where ALL of the comment lines (for that SO) didn't start with Q. Thanks.

 

[kenhamady]

So, if there is one Q in the group, you don't want any comment from that sale to print?

Create a formula:

if {so.comment} [1] = &quot;Q&quot; then 1 else 0


Create a subtotal of that field for each SO.
Now, select based on the subtotal itself. Where the subtotal is equal to 0. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[IdoMillet]

Ken's solution would work nicely.

If this is a large database and you want
to resolve the condition on the server
you could use a SQL query such as:

SELECT ...
FROM SO SO_Alias, COMMENT
WHERE NOT EXIST
(SELECT COMMENT.SO_ID FROM COMMENT WHERE
COMMENT.SO_ID = SO_Alias.SO_ID AND
LEFT(COMMENT.Comment_text, 1) =Q)

Cheers,
- Ido

ixm7@psu.edu