How to treat null values in query joins

[keith2511]

I have a database were the tables that contain volatile data include codes which are used in joins to other tables that hold the description associated with that code.



There are instances in the volatile data where fields that contain codes are rightly empty.

In these instances I would like the query results to show a piece of text such as "None" or "N/A" but this is beyond my understanding of the software.

Can this be done and if so, how?

Thanks in advance.

[aytee111]

You cannot join on null fields so you will need to rethink your query. You can create an initial query which changes nulls to None but then you can't use it in a join. It may help if you created a code with the description None so that your joins will be valid.

[keith2511]

Thanks for your reply. I am not the data owner nor have any influence on changing nulls to another value.
I was thinking more about if it is possible to use an IF statement that checks for a null and returns a "none" in the query results, else returns the value of the join if the field is not null.

Is that possible?

[CJ_London]

not quite clear what you are asking, some example data and the required outcome would be helpful.

It may be that a left join will do what you require

SELECT TableA.Joinfield, nz(TableB.Joinfield,"None") AS BJoinfield
FROM TableA LEFT JOIN TableB ON TableA.Joinfield=TableB.Joinfield

Alternatively you can use the format property to display None if null. For text it would be

@:"None"

and for Numbers

0;0;0;"None"

Benefit of using the format property is it does not change the underlying value which may or may not be useful for your project

[keith2511]

Thanks for your reply Ajax.

Below is an example of data and required results. Just to confirm that I have no control over the data and have to live with what I receive.

[CJ_London]

in that case, my initial suggestion would work

SELECT TableA.Surname, nz(TableB.Make,"None") AS BJoinfield
FROM TableA LEFT JOIN TableB ON TableA.Car_Type=TableB.Car_Type

[keith2511]

Thank you Ajax. It has worked