Selection Statement with OR

[wilekoyote76]

When using this selection statement is brings up the data for the first 2 fields (udaterecd and udateinv) but it will not bring up data from ustaterep:

{CONTACT2.UDATERECD} = {?Date for Status Report} or
{CONTACT2.UDATEINV} = {?Date for Status Report} or
{CONTACT2.USTATREP} = {?Date for Status Report}

I've even tried putting () around the whole statement to no go. My guess is programmatically, it can only check 1 OR the other. It cannot see 3 statements. Is there a better way to write it?

 

[fisheromacse]

I have had no issues using multiple OR statements in crystal reports xi r1.

Are there other selection criteria involved in your statement that are not shown?

Have you tried:
(
{CONTACT2.UDATERECD} = {?Date for Status Report} or
{CONTACT2.UDATEINV} = {?Date for Status Report}
) or
{CONTACT2.USTATREP} = {?Date for Status Report}

 

[wilekoyote76]

I have tried that. In fact, the "missing" data shows up and other data disappears when the equation is changed.

Using this:
(
{CONTACT2.UDATERECD} = {?Date for Status Report} or
{CONTACT2.USTATREP} = {?Date for Status Report}
)
or
{CONTACT2.UDATEINV} = {?Date for Status Report}

I get the missing status report data for the date but ALL DATA for the DateInv field is now missing.

If I use statrep and dateinv as the first 2 criteria, then anything with daterecd no longer shows up

(
{CONTACT2.UDATEINV} = {?Date for Status Report} or
{CONTACT2.USTATREP} = {?Date for Status Report}
)
or {CONTACT2.UDATERECD} = {?Date for Status Report}

No matter what I do, I can ONLY get the first 2 criteria of the OR statements to show up.

 

[IanWaterman]

Have you tried running report with just

{CONTACT2.USTATREP} = {?Date for Status Report}

And seeing if there are any dates returned?

Ian

 

[wilekoyote76]

As noted, each of the criteria DOES have data that it returns. I'm comparing against the original form that I'm rewriting into Crystal. That is how I noticed Status Report was missing data. Once I realized that, I've tried multiple modifications using brackets to break up the equation.

I'm not sure what is going in where it won't allow me to use 3 OR statements. Is there another way to write the same thing?

 

[IanWaterman]

Please show SQL Statement created, you should see the 3 OR statements there.

Ian

 

[fisheromacse]

I am not sure about the below and parameters.
Can you try to write it as:

{?Date for Status Report} in [CONTACT2.UDATEINV},
{CONTACT2.USTATREP},{CONTACT2.UDATERECD}]

 

[fisheromacse]

You didn't answer my 1st question...Are there other selection criteria involved in your statement that are not shown?

Also, I share Ian's thought, please show the entire SQL query you are using. (Database menu --> show SQL Menu)
Seeing what is actually being run can be the dealmaker!

 

[wilekoyote76]

SELECT "CONTACT2"."UADJNAME", "CONTACT2"."UINSFIRST", "CONTACT2"."UINSLAST", "CONTACT2"."USTATREP", "CONTACT2"."UINSCO", "CONTACT2"."ULOSSTYPE", "CONTACT2"."ULSLOCCIT", "CONTACT2"."ULSLOCST", "CONTACT2"."UCLMCLS", "CONTACT2"."UCLMYR", "CONTACT2"."UCLMNO", "CONTACT2"."UCLMADJ", "CONTACT2"."ULONGINSD", "CONTACT2"."UDATEINV", "CONTACT2"."UAMTINV", "CONTACT2"."UDATERECD"
FROM "Goldmine"."dbo"."CONTACT2" "CONTACT2"
WHERE (("CONTACT2"."UDATERECD">={ts '2010-07-27 00:00:00'} AND "CONTACT2"."UDATERECD"<{ts '2010-07-28 00:00:00'}) OR ("CONTACT2"."UDATEINV">={ts '2010-07-27 00:00:00'} AND "CONTACT2"."UDATEINV"<{ts '2010-07-28 00:00:00'}) OR ("CONTACT2"."USTATREP">={ts '2010-07-27 00:00:00'} AND "CONTACT2"."USTATREP"<{ts '2010-07-28 00:00:00'}))
ORDER BY "CONTACT2"."UADJNAME"

 

[wilekoyote76]

There is no other selection criteria besides what was shown.

 

[IanWaterman]

As you can see from the SQL its not a limitation of Crystal as the 3 ORs are there.

It must be something to do with your data.

Do you have access to DB via SQL Server Management suite so that you can run query directly?

Are you sure there is no group or section suppression hiding the USTATREP data?

Ian

 

[wilekoyote76]

{?Date for Status Report} in [{CONTACT2.UDATEINV},
{CONTACT2.USTATREP},{CONTACT2.UDATERECD}]

returns only 5 total records and it should return 50 something

 

[wilekoyote76]

Direct access to the server shows the missing items using the sql query.

Where in Crystal would it be set to not show all the data then?

 

[fisheromacse]

If you run it with 1 field in the where clause at at time, do you get accurate data each time?

ie:
1) WHERE ("CONTACT2"."UDATERECD">={ts '2010-07-27 00:00:00'} AND "CONTACT2"."UDATERECD"<{ts '2010-07-28 00:00:00'})

2) WHERE ("CONTACT2"."UDATEINV">={ts '2010-07-27 00:00:00'} AND "CONTACT2"."UDATEINV"<{ts '2010-07-28 00:00:00'})

3) WHERE ("CONTACT2"."USTATREP">={ts '2010-07-27 00:00:00'} AND "CONTACT2"."USTATREP"<{ts '2010-07-28 00:00:00'})

 

[wilekoyote76]

To answer Ian's question:

Are you sure there is no group or section suppression hiding the USTATREP data?

There is no suppression at all within the report as far as I know.

 

[wilekoyote76]

fisher:

If I use 1 or 2 criteria of the OR statements in Crystal, it provides the correct data. If I add the 3rd statement, I do not see the 3rd statement's data.

 

[wilekoyote76]

I'm going to test this in Visual Studio 2010 with Crystal 2010. If it shows up with the data...maybe I have a buggy version of Crystal.

 

[wilekoyote76]

still is happening in VS2010. Any thoughts on what to look for in Goldmine? Its weird the SQL is right and pulls right, even when the equation is changed so that one of the other statements is requesting data before the other subset of data, the new data shows but not all the data from all 3 except in a direct link to the SQL database.

 

[lbass]

I think you should place all three fields in the detail section and remove the selection criteria and then observe whether all three fields are populated for every row.

-LB

 

[wilekoyote76]

Convert Database NULLS to Default was not checked in Report Options. Upon checking the box, the items that were missing now show up.