Nulls in expressions

[dtburdick]

I want to create a field on my report that has the following:

[Prefix] & " " & [FirstName] & " " & [LastName]

The problem is, [Prefix] can be null so I get an #error in the field when it displays. I've tried using Nz() and iif(isnull()) to no avail.

Any ideas?

Thanks

 

[Rob.Mills]

Try this:

IIf(IsNull([Prefix],[FirstName] & " " & [LastName],[Prefix] & " " & [FirstName] & " " & [LastName])

 

[dtburdick]

This makes no sense.

I put a value in for every Prefix but I still get an error.

These work:

Prefix
AttendeeFirstName
=[AttendeeFirstName]

This doesn't:

=[Prefix]

I looked at the field definitions and they are the same.

 

[Rob.Mills]

Not sure what to tell you. I've got a similar formula to work. Give me the exact names of your fields. Also copy and paste the formula you've got in here.

 

[dtburdick]

Those are the names and formulas. I broke it down as simple as I can and it doesn't work. It works for [AttendeeFirstName] but not for [Prefix].

I even tried creating another column named [Prefix2] and copied in the data from [Prefix] to see if that would work but I still get the error.

 

[Rich]

The syntax here isn't correct

IIf(IsNull([Prefix],[FirstName] & " " & [LastName],[Prefix] & " " & [FirstName] & " " & [LastName])

try
IIf(IsNull([Prefix]),[FirstName] & " " & [LastName],[Prefix] & " " & [FirstName] & " " & [LastName])

 

[Rob.Mills]

Oops... Didn't notice my typo. Thanks Rich.

 

[dtburdick]

This is something WAAAAAY more basic than that.

If someone has time, please try this. I'm totally under the gun here and this is a complete show stopper for me.

The following doesn't work:

Create baseline Event Management database using the wizard.

Add attendee John Doe.

Run "Attendee Listing" report to see that it works.

Edit "Attendee Listing" report. Make detail section bigger and drag "AttendeeFirstName" to the detail section.

Edit Control Source of that field and set it to "=[AttendeeFirstName]"

Run report.

I get #error.

 

[Rob.Mills]

I got the same error as you. But if you're only going to have one field in the source you don't need the equal sign or the brackets. Just select the field.

 

[dtburdick]

I just figured it out. Thanks for trying it.

My problem is because the field is named "Prefix". When I changed the name to "AttendeePrefix" it worked fine.

However, I still don't know why the scenario I stated above doesn't work. But, I don't really care.

Thanks all for your help.