user function in a query

[JC]

The following function creates a display name from a [last name] and
[her name] and [his name]. I call the function from a query. When I
run the query those records that have BOTH a his and her name appear
correctly, but if one of the names is blank "#ERROR" appears in the
field where the combined name should appear. Why doesn't the function
process the else statement correctly? The If statement works just
fine.
Thank you in advance.
- jc -

This is how it is called from the query:
name:cname([his_name], [her_name])

The function: (resides in a module)
Public Function cName(strHisName As String, strHerName As String) As
String
On Error GoTo Err_cName

If (Len(Trim(Nz(strHisName))) > 0) And (Len(Trim(Nz(strHerName))) >
0) Then
cName = Trim(strHisName) & " & " & Trim(strHerName)
Else
cName = Trim(Nz(strHisName)) & Trim(Nz(strHerName))
End If

Exit_cName:
Exit Function

Err_cName:
cName = ""
MsgBox "Runtime Error # " & Err.Number & vbCrLf & vbLf &
Err.Description
Resume Exit_cName

End Function

[jimfortune]

JC wrote:

The following function creates a display name from a [last name] and
[her name] and [his name]. I call the function from a query. When I
run the query those records that have BOTH a his and her name appear
correctly, but if one of the names is blank "#ERROR" appears in the
field where the combined name should appear. Why doesn't the function
process the else statement correctly? The If statement works just
fine.
Thank you in advance.
- jc -

This is how it is called from the query:
name:cname([his_name], [her_name])

The function: (resides in a module)
Public Function cName(strHisName As String, strHerName As String) As
String
On Error GoTo Err_cName

If (Len(Trim(Nz(strHisName))) > 0) And (Len(Trim(Nz(strHerName))) >
0) Then
cName = Trim(strHisName) & " & " & Trim(strHerName)
Else
cName = Trim(Nz(strHisName)) & Trim(Nz(strHerName))
End If

Exit_cName:
Exit Function

Err_cName:
cName = ""
MsgBox "Runtime Error # " & Err.Number & vbCrLf & vbLf &
Err.Description
Resume Exit_cName

End Function

Instead of:

cName = Trim(Nz(strHisName)) & Trim(Nz(strHerName))

Try:

cName = Trim(Nz(varHisName,"")) & Trim(Nz(varHerName,""))

Hint: Use variant variables so that Null values can be dealt with.

It would also be good to check for the case where both varHisName and
varHerName are Null. Return "Mr. & Mrs."?

James A. Fortune

[fredg]

On 10 Aug 2005 11:07:48 -0700, JC wrote:

The following function creates a display name from a [last name] and
[her name] and [his name]. I call the function from a query. When I
run the query those records that have BOTH a his and her name appear
correctly, but if one of the names is blank "#ERROR" appears in the
field where the combined name should appear. Why doesn't the function
process the else statement correctly? The If statement works just
fine.
Thank you in advance.
- jc -

This is how it is called from the query:
name:cname([his_name], [her_name])

The function: (resides in a module)
Public Function cName(strHisName As String, strHerName As String) As
String
On Error GoTo Err_cName

If (Len(Trim(Nz(strHisName))) > 0) And (Len(Trim(Nz(strHerName))) >
0) Then
cName = Trim(strHisName) & " & " & Trim(strHerName)
Else
cName = Trim(Nz(strHisName)) & Trim(Nz(strHerName))
End If

Exit_cName:
Exit Function

Err_cName:
cName = ""
MsgBox "Runtime Error # " & Err.Number & vbCrLf & vbLf &
Err.Description
Resume Exit_cName

End Function

Note:
regarding > name:cname([his_name], [her_name]) <
Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
Are you just trying to combine the His and Her names with a "&" if
they both exist?
No need for a user Defined function.
Just copy the below expression directly into the query grid.

BothNames: Nz([strHisName],[strHerName]) & IIf(Not
IsNull([strHisName]) And Not IsNull([strHerName])," & " &
[strHerName])

Mark & Beth
Mark
Beth

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[the chiller]

There are many problems in this function. First off, the Nz function
normally returns 0 if the argument is null. So I guess what you would
do for a string is Nz(str, "").

Second off your If statements should be If (Len(Trim(strHisName & "")))

0). There is no need for Nz here since nulls are avoided by concatenating the empty string.

Third off your function is more complicated than it should be. This is
what it can be reduced to:
Trim(strHisName) & IIf(IsNull(strHisName) or IsNull(strHerName), ""," &
") & Trim(strHisName)

I also thought about using the + operator like this:
Trim(strHisName) & (" & " + Trim(strHisName))

but couldn't see how it would work in your case.

[Salad]

JC wrote:

The following function creates a display name from a [last name] and
[her name] and [his name]. I call the function from a query. When I
run the query those records that have BOTH a his and her name appear
correctly, but if one of the names is blank "#ERROR" appears in the
field where the combined name should appear. Why doesn't the function
process the else statement correctly? The If statement works just
fine.
Thank you in advance.

As others have noted, you can use NZ. However, in your function you
care declaring His/Her names to be strings. Why not declare them as
Variants and then check in the function if His/Her is null or not.

[JC]

Thank you for all the replys and the constructive comments. I have
much to learn... I guess I have had the wrong understanding of Nz up
until this point.
A couple of questions:
If you create a record and all the string fields are not filled in,
don't they have a value of null at that point? They haven't been
initialized, right? So how do you test for that?

Fred, that expression you provided does just what I want. Thank you.

- jc -

[Salad]

JC wrote:

Thank you for all the replys and the constructive comments. I have
much to learn... I guess I have had the wrong understanding of Nz up
until this point.
A couple of questions:
If you create a record and all the string fields are not filled in,
don't they have a value of null at that point? They haven't been
initialized, right? So how do you test for that?
Open up your table. Is there a default value? The default value is
what is inserted into the field when the record is created and no value
provided. If blank, it will be null for new records.

IsNull() is one way to test for null.

Fred, that expression you provided does just what I want. Thank you.

- jc -

[JC]

Thank you - as I said before, I have much to learn.

[David W. Fenton]

"JC" <jc********@gmail.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

The following function creates a display name from a [last name]
and [her name] and [his name]. I call the function from a query.
When I run the query those records that have BOTH a his and her
name appear correctly, but if one of the names is blank "#ERROR"
appears in the field where the combined name should appear. Why
doesn't the function process the else statement correctly? The If
statement works just fine.

I believe it was Trevor Best who recommended this method for
concatenating Lastname + Firstname without requiring a bunch of
conditionals. It depends on the use of the + operator to propagate
Nulls.

Mid(("12" + LastName) & (", " + FirstName), 3)

If either LastName or FirstName is Null, that part of the equation
returns Null. As long as either is not Null.

A variation this should be able to handle your problem, without
needing to make it into an external function.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"JC" <jc********@gmail.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

Thank you - as I said before, I have much to learn.

A couple of helpful articles about Nulls:

http://allenbrowne.com/casu-02.html

http://allenbrowne.com/casu-11.html

http://allenbrowne.com/casu-12.html

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc