Selection formula problem 1

[savok]

i have a selection formula that works fine.

{training.actidn} like ["T*", "53"]
and
{training.actustart} in {?Start Date} to {?End Date}
and
{@Contract2}


now to that i need to add a filter that only selects customers with actual end date of 12/30/1899 (null)

when i do that it gives me 0 records any idea why? thanks

{training.actuend} = Date (1899,12,30)
and
{training.actidn} like ["T*", "53"]
and
{training.actustart} in {?Start Date} to {?End Date}
and
{@Contract2}

 

[kenhamady]

Try:

IsNull ({training.actuend})
and
{training.actidn} like ["T*", "53"]
and .. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[alley211]

Hi,

You have to be careful with null dates because the isnull function doesn't always work.

Try this instead:

either

Date({datetimefield}) = Date(0,0,0)


or

{datetimefield }< DateTime(0,0,0,0,0,0)

Hope this helps,

alley

 

[kenhamady]

Null is tricky because not all databases support true nulls.
IsNull always works if the value is truly null.

Date (0,0,0) is not a true a null value, it is the default value for a date field in CR. A formula using less than will not work with true null values, either. This is because a formula can't evaluate a null value. Using this as a selection criteria will skip true nulls. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[savok]

thanks for the response guys

Its a foxpro database, and for some reason all the null dates are replaced with a date of 12/30/1899

so basicly this formula should work, but i have no idea why it gives me 0 records.

isnull({training.actuend}) or {training.actuend} = Date (1899,12,30)
and
........

I am about to give up ;(

 

[dgillz]

I have used length({fieldname})=0 with some success in the past as an alternative to IsNull() Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[savok]

The thing is its not null, every null field is replaced with a bogus date of 12/30/1899 (no idea why)

So basicly i just need to put where date = 12/30/1899 in my select but its not working, and i have no idea why.

 

[dgillz]

How about a statement for date is less than 1/1/1900? Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[kenhamady]

If IsNull doesn't work, then these aren't null values, but stored defaults. Did you try Alley's options?

= Date(0,0,0)
< Date(1901,1,1) Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

Also try this: under file, options, make sure the &quot;convert null field values to default&quot; checkbox is NOT CHECKED.

Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[savok]

LOL yes! it worked!

why didnt i think of it

thank you very much