IsNull

[Former Member]

Post Author: hd1

CA Forum: Formula

I am trying to include a null value in my report. I've searched this forum and and found a formula like this one

If IsNull({WebAddress.Value}) then "N/A" else {WebAddress.Value}

It doesn't work though. I still get the same results. I think my situation is a tad different though. I am trying to include results that are missing information obviously, however the entire WebAddress table is empty for certain entries. So I think that is where my problem lies. I thought this formula would work because it saying that if the Value field is null then disply "N/A" or the value of the field. I still get the same results, it's not including entries that have no webaddress listed. Has anyone had a situation similar? The last post I looked at this issue was resolved but I do not know why it is not working for me. Any feedback would be most appreciated. Thank you!

[Former Member]

Post Author: hd1

CA Forum: Formula

If it is pulling non matching rows then how do you know if the data your are querying is correct?

[Former Member]

Post Author: CrystalGirl

CA Forum: Formula

A full outer join, pulls the matches, and then it also pulls all non matching rows from the right and left table.

[Former Member]

Post Author: hd1

CA Forum: Formula

Thank you for your responses, both were helpful and I appreciate the link. I can see the difference between the different joins now. I can see how this made the difference. There were no matches in my webaddress table so doing a left outer join forced the query to pull everything from the table. So I guess another question is, what is a full-outer join then? Does that mean it pulls from both tables regardless if there are any matches or not?

[Former Member]

Post Author: CrystalGirl

CA Forum: Formula

When you join to the table that holds web addresses you will need to use a left outer join, and this will allow you to include records from the main table where they do not exist on the web address table.

Hope this helps

[Former Member]

Post Author: yangster

CA Forum: Formula

http://www.w3schools.com/sql/sql_join.aspa little refresher for you on the types of joins available

[Former Member]

Post Author: hd1

CA Forum: Formula

YES! That was the problem, I was doing an inner join on the tables but once I did a full-outer join it started displaying the information. It also did it on a right-outer join but not a left. I am a little confused on the joins. Could you explain those a bit?

[Former Member]

Post Author: Jagan

CA Forum: Formula

"the entire WebAddress table is empty for certain entries"

Are you outer joining to the table?