query formula

[CraigDouglas]

Not sure where to ask my question? I am working on my database and have a formula that puts the company name in if there is no information in the FirstName & Surname field. My problem is that I also want it to take into account the middle name field. I messed around with the formula and tried to get it to put the company name if there is no FirstName, MiddleName & Surname. Here is the formula I have:
FullName: IIf(IsNull([CustomerSurname]),IIf(IsNull([CustomerFirstName]),[CustomerCompanyName],[CustomerFirstName]),IIf(IsNull([CustomerFirstName]),[CustomerSurname],[CustomerFirstName] & " " & [CustomerSurname]))

 

[plog]

I also want it to take into account the middle name field

Kinda vague. How do you want to take it into account? Perhaps it would be best to demonstrate your issue with data. Please provide examples of every case and what you hope to end up with each one.

 

[CraigDouglas]

Thank you for the reply. I want the formula to put in the name of the Customers company if there is no information in the FirstName field, the MiddleName field & the Surname field. At the moment the formula works if there is no information in the FirstName field & the Surname field. If there is information in the three name fields I want it to produce a full name ie. FirstName &" "&MiddleName &" "&Surname. Hope this clears it up a bit. Not sure how to represent the data information you ask for?

 

[plog]

Not sure how to represent the data information you ask for?

I have this:
ID, FirstName, MiddleName, LastName, CompanyName
1, Steve, R, Smith, Nike
2, , , , Microsoft
3, David, , , Lexus

I want this:
1, Steve R. Smith
2, Nike
3, Lexus

 

[Gasman]

Not sure where to ask my question? I am working on my database and have a formula that puts the company name in if there is no information in the FirstName & Surname field. My problem is that I also want it to take into account the middle name field. I messed around with the formula and tried to get it to put the company name if there is no FirstName, MiddleName & Surname. Here is the formula I have:
FullName: IIf(IsNull([CustomerSurname]),IIf(IsNull([CustomerFirstName]),[CustomerCompanyName],[CustomerFirstName]),IIf(IsNull([CustomerFirstName]),[CustomerSurname],[CustomerFirstName] & " " & [CustomerSurname]))

Why not concatenate the three name fields and if len = 0 then populate the company field?

 

[CraigDouglas]

Thank you to those who are trying to help me.

 

[Minty]

To elaborate on Gasman's suggestion;

IIf(Len([CustomerFirstName] & [CustomerMiddleName] & [CustomerSurname])>0,Trim([CustomerFirstName] & " " & [CustomerMiddleName] & " " & [CustomerSurname]),[CustomerCompanyName])

 

[CraigDouglas]

Thank you Minty. I will try your formula. Craig

 

[CraigDouglas]

Thank you minty that worked. Craig