Compile error in Query Expression - Help

[vad77]

I am currently rebuilding one of my databases from scratch due changes in procedures. I felt it would be better to build a new DB from scratch instead of trying to make my current DB fit the new procedures.
With that said I have built my two main tables which have a 1 to 1 relationship. My last DB had about 75 fields and my new DB table would have been more so I broke it into the two tables.
I am using the following formula in my query to combine the First and Last name fields but I get a compile error. This is the same formula which I just copied and pasted from my other DB and then changed the field names.

Last First: IIf(IsNull([ContLastName]),IIf(IsNull([ContFirstName]),[ContCompanyCode],[ContFirstName]),IIf(IsNull([ContFirstName]),[ContLastName],[ContLastName] & ", " & [ContFirstName]))

Here is a screenshot of the error.



Can anyone explain why I am getting this error and how I can correct it?
Thank you for your responses.

[June7]

Can we assume if there is no last name there will not be a first name?

IIf(IsNull([ContLastName]), [ContCompanyCode], [ContLastName] & ", " + [ContFirstName])

Otherwise

IIf(Not IsNull([ContLastName]), [ContLastName] & ", " + [ContFirstName], IIf(Not IsNull([ContFirstName]), [ContFirstName], [ContCompanyCode]))

Any expression involving math operators and Null will return Null. The + is a very special operation character. It not only does number addition but also concatenates. So if you '+' text data and any field is Null, the expression will return Null.

Regarding use of IsNull http://allenbrowne.com/QueryPerfIssue.html


Best not to use spaces in naming convention. LastFirst would be better.

[vad77]

I tried without the space and get the same error. In my old database I have a space in the Name and it works fine.

This is from my old DB: Employee Name: IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[HiringAgency],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName]))

I wouldn't think the length of the field names in my new DB would cause the problem? I never use spaces in field names only underscores.
There is a reason why I use this formula instead of just doing: Name: [Last]&", "&[First]. I use the field "Employee Name" in my combo box code and at the top of my form to display the name.

This is the formula at the top of my form: =Nz([Employee Name],"Untitled")

[June7]

I edited my previous post while you were reading. Might review again.

I meant space in field name, fine in calculation.

[vad77]

The two formulas you provided in your reply work. They work in my old database. I tried the 2 formulas in my new database with the same result, compile error. So I tried them in my old database with the correct field names and they worked.
I did a Compact and Repair on my new DB and I still get the same compile error. I also tried to see if there is an issue with any kind of form controls. I created a quick form and tried to add a command button and get the error in the screenshot.

[June7]

That they work in one db tells me possible corruption in other db. Maybe corruption beyond repair.

Are you using custom function AppLoadString in both dbs? How does this function relate to the expressions?

If you want to provide db for analysis, follow instructions at bottom of my post.

[vad77]

I figured it out, it was a module I had created for table audits. What happened was that the statement "Option Compare Database" was in the module twice. I deleted the first instance and the query worked.
Thanks for helping me find the problem.