Replacing null values in text fields

[Clive]

Hi,

i have created a query from just one table, which is basically a list of
Financial transactions.

There is a field called supplier code, in this query.

Most of the transactions have a supplier code, but there are a few that don't.

i've got another table that has the supplier name against the supplier code.

i want to bring this table into the query, so that i can show the supplier
names against those records in the query that have a supplier code.

when i add this table and link the 2 tables up and put the supplier name
into the query. The query excludes the records with no supplier code.

This is presumably, because they contain null values.

I've been reading about the Nz function and i think this is what i need to
use.

i've tried this, but it still only brings through the records with a
supplier code and not all of them.

How i've done this is to go into the design mode of the query and in the
column that would have null values i have click "build"

in here i have the following formula.

Nz([Supplier Name],"No")

Am i doing anything wrong?

 

[Rob Parker]

Hi Clive,

You don't need the Nz function. What you need to do is change the join type
between the two tables from the default Inner join to an Outer join.
Right-click on the join line in the query design grid, select Join
Properties, then choose the option to show all records from the transactions
table and only thoses records fromthe supplir table where the joined fields
are equal.

HTH,

Rob