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

P

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?
 
M

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
 
K

KARL DEWEY

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top