Need Help with SQL Statement used in Filter Macro

[Jgk]

SELECT IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName])) AS [File As], IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName])) AS [Customer Name], Customers.*


FROM Customers
ORDER BY IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName])), IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName]));



The problem I have is it lists contact as Customer Name and I Need Customer Name to be Company.


In my forms it is pulling:

Customer Name	File As	ID	Company	LastName	FirstName
Dan Smith	Smith,Dan	2	ABC	Smith	Dan
JCF	JCF	1	JCF

The null statement is used in a macro filter needed in forms in embedded macro.

Thanks for your assistance.

[June7]

There can be FirstName or LastName without the other? Consider:

Nz(LastName & IIf(IsNull(FirstName), Null, IIf(IsNull(LastName), Null, ", ") & FirstName), Company) AS FileAs

If you want Company to list as customer name, then why the IIf for CustomerName?

[Jgk]

You are correct I do not need Customer name, but the File as is critical due to dcounts and lookup fields in forms. I used the Desktop Project Management Access template and modified to add Customer table and Customer Extended Query. The Employee Extended Query in sample dbase has the query I am trying to modify the File As:
I renamed that table (Contractors) and query (Contractors Extended). I made it work by not having first and last names just (Contractor) for Contractors table and Company for Customers table. I have hidden fields for first and last name entry on forms. It is sad but it works.

I am having trouble putting your sql statement in Query.

Thanks

[June7]

What is issue with the suggested expression? It worked in my test.

[Jgk]

Thanks again,

This is not the project just enough info to see my predicament. The queries for both customer and contractor extended are used in forms and need the Customer Name and Contractor Name to pull to forms correctly( not first and last name but field Company from Customer table and field Contractor from Contractor table. In my actual database I made work by removing first and last names from both Contractor and Customer Tables and Forms. However, the embedded macros are adding Customers and Contractors which I like and want to utilize.

I just did not understand where your code had to go after If statement.

[June7]

What I suggest is an expression to create a field in query. It simply replaces the longer and unnecessarily complicated expression you are using.

SELECT Customers.*, Nz(LastName & IIf(IsNull(FirstName), Null, IIf(IsNull(LastName), Null, ", ") & FirstName), Company) AS FileAs
FROM ...

[Jgk]

Got It Thank you very much,
SELECT Contractors.*, Nz([LastName] & IIf(IsNull([FirstName]),Null,IIf(IsNull([LastName]),Null,", ") & [FirstName]),[Contractor]) AS [Contractor Name]
FROM Contractors;

It worked had to change header fields than everything good .