Union query not calculating IIF

[steve1111]

Hello all,

I am building a union query and it is not calculating an IIF statement correctly. I have three name fields, Last, First and Prefered, so if William prefers Bill then the prefered field states Bill, otherwise that field is left null.

When i build a query for just the name this works great (Use last name and prefered if there is one, else use last name and first name.

SELECT dbo_Drivers.ID, [dbo_Drivers]![LastName] & ", " & IIf(IsNull([dbo_Drivers]![PreferredName]),[dbo_Drivers]![FirstName],[dbo_drivers]![PreferredName]) AS Name

However when i copy that text as part of a union query then any time the driver has a prefered name it is only showing up as the last name only.

Not sure why it works in the stand alone query and not the union.

thanks for any help!

 

[arnelgp]

why did you use the bang (!), use the dot (.) as your field qualifier.

 

[steve1111]

I have limited experience in SQL so i built each query of the union query in the Access QBE and pasted the SQL code it provides.

 

[arnelgp]

how about:

SELECT dbo_Drivers.ID, [dbo_Drivers]![LastName] & ", " & Nz([dbo_Drivers].[PreferredName], [dbo_Drivers]![FirstName] & "") AS Name