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

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

Post by cGpv » Sun, 14 Feb 2010 02:35:01


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?
 
 
 

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

Post by Microsoft » Sun, 14 Feb 2010 02:46:19

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

 
 
 

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

Post by S0FSTCBERV » Sun, 14 Feb 2010 02:57:01

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


--
Build a little, test a little.
 
 
 

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

Post by John W. Vi » Sun, 14 Feb 2010 04:14:41


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.

--

John W. Vinson [MVP]