Record Selection Problem with Default Null Fields - Help Appreciated 1

[Cort]

CR 8.5

OK I have a database where the selection field I want to use <Open> to limit my records has 3 possible values. Y, N, or empty.

I am using the convert null values to default to make my life a lot easier on some math related formulas elsewhere in the report.

I want to limit the selection criteria of the report to all records where <Open> <> &quot;Y&quot;.

When I use <Open> <> &quot;Y&quot; as the criteria I get only those records where the value in the field is &quot;N&quot; none of the empty fields.

When I use Isnull(<Open>) no records at all are returned.

When I use <Open> = &quot;&quot; No records are returned, same for &quot; &quot;.

Any ideas on how I can have the records returned?

Thanks again as always.

 

[Naith]

If you remove all the filtering on {Open}, do you get all three types of record?

 

[Cort]

Yes. If I turn off null to default I can return the null values. Ouch, gonna be a long day redoing formulas. But that's kind of what I had figured on. Didn't know if there was a command for <select default null value> or not.

Thanks

 

[Naith]

You shouldn't have to redo your formulas. What I was after was if the report does return the null values when filtering is off, are the values treated as null in the report? You can test this by creating a formula stating If IsNull({Open}) Then 'Not Null' else 'Not Null'.

If this doesn't return the expected result, try If {Open} = '' too. You might want to utilise the trim function in these tests.

Because you have 'default value' for nulls switched on, I'm trying to ascertain what the default value for this field is.

Naith

 

[Naith]

One more thing which I forgot to mention, (this will probably turn out to be a heck of an oversight, as I suspect it's actually what your problem is...) the Null filtering of {Open} needs to be the first filter in your Record Selection. If null harvesting doesn't come first, for some reason, null values tend to get overlooked.

Naith