Help with isNull expression

  • Thread starter Thread starter sankop
  • Start date Start date
S

sankop

I use Access 2002 to manage members in a neighborhood association. I
have a field for "Last" Name and one for "Spouse Last" to accommodate
members with different last names. I am trying to figure out how to
write an expression in a text box on a form that will replace empty
"Spouse Last" fields with the values in "Last" so the Spouse Last field
won't appear blank on the form.

Here are the expressions I've tried:

=IIf(IsNull([Spouse Last]),[Last],[Spouse Last]) returns #Error
=IIf(IsNull([Spouse Last]),([Last]),([Spouse Last])) returns #Error
=IIf(IsNull([Spouse Last]),([Last],[Spouse Last])) field is blank
=IIf(IsNull([Spouse Last]),Last,Spouse Last) missing operator

Thanks kindly for any and all help. I'm a self taught Access user with
a very basic understanding of Access.

Sandie
 
You can try

=Iif([SpouseLast] is null, [Last], [SpouseLast])

Eechhutti R.Rajasekaran
(e-mail address removed)
 
=IIf(IsNull([Spouse Last]),[Last],[Spouse Last]) returns #Error
Click to expand...

This is the correct syntax, and I'm not sure why it's giving you an
error. A simpler alternative which might work is

NZ([Spouse Last], [Last])

The NZ function returns its second argument if the first is NULL.

Just to check one possible reason for this error:
 
I used your expression you supplied, but I had to delete the old text
box and insert a new one for it to work. Now I need to add a component
for single members, the [spouselast] field must remain blank.

The expression should say something like, =IIf([Spouse Last] Is
Null,[Last],[Spouse Last])but if [Spouse First]is Null, leave blank

Can you help?

Thanks so very much!

Sandie
 
sankop said:
I use Access 2002 to manage members in a neighborhood association. I
have a field for "Last" Name and one for "Spouse Last" to accommodate
members with different last names. I am trying to figure out how to
write an expression in a text box on a form that will replace empty
"Spouse Last" fields with the values in "Last" so the Spouse Last
field won't appear blank on the form.

Here are the expressions I've tried:

=IIf(IsNull([Spouse Last]),[Last],[Spouse Last]) returns #Error
Click to expand...

As John Vinson points out, this expression is valid. If it returned
#Error, I wonder if it was because you put it in a text box named
"Spouse Last" or "Last". That would present Access with a circular
definition that could not be resolved.
 
Thanks. I resolved the circular reference which was causing the #error
I had two tables with fields named "last" in the query for the form.

Now I have another problem. I need the "Spouse Last" field to remai
blank for single members. I can make the following two expressions wor
separately, but I can't figure out how to make them work together.

=IIf([Spouse Last] Is Null,[Last],[Spouse Last])

=IIf([Spouse First] Is Null,” ”)

I'm trying to say:

If "Spouse Last" is blank, fill in the text box with "Last", otherwis
fill in the "Spouse Last" value, but if "Spouse First" is blank, leav
the text box blank.

Thanks again for everyone's help.

Sandi
 
sankop said:
Thanks. I resolved the circular reference which was causing the
#error. I had two tables with fields named "last" in the query for
the form.

Now I have another problem. I need the "Spouse Last" field to remain
blank for single members. I can make the following two expressions
work separately, but I can't figure out how to make them work
together.

=IIf([Spouse Last] Is Null,[Last],[Spouse Last])

=IIf([Spouse First] Is Null," ")

I'm trying to say:

If "Spouse Last" is blank, fill in the text box with "Last", otherwise
fill in the "Spouse Last" value, but if "Spouse First" is blank, leave
the text box blank.

Thanks again for everyone's help.

Sandie
Click to expand...

If I understand you correctly, this should do it:

=IIf([Spouse Last] Is Null,[Last],
IIf([Spouse First] Is Null,Null,[Spouse Last]))

Note: I broke that expression onto two lines for clarity, but actually
it must all be entered on one line in the text box's ControlSource.
 
You may try this:

Iif([Spouse First] is not null and [Spouse Last] is null, [Last],
Iif([Spouse First] is null and [Spouse Last] is null, " ", [Spouse Last]))

Eechhutti R.Rajasekaran
 
Thank you all! The expression John supplied did the trick! I appreciate
everyone's help and kindness.

Sandie :p
 
Back
Top