Boolean - Null - Select Problem

[Viscount]

I am using CR 9.0 and SQL Database.

I have created a report that lists records based on the information in 2 tables.

I want to restrict the records returned when a boolean (true / false) field is false or does not have a value.

If the value is true then the operation have been performed - any other value means that it has not been performed therefore I need to know about it.

Looking at the table it would appear that the only entries in that field are ture - or they are null. There are no False values.

I have tried just using the select tool saying that the table.field is false, I have tried a formulea isnull({table.field}), I have tried (isnull({table.field})= true or not({table.field})) - but every time I do this I get no results.

Anyone got any ideas as to why this is not working..?

Thanks

Vis

 

[dgillz]

Plese post your formula. If there is more than one line of logic, your isnull() test must come first.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Reebo99]

In field explorer, right click on database fields, and select Show Field Type.

This will give you the type of field along side the filed name.

What field type does it say this field is, cause I can't see anything wrong with (isnull({table.field})= true or not({table.field}))

As dgillz has already said, you need to post the whole selection formula......

Reebo
UK

 

[Viscount]

There are 2 isnull statments in the formulea:

not(isnull({table.field1})) and
(isnull({table.field2})= true or not({table.field2}))

Field2 is the problem field

I tried to switch them around - but it made no difference and the select tool put them back the way that they were.

The field is a Boolean field. Could it be a problem with the database..?

Thanks.

vis

 

[Reebo99]

The best thing to do is test the field.

Create a formula :

If Isnull(table.field2) then "Yes" else "No"

And see what the formula shows when you place it in the detail....

Reebo
UK

 

[Viscount]

Thanks,

With no other selection criteria they are all 'No' with the other selection criteria there is no enteries and the test firld is 'Yes'

Does that shed any light on the situation..?

 

[Reebo99]

That means that the records which do not have a value are not null's.

Reebo
UK

 

[Ngolem]

Try this formula

(not isnull({table.field1}) or not {table.field2})


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[synapsevampire]

Given that you understand what your table holds, consider creating a mini-spec by posting example data and expected output rather than typing up descriptions.

-k

 

[Viscount]

Finally got it to work be creating a totext field - adding it to the details and then selecting anything where the text field = False

 

[scottostanek]

I had a similar problem with formulas that use booleans that occasionally get changed to integer by patch releases.
Nowadays I use ToNumber({table.field1}) = 1 to test True.

Went this way for standard because a lot of times it is multipled into a weight factor that should goto zero if the event should not be counted at all.

Scotto the Unwise