geckerdt
asked on
Crystal Reports Record Selection Or command does not work
Use a Record Selection Formula of {ARMAST01.ARTYPE}="C" or {ARMAST01.WARRANTY}="W" on selects on the first half of the or statement. Can reverse and both sections work but they will not show records that meet either statement. Using the "and" operator works correctly as no records show.
Is that the whole of the record selection expression or is there more that you have not shown?
If there is more please post the whole expression.
If there is more please post the whole expression.
ASKER
{ARYMST01.WARRANTY}="W" or {ARYMST01.ARTYPE}="C" is the whole record selection. I am starting to think the problem is that some records have both a Warrany=W and an ARTYPE=C. Using "and" gives the records with both types of data. {ARYMST01.WARRANTY}="W" or
{ARYMST01.ARTYPE}="C" gives the Warranty="W" records. If you reverse the statement to {ARYMST01.ARTYPE}="C" or {ARYMST01.WARRANTY}="W" you only get ARTYPE=C records. Which ever statement is first are the records you get.
{ARYMST01.ARTYPE}="C" gives the Warranty="W" records. If you reverse the statement to {ARYMST01.ARTYPE}="C" or {ARYMST01.WARRANTY}="W" you only get ARTYPE=C records. Which ever statement is first are the records you get.
What database are you using and what connection method ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
James0628: Bingo! I would have argued until I was blue in the face that this would not have made any difference but it did. Thanks. I use Crystal a lot in our business and have for quite a few years and this one seems to not make any sense. Do you have any additional references on this. Hadn't the results been so blatantly wrong I would have accepted the report as accurate. Do you work in crystal a lot?
Sorry I didn't get back to this sooner. I've just been busy this week.
For additional references, there's basically this from the CR 10 Help:
In general, when Crystal Reports encounters a null valued field in a formula, it immediately stops evaluating the formula and produces no value. If you want to handle null field values in your formula, you must explicitly do so using one of the special functions designed for handling them: IsNull, PreviousIsNull or NextIsNull.
The part that didn't seem to fit was that when you switched the order of the tests, the first test always seemed to work. Somehow, that just didn't sound like a null issue. But I guess maybe I can see how it could be. IAC, I'm glad that fixed it for you.
James
For additional references, there's basically this from the CR 10 Help:
In general, when Crystal Reports encounters a null valued field in a formula, it immediately stops evaluating the formula and produces no value. If you want to handle null field values in your formula, you must explicitly do so using one of the special functions designed for handling them: IsNull, PreviousIsNull or NextIsNull.
The part that didn't seem to fit was that when you switched the order of the tests, the first test always seemed to work. Somehow, that just didn't sound like a null issue. But I guess maybe I can see how it could be. IAC, I'm glad that fixed it for you.
James
ASKER
James,
Thanks for the update. What is the best way to get your attention for future problems? There seems to be a shortage of people that are really good at crystal?
Thanks for the update. What is the best way to get your attention for future problems? There seems to be a shortage of people that are really good at crystal?
Well, I believe it's against the EE rules to try to contact people directly (via email or whatever), so all I can suggest is to post your question here on EE. There are a number of people around here that are pretty good with CR, and getting multiple perspectives can be very helpful, since there's usually more than one way to skin the proverbial cat.
For another question that was actually related to this one, you could use the "ask a related question" link, and anyone that was involved in this question should get an email about the new question.
James
For another question that was actually related to this one, you could use the "ask a related question" link, and anyone that was involved in this question should get an email about the new question.
James
ASKER
I did not know about the "ask a related question" . I thought there had to be something. Thanks!!!!
Not wishing to detract from James's expertise in any way, but I suspect that most of us, like James, considered a 'nulls' problem, and also like James, dismissed it.
It's just that James posted it anyway.
I have to say that although i have to accept that was the solution, it still makes no sense to me.
I would expect such a selection rule to be passed to the database for processing as part of the sql command that CR generates, and so CR's 'fear of nulls' should never have entered into the situation.
Can you say what type of database you are using?
It's just that James posted it anyway.
I have to say that although i have to accept that was the solution, it still makes no sense to me.
I would expect such a selection rule to be passed to the database for processing as part of the sql command that CR generates, and so CR's 'fear of nulls' should never have entered into the situation.
Can you say what type of database you are using?
ASKER
peter, I really appreciate the interest and would like to understand it as well. I use basically the same chain of or statements regularly and this one makes no sense. Attached is a sample database and the report. Change the extension of aaa_c_W.txt to dbf. It is a foxpro database. Open report in crystal and go to report, ediit selection criteria, record. You will see my notes remmed there. You will probably have to reset the database path in the report.
aaa-c-w.rpt
AAA-C-W.txt
aaa-c-w.rpt
AAA-C-W.txt
Peter has a good point about a record selection like that normally being passed to the db. Can you go to Database > Show SQL Query and see if those tests are included in the query? You can copy the query and paste it here if you like. I tried checking it in the report that you posted, but the "Show SQL Query" option was greyed out (maybe because I don't have Foxpro).
James
James
ASKER
It appears that since this is a fox pro database, there is no sql statements created in Crystal. Mine are grayed out as well and the Crystal help file implies it is used only with an sql database. We were able to do the same data selection showing both C & W with foxpro without a problem.
My continued thanks for your interest.
My continued thanks for your interest.
Interesting. Maybe it does have something to do with how CR communicates with Foxpro then. Something to keep in mind, I guess.
James
James
Just got back to this thread.. I can see an explanation has been found; thank goodness for that.
Preece