TRUE/FALSE 1

[retygh45]

This is probably a dumb question, but in CR10, I'm trying to add a criteria that checks to see if a field is True or False.

It's a boolean field that's either true or false, but when I use the selection expert window, and say {table.field}=True it just changes it to {table.field} and removed the "True" part.

However when I run my report, it shows both True and False records.

Am I using this field incorrectly? Any help is appreciated, thanks!

 

[kenhamady]

If the field is a boolean (confirm this) then using it all by itself is exactly the same as doing {field} = true.

So, if it isn't working there is another reason.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[retygh45]

Thanks Ken. I did confirm this, it is a boolean field, and when I "right-click" on the field and say "Browse Data" it shows only 2 items: False and True

That's why I was thinking there were fields with "False " (with a space in it or something) else causing False fields to show up in the report.

 

[kenhamady]

What do you get when you set it:

{field} = FALSE

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[retygh45]

It correctly shows only the False records.

 

[retygh45]

Something else is not correct here, I don't think it's the true/false.

I'm simply trying to re-create a query from Access in Crystal, but there's something not right. I'm sure it's right in my face, and I just can't see it, but the Access query is:

SELECT dbo_Claims.Claim_Num, dbo_Claims.Claim_Status_Code, dbo_Claims.Review_Date, dbo_Claims.Administrative_Hold_Code, dbo_Claims.Violation_Code, dbo_Claims.Passed_Trial_Date, dbo_Claims.Location_User_Code, dbo_Claims.Location_Date
FROM dbo_Claims
WHERE (((dbo_Claims.Claim_Status_Code)="rv") AND ((dbo_Claims.Review_Date)<[Review Date]) AND ((dbo_Claims.Administrative_Hold_Code) Is Null) AND ((dbo_Claims.Violation_Code)="nv" Or (dbo_Claims.Violation_Code) Is Null) AND ((dbo_Claims.Passed_Trial_Date)=0) AND ((dbo_Claims.Location_User_Code)<>"SEVRTANK"))
ORDER BY dbo_Claims.Review_Date;


and my Crystal, I tried to simplify down to:

{Claims.Claim_Status_Code} = "RV" and
{Claims.Review_Date} < {?Review Date} and
({Claims.Violation_Code}="NV" OR IsNull({Claims.Violation_Code})) and
{Claims.Passed_Trial_Date} = TRUE and
{Claims.Location_User_Code} <> "SEVRTANK" and
IsNull({Claims.Administrative_Hold_Code})

I may have some parenthesis grouping wrong or something, b/c I get 0 records selected, but the Access query returns 124 rows.

Any help is greatly appreciated, thanks!

 

[HardJeans]

Your Access Select statement shows:
(dbo_Claims.Passed_Trial_Date)=0

and your Crystal Select shows:
{Claims.Passed_Trial_Date} = TRUE

I don't see anything else different except "RV"/"NV" vs. "rv"/"nv" but I don't recall access being case sensitive.

Use 'Show SQL Query' under database in Crystal, and try running the query in Access.

 

[lbass]

In CR, you should be checking for nulls first, so you would use:

IsNull({Claims.Administrative_Hold_Code}) and
(
IsNull({Claims.Violation_Code}) or
{Claims.Violation_Code}="NV"
) and
{Claims.Claim_Status_Code} = "RV" and
{Claims.Review_Date} < {?Review Date} and
{Claims.Passed_Trial_Date} = TRUE and
{Claims.Location_User_Code} <> "SEVRTANK"

-LB

 

[retygh45]

Thanks again LB! I wasn't aware of this, and it definitely does work, but why? Why does Crystal care if the NULL's are checked first? I thought it was a grouping issue, where I had parenthesis around the wrong group, trying to group a "this OR that" inside a Where clause.

Thanks again!

 

[Madawc]

Crystal stops processomg a formula whenever it encounters a null, unless you first do the ISNULL check. That's part of the design philosophy and it surprised me too, when I first started using Crystal.



Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Checking for a value first causes CR to search a field for values, not nulls. In other words, CR "assumes" you are concerned only with non-null fields, unless you check for nulls first.

-LB