Returning A String When Data is Not Entered into Table

[Garyjr123]

Afternoon,

The report I'm writing needs the enduser to enter answers to questions for the table to be populated (answerlist table). The answerlist table is linked to the visit.visit_id so when the questions are answered the visit.visit_id is the primary key and the foreign key is the answerlist.owner_id (how to link the tables).

I want to write a formula for a subreport where if the answerlist.owner_id does not have the visit.visit_id in the answerlist table the string that is returned is "Questions not answered". Is this possible if no information is entered in the table?

I was thinking about using an If, Then, Else statement.

If {visit.visit_id} does not equal {answerlist.owner_id} then "Questions not answered"

Thank you,

Gary

 

[lbass]

If you are using a left join FROM the visit table TO the answer table, you could use a formula like this:

if isnull({answerlist.owner_id}) then
"Questions not answered"

...assuming that you have NOT checked "convert nulls to default values" in file->report options.

-LB

 

[Garyjr123]

Hey LB,

Thanks for the help. The problem I have with the If isnull is that I don't see and null values in the table when I do a SQL Query. Will the If isnull still work even though I don't seem the null values in the table?

Gary

 

[lbass]

If the ID isn't represented in the table, then the result is null--this doesn't mean the TABLE contains nulls. Why not try it?

-LB

 

[Garyjr123]

LB,

I should know better then to ever doubt you you are a god among us mere mortals. Never again...

One more thing, How do I incorporate the following formula to the one you gave me?

{quest.question}+ ' ' +
If isnull ({answerlist.answer}) Then
{validanswer.answer}
Else
{answerlist.answer})

Gary

 

[lbass]

Your formula should work, although I'd add parens, unless either the question or the valid answer can also be null.

{quest.question}+ ' ' +
(
If isnull ({answerlist.answer}) Then
{validanswer.answer} Else
{answerlist.answer}
)

-LB