Reporting on NULL values (DateTime included)

[rcplanecrasher1]

I am trying to report on any tickets (that are grouped) that have no journal entries. And report back to the report that there is no info. My hangup is DateTime. In the SQL Table it reads <null> however when I write my formula it asks for a date time. Any help would be appreciated...This is what i have so far.. I am failing at the isnull

if {Journal.CreatedBy} = "" then totext ("No Journal") else
if {Journal.NotesBody} = "" then totext ("No Journal") else
isnull ({Journal.CreatedDateTime}) then totext ("No Date")
else {Journal.NotesBody}

 

[lbass]

You are just missing an "if":

if {Journal.CreatedBy} = "" then totext ("No Journal") else
if {Journal.NotesBody} = "" then totext ("No Journal") else
[red]if[/red] isnull ({Journal.CreatedDateTime}) then
totext ("No Date") else
{Journal.NotesBody}

-LB

 

[rcplanecrasher1]

thank that made no errors.. However it is not returning the test ticket I have created that does not have a journal entry. So none of those fields are populated with info. When I put the formula in the details section it still does not populate my test ticket...

I have the 2 tables linked... If there is a journal entry all the tickets show up... However since they want to see tickets that have no journals (so they can ask whynot)this formula still does not pull the group record in and Print "No Journal

 

[Turkbear]

Hi,
If this is a record selection formula then this format is probably the one to use:
(Also confirm that the DataType for {Journal.CreatedDateTime}
is shown in the database as a DateTime.)

Trim({Journal.CreatedBy}) = "" OR Trim({Journal.NotesBody})= "" OR {Journal.CreatedDateTime}= Date(0,0,0)

Using this only those records with one or more of those with blanks ( or NULLs) will be returned.

In your report use 2 formulas to display the text you need:

@NoJournal

if 
(
Trim({Journal.CreatedBy}) = "" OR Trim({Journal.NotesBody}) = ""
)
Then 
"No Journal"

@NoDate

If {Journal.CreatedDateTime}= Date(0,0,0) 
Then
"No Date"

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
Now that you mentioned the link, be sure it is left-outer.

I also did not understand that you wanted records in addition to those specified in your original post..sorry.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

if
(
isnull({Journal.CreatedBy}) or
trim({Journal.CreatedBy}) = ""
) then
totext ("No Journal") else
if
(
isnull({Journal.NotesBody}) or
trim({Journal.NotesBody}) = ""
) then
totext ("No Journal") else
if
(
isnull ({Journal.CreatedDateTime}) or
{Journal.CreatedDateTime} = datetime(0,0,0,0,0,0)
) then
totext ("No Date") else
{Journal.NotesBody}

-LB

 

[rcplanecrasher1]

GOT it with the help from both of you. LB thank you for pointing out I was missing the if... and instead of a left outer join I needed a right outer... Thank you so much to the both of you..... I truely appreciate it.. I was going batty on this one... thanks again