Null DateTime Value 2

[Hillary]

I use Date(0,0,0) for null Date.

Does anyone know what the formula for null DateTime is?

Thanks for your help.

Hillary

 

[synapsevampire]

I don't think that Crystal recognizes a date() in a formula set to null, if you create a formula containing:

@Nulldate
date(0,0,0)

and display it, it appears as nothing, but if you test it with:

isnull({@Nulldate})

You'll get false.

-k

 

[Hillary]

The IsNull isn't working either. I am using this fomula in the conditional section for backround color.

If IsNull({LABOR_TICKET.ACT_CLOCK_IN}) then Color(100, 250, 200) else Color(125, 225, 175)

The Labor_Ticket Table has Clock_In, Actual_Clock_In, Clock_Out and Actual_Clock_Out. I want the backround color to change on those records where Actual_Clock_In or Actual_Clock_Out is null.

Thanks for your help!

Hillary

 

[lyanch]

Is the data actually null in the DB? The statement that you gave works fine with my null dates.

Lisa

 

[synapsevampire]

Check the setting:

File->Report options->Convert Null Field Values to Default.

If this is selected, you can't test for null, if you're certain that the database has nulls (they may be using a default date), uncheck this and you'll be fine.

Another thing to consider is to check to see if the date = date(0,0,0)

-k

 

[Hillary]

I have the Convert Null Field Values to Default checked. I don't know why but I have alway assumed this convered null Number Fields to 0 where applicable. I never realized this could affect other datatype fields. What "default" could this be converting a DateTime field to?

I can't test Date = Date(0,0,0) for this field becuase it is the wrong DataType. I use Date = (0,0,0) in other reports which have the same settings so I'm pretty sure it would work in this one. This brings me back to my original question; What is the equivilant DateTime function for Date(0,0,0)?

Thanks,
Hillary

 

[lyanch]

How about datetime(0,0,0,0,0,0)? I don't know if that is what it is converting to.. but it makes sense..


Lisa

 

[Hillary]

You right it does make sence but I had already tried that without success. Thanks for the suggestion.

 

[lyanch]

Where there is a will there is a way....

The following returns true if the original datetime was null:

not ({table.datetimefield} = datetime(datepart("yyyy",{table.datetimefield}),datepart("m",{table.datetimefield}),datepart("d",{table.datetimefield}),datepart("h",{table.datetimefield}),datepart("n",{table.datetimefield}),datepart("s",{table.datetimefield})))


Lisa

 

[Madawc]

My database doesn't ever record time, but whenever a date is set, it seems to come out as midnight regardless. Madawc Williams
East Anglia
Great Britain

 

[Hillary]

Lisa,

Yes this works! I'm only looking at the Act_Clock_In. The formula returns "True" where the field is not populated and "False" where it is.

So why doesn't the formula If IsNull({LABOR_TICKET.ACT_CLOCK_IN}) then Color(100, 250, 200) else Color(125, 225, 175) work?

Thanks for your help!

Hillary

 

[lyanch]

The isnull doesn't work because you have the "convert nulls to default" checked. I am not certain why the DateTime(0,0,0,0,0,0) doesn't work because all of those little formulas above evaluate to 0 when there isn't an entry.. So apparently LABOR_TICKET.ACT_CLOCK_IN doesn't equal itself even though it isn't null? Not sure what is going on there.. but it works.

Lisa

 

[ajca]

Try using:
Datetimetodate({table.datetimefield}) = Date(0,0,0)
It always seems to work for me.