Left join (sorting makes the empty fields show on top)

[Geel]

Hello,

Im busy on a project that's building reports out of entry's in an
access system.

'The data is build on some different levels.

Savings_ID
Contract_ID
Department_ID
Total

Now i did a left join that will show all the data in the department ID
( even if savings and contract are empty ). This is what i want and im
very pleased it's working.

But now i've got a new problem. Because of the left join empty fields
show up, wich is what i want. But the fields are showing on top of the
query. Making the report read empty fields where there should actually
be data. For instance the report_name should be shown but because of
this field being empty in the first record showing, it's showing an
empty field.

Is there any simple workaround or IFF statement wich can help me out ?

Thanks a lot,

Tim

 

[John Vinson]

But now i've got a new problem. Because of the left join empty fields
show up, wich is what i want. But the fields are showing on top of the
query. Making the report read empty fields where there should actually
be data. For instance the report_name should be shown but because of
this field being empty in the first record showing, it's showing an
empty field.

Is there any simple workaround or IFF statement wich can help me out ?

A couple. If you don't want to see the records with NULLs at all, you
can of course use a criterion IS NOT NULL.

If you want to see them for other purposes, you can add a calculated
field in the query:

SortKey: IsNull([fieldname])

This will be -1 for the NULL values, 0 for the non-NULL; sort it
descending and it will push the NULLs to the bottom of the list.

John W. Vinson[MVP]