IIF IsNull

[angie]

I have a form called invoices that I have a field in that is called Bill To.


I would like the Bill To field to auto fill using information from the form customer.

I have tried the following. =IIf(IsNull([Forms]![CUSTOMER]![Company/Owners Name]),[Forms]![CUSTOMER]![FIRSTNAME])

if I remove the first part and just have the default =[forms]![customer]![firstname] it works but I need it to use the company/owners name if it is not null.

Thanks Angie

[orange]

IIF has 2 parts.

Iif (some condition, true part, false part)
I think you need
=IIf(IsNull([Forms]![CUSTOMER]![Company/Owners Name]),[Forms]![CUSTOMER]![FIRSTNAME],[Forms]![CUSTOMER]![Company/Owners Name])

The part in Green, is used if your criteria IsNull([Forms]![CUSTOMER]![Company/Owners Name]) is False

[Perceptus]

Instead of
=IIF(IsNull([Forms]![CUSTOMER]![Company/Owners Name]),[Forms]![CUSTOMER]![FIRSTNAME])

I would use something like this
=Nz([Forms]![CUSTOMER]![Company/Owners Name],[Forms]![CUSTOMER]![FIRSTNAME])

[angie]

Thanks this did work and I wanted to add to it and when I did It would not work I think it has something to do with the chr(13) and chr (10) (to force a new line)
here is what I tried. Thanks again Angie


=IIf(IsNull([Forms]![CUSTOMER]![Company/OwnersName]),[Forms]![CUSTOMER]![FIRSTNAME] & " " &[Forms]![Customer]![LastName] & Chr(13) & Chr(10) &[Forms]![customer]![Street Number] & " " & [Forms]![customer]![Address]& Chr(13) & Chr(10) & [Forms]![customer]![City] & " "&[Forms]![customer]![state]![forms]![customer]![zip],[Forms]![customer]![Company/OwnersName])

[June7]

Nz is still simpler. Missing & between state and zip.

=Nz([Forms]![CUSTOMER]![Company/OwnersName], [Forms]![CUSTOMER]![FIRSTNAME] & " " & [Forms]![Customer]![LastName]) & Chr(13) & Chr(10) & [Forms]![customer]![Street Number] & " " & [Forms]![customer]![Address] & Chr(13) & Chr(10) & [Forms]![customer]![City] & " " & [Forms]![customer]![state] & " " & [forms]![customer]![zip]

Why are you using the Forms! qualifier? If this textbox is on the same form as the fields/controls referenced in expression, the form name is not needed.

Why even concatenate instead of separate textboxes?

[Perceptus]

You can replace Chr(13) & Chr(10). So if you had
=Nz([Object],[Value1] & chr(13) & chr(10) & [value2])
you could change it to
=Nz([Object],[Value1] & vbcrlf & [value2])

[June7]

Unfortunately, vbCrLf does not work in textbox ControlSource nor in Access query object, it is a VBA constant.

[angie]

This does not work the nz = the field shows #name?

there is two forms I am pulling information from another form.

[June7]

See posts 5 and 7.

[angie]

"Orange"Thanks this did work and I wanted to add to it and when I did It would not work I think it has something to do with the chr(13) and chr (10) (to force a new line)
here is what I tried. Thanks again Angie


=IIf(IsNull([Forms]![CUSTOMER]![Company/OwnersName]),[Forms]![CUSTOMER]![FIRSTNAME] & " " &[Forms]![Customer]![LastName] & Chr(13) & Chr(10) &[Forms]![customer]![Street Number] & " " & [Forms]![customer]![Address]& Chr(13) & Chr(10) & [Forms]![customer]![City] & " "&[Forms]![customer]![state]![forms]![customer]![zip],[Forms]![customer]![Company/OwnersName])

[June7]

The Chr(13) & Chr(10) work just fine for me.

You are still missing the & between state and zip. See post 5.

[angie]

Ok I had to correct a few things but I got the NZ code to work, thank you very much Angie