Combining fields - iif??

[annaisakiwi]

Hi guys



I'm setting up a contacts database.

I have 4 fields:
Name 1
Name 2
Surname
Company Name

What I would like to happen is:
If all fields have data then, Name 1 & Name 2 Surname, Company Name
If any of the fields are null - then don't enter them, if only Name 1 or Name 2 then won't need the & and if only the Company Name, won't need the , before it.

Is anyone able to help me??

Cheers Anna

[annaisakiwi]

This is what I've got so far... although i think i'm totally mucking this up:

Contact Name: IIf(IsNull([Name 1]),IIf(IsNull([Name 1]),[Surname],[Name 1]),IIf(IsNull([Name 1]),[Name 1],[Name 1] & " & " & [Name 2] & " " & [Surname]))

[ConneXionLost]

Hi Anna,

Try this:

Contact: IIf(IsNull([Name 1]) Or IsNull([Name 2]) Or IsNull([Surname]) Or IsNull([Company Name]),"",[Name 1] & " & " & [Name 2] & " " & [Surname] & ", " & [Company Name])

Cheers,

[annaisakiwi]

Hi Anna,

Try this:

Contact: IIf(IsNull([Name 1]) Or IsNull([Name 2]) Or IsNull([Surname]) Or IsNull([Company Name]),"",[Name 1] & " & " & [Name 2] & " " & [Surname] & ", " & [Company Name])

Cheers,

Cheers that worked when all the fields are filled in :-)

How do I get it so it combines the fields, even when one is empty thou? thanks heaps for the help - such a beginner!

[ssanfu]

How about this????

ContactName: nz([name1],"") & IIf(IsNull([name1]),""," ") & nz([name2],"") & IIf(IsNull([name2]),""," ") & nz([Surname],"") & IIf(IsNull([companyname]),"",", ") & Nz([companyname],"")

You might have to edit this a little. I never use spaces in field names..... it causes too many problems.

[annaisakiwi]

How about this????

ContactName: nz([name1],"") & IIf(IsNull([name1]),""," ") & nz([name2],"") & IIf(IsNull([name2]),""," ") & nz([Surname],"") & IIf(IsNull([companyname]),"",", ") & Nz([companyname],"")

You might have to edit this a little. I never use spaces in field names..... it causes too many problems.

Wicked thats pretty much it! THANKS HEAPS!

one little last question though... If there is a Name 1 and Name 2 - I want to put '&' inbetween them... is this possible? again thanks sooooo much for all the help.

[ssanfu]

one little last question though... If there is a Name 1 and Name 2 - I want to put '&' inbetween them... is this possible? again thanks sooooo much for all the help.

Yes, but you have to be able to define a rule to determine when you add the '&'.


Hmmmm, maybe this:

ContactName2: nz([name1],"") & IIf(IsNull([name1]),"",IIf(IsNull([name2])," "," & ")) & nz([name2],"") & IIf(IsNull([name2]),""," ") & nz([Surname],"") & IIf(IsNull([companyname]),"",", ") & Nz([companyname],"")

BTW, its not a good idea to use spaces in object names (field names, form names, query names,...) Use CamelBackNames....even there are 3 words run together, it is still easy to read. Spaces will cause you headaches down the road..

[annaisakiwi]

Yes, but you have to be able to define a rule to determine when you add the '&'.


Hmmmm, maybe this:

ContactName2: nz([name1],"") & IIf(IsNull([name1]),"",IIf(IsNull([name2])," "," & ")) & nz([name2],"") & IIf(IsNull([name2]),""," ") & nz([Surname],"") & IIf(IsNull([companyname]),"",", ") & Nz([companyname],"")

BTW, its not a good idea to use spaces in object names (field names, form names, query names,...) Use CamelBackNames....even there are 3 words run together, it is still easy to read. Spaces will cause you headaches down the road..

Again thanks so much! it's pretty much perfect. I've taken your advise and renamed the fields so theres no gaps :-) The only thing i've got now if when there is just a company name - there is a ',' before it.. I tried having a little play around, trying to follow what you did - but I'm not getting any closer..

[ssanfu]

The only thing i've got now if when there is just a company name - there is a ',' before it.. I tried having a little play around, trying to follow what you did - but I'm not getting any closer..

I saw that, but.... well I didn't want to fix it because you might think I was a Nerd.

Well, I guess I just proved it........

ContactName2: nz([name1],"") & IIf(IsNull([name1]),"",IIf(IsNull([name2])," "," & ")) & nz([name2],"") & IIf(IsNull([name2]),""," ") & nz([Surname],"") & IIf(IsNull([companyname]),"",IIf(IsNull([Name1]) And IsNull([name2]) And IsNull([Surname]),"",", ")) & Nz([companyname],"")

[annaisakiwi]

I saw that, but.... well I didn't want to fix it because you might think I was a Nerd.

Well, I guess I just proved it........

ContactName2: nz([name1],"") & IIf(IsNull([name1]),"",IIf(IsNull([name2])," "," & ")) & nz([name2],"") & IIf(IsNull([name2]),""," ") & nz([Surname],"") & IIf(IsNull([companyname]),"",IIf(IsNull([Name1]) And IsNull([name2]) And IsNull([Surname]),"",", ")) & Nz([companyname],"")

LOL nerd! not at all... a big help definately!!! thanks again!!!

[ssanfu]

You're too kind...