Only include a column value if it is not NULL

[Weon]

Hullo! I'm new to the world of MS Access so please forgive my ignorance!

On my form I have a Combo Box that pulls in the values from a seperate table using a SQL query. This combo box only displays one of the many values it pullsin, keeping the other hidden.

I then have a text box that has a expression that causes it to display all the other hidden values, dependant on what is selected in the combo box. My expression currently looks like this:

=OrgCombo.column(2) & ", " & Chr(13) & Chr(10) & OrgCombo.column(3) & ", " & Chr(13) & Chr(10) & OrgCombo.column(4) & ", " & Chr(13) & Chr(10) & OrgCombo.column(5) & ", " & Chr(13) & Chr(10) & OrgCombo.column(6) & ", " & Chr(13) & Chr(10) & OrgCombo.column(7)

This is basically displaying an address for whatever organisation is selected in the combo box. What I want it to do it to only display the value in OrgCombo.column(3) and it's following comma and linebreak if there is a value to display.

Currently, if there is no second line of the address (it is an optional field) I get something like:

123 Four Close,
,
Cleethorpes,
JN89 7HG,
8463218,
984374

[KnooKie]

You need to use the immediate IF statement (IIf) take a look in the help file for a description. i have entered it in below for column(3)....

=OrgCombo.column(2) & ", " & Chr(13) & Chr(10) & IIf(IsNull(OrgCombo.column(3)), "", OrgCombo(3) & ", ") & Chr(13) & Chr(10) & OrgCombo.column(4) & ", " & Chr(13) & Chr(10) & OrgCombo.column(5) & ", " & Chr(13) & Chr(10) & OrgCombo.column(6) & ", " & Chr(13) & Chr(10) & OrgCombo.column(7)

[Weon]

Ah, IIf is what I was after. It has to be said that the Help system is rubbish unless you know the exact terms of what you are looking for.

Many thanks Knookie, my problem is solved - now on to the next one!