functions in Control Source returning '#Error'

[thevillageinn]

I have a text box on my form which combines First and Last name fields. Sometimes either name has extra padding which I want to remove before displaying the data.

I was using the following functions in the Control Source for this field:

=Nz(Trim$([firstname]),"&quot & " " & Nz(Trim$([lastname]),"&quot

This works just fine as long as there is data in both fields, but when there is no data, the 'Nz' function is not properly filling in the null, even when I provide a value (something like: Nz(Trim$([firstname]), "n/a&quot )

Am I trying to do too many functions? I thought I needed the Nz function to prevent errors if/when I had a null value in one of the fields, but it's failing me.

Any suggestions?
Many thanks-
-Dan

 

[LarryDeLaruelle]

Dan:

The Nz function is used only with variant type data (as returned in a query result). It looks like you are using a text type control. Text controls would not be null but might contain an empty string.

If your form is based on a query, I would suggest doing the concatenation in the query. In the field cell put in:

FullName: [FirstName] & " " & [LastName]

If you are using the table directly, your expression in the control source would be:

=[FirstName] & " " & [LastName]

You can still use the trim functions:

=Trim([firstname]) & " " & Trim([Lastname])

I don't see why you need a fill character if the name field is empty. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[thevillageinn]

Larry,
Thanks for your response. I have added the Trim function to the query that I have set as the record source for the form. I also notice this method is faster because all of the concatenation is performed in the query and not on the form.

I did notice one thing in your response, where you said "I don't see why you need a fill character if the field name is empty"
I'm trying to figure out how you meant this. Did you mean...
1. Why do I need to add a space between the names if one of the fields is empty?

2. Why would I use the Nz function and add a character if the field is empty?

I'm assuming you meant #2, and in that case, I don't need a fill character, and was experimenting with Nz because using the Trim function only was returning "#Error".

If you meant #1, I would like to eliminate the space between the names if there is only data in the last name field, but I don't know how to do it easily.

Thanks again for your suggestion.
-Dan

 

[LarryDeLaruelle]

Dan:

My question was #2.

Glad it's working for you. Using a query as the record source was the way I was taught to do it. Gives you more flexibility than basing directly off the table.

The trade-off is that if you add fields to the table you also, most likely, will need to modify the query. Larry De Laruelle
ldelaruelle@familychildrenscenter.org