Access query with IIF statements is null for a field and not null

[pjo]

I am trying to insert a comma between [name] and [section name] field if the
[name] field is not null - but I don't want the comma if the [name] field is
null

RPTNAME: IIf([name]=" ",[section name],IIf([name]>" ",[name] & ", " &
[section name]))

But the RPTNAME comes up blank if there is not data in the [name] field.

Any suggestions?

 

[Microsoft Access]

You want to use the isnull() function probably.

if there is no data in the [name] field to test for this you would
if(isnull([name],[section name],[name] & ", " & [section name])

in your test you are just testing if the string is an empty string or in
your case a string with one space in it
difference between "" and " "

hope that helps

 

[KARL DEWEY]

Try this --
RPTNAME: IIf([name] Is Null OR [name] = "", [section name], [name] & ", " &
[section name])

 

[John W. Vinson]

I am trying to insert a comma between [name] and [section name] field if the
[name] field is not null - but I don't want the comma if the [name] field is
null

RPTNAME: IIf([name]=" ",[section name],IIf([name]>" ",[name] & ", " &
[section name]))

But the RPTNAME comes up blank if there is not data in the [name] field.

Any suggestions?

There's a sneaky trick you can use which depends on the fact that both & and +
are string concatenation operators; but & treats a NULL as a zero length
string, and + returns NULL if either argument is NULL: so you could use

RPTNAME: ([name] + ", ") & [section name]

as the calculated field.