Function ??? (Access 2000)

Hi,

I created a function to concatenate names using knowledge
base article acc2000 Sample Function to Format Names
Several Different Ways.

I have 2 questions:
1. I'm using the following in a report and it's not
working. What am I doing wrong?

=NFormat([chrFirst],[chrMiddle],[chrLast],[chrSuffix],
[chrCompany],1)

2. I have a few records that only have a company name and
no first and last name. When I run the function, I only
want the company name to display and not a , or /.

LFM displays: , (It should be blank)
LFMC displays: / XYZ Company (It should be XYZ Company)

*****

Public Function NFormat(chrFirst As Variant, chrMiddle As
Variant, chrLast As _
Variant, chrSuffix As Variant, chrCompany As Variant,
varStyle As Variant)

On Error GoTo Err_NFormat

Dim strNewName As String

Select Case varStyle
Case "0", "FML"
strNewName = chrFirst & " " & (chrMiddle + " ") &
chrLast & (", " + chrSuffix)
Case "1", "LFM"
strNewName = chrLast & (", " + chrSuffix) & (", "
& chrFirst) & (" " + chrMiddle)
Case "2", "LFMC"
strNewName = chrLast & (", " + chrSuffix) & (", "
+ chrFirst) & (" " + chrMiddle) & (" / " + chrCompany)
Case Else
strNewName = ""
End Select

NFormat = Trim(strNewName)
Exit Function

Err_NFormat:
NFormat = "#Error"
End Function


Thank you.

jbc

The function is using the fact that Null will propagate through an equation.
That is the reason for the mix of + and & to do the concatenation. The +
will cause the Null to propagate and will therefore remove the information
on both sides of the + when one side is Null.

> Case "2", "LFMC"
> strNewName = chrLast & (", " + chrSuffix) & (", "
> + chrFirst) & (" " + chrMiddle) & (" / " + chrCompany)

To accomplish what you're after, you could change the & before the / to a +
and the + after it to & (rearranging the parenthesis also), but then that
would leave you with a trailing / if there is no company name. There are a
couple of ways to fix this. One, you could check for the leading / and
remove it.

If Left(strNewName, 1)="/" Then strNewName=Mid(strNewName, 2)
or
If strNewName="," Then strNewName=""

Two, you could create more "Cases" that will more clearly define what you
have and create statements to return appropriate formats for them.

--
Wayne Morgan
Microsoft Access MVP


"jbc" <anonymous@discussions.microsoft.com> wrote in message
news:1204b01c4425a$c5e007e0$a001280a@phx.gbl...
> Hi,
>
> I created a function to concatenate names using knowledge
> base article acc2000 Sample Function to Format Names
> Several Different Ways.
>
> I have 2 questions:
> 1. I'm using the following in a report and it's not
> working. What am I doing wrong?
>
> =NFormat([chrFirst],[chrMiddle],[chrLast],[chrSuffix],
> [chrCompany],1)
>
> 2. I have a few records that only have a company name and
> no first and last name. When I run the function, I only
> want the company name to display and not a , or /.
>
> LFM displays: , (It should be blank)
> LFMC displays: / XYZ Company (It should be XYZ Company)
>
> *****
>
> Public Function NFormat(chrFirst As Variant, chrMiddle As
> Variant, chrLast As _
> Variant, chrSuffix As Variant, chrCompany As Variant,
> varStyle As Variant)
>
> On Error GoTo Err_NFormat
>
> Dim strNewName As String
>
> Select Case varStyle
> Case "0", "FML"
> strNewName = chrFirst & " " & (chrMiddle + " ") &
> chrLast & (", " + chrSuffix)
> Case "1", "LFM"
> strNewName = chrLast & (", " + chrSuffix) & (", "
> & chrFirst) & (" " + chrMiddle)
> Case "2", "LFMC"
> strNewName = chrLast & (", " + chrSuffix) & (", "
> + chrFirst) & (" " + chrMiddle) & (" / " + chrCompany)
> Case Else
> strNewName = ""
> End Select
>
> NFormat = Trim(strNewName)
> Exit Function
>
> Err_NFormat:
> NFormat = "#Error"
> End Function
>
>
> Thank you.
>
> jbc

Your question #1... if that expression is being used as
the control source of a control in your report then be
sure the names in brackets are bound control names, not
just the field names in the report's recordsource.

Your question #2... try this modified version. I changed
several things to address your issues, one being that your
function can now also return a null value. The key point
to understand is how each punctuation gets concantinated
with a value, as Wayne has already mentioned.

Public Function NFormat(chrFirst As Variant, _
chrMiddle As Variant, chrLast As Variant, _
chrSuffix As Variant, chrCompany As Variant, _
varStyle As Variant) As Variant

On Error GoTo Err_NFormat
Dim strNewName As Variant

Select Case varStyle
Case "0", "FML"
strNewName = chrFirst & (" " + chrMiddle) _
& (" " + chrLast) & (", " + chrSuffix)
Case "1", "LFM"
strNewName = chrLast & (", " + chrSuffix) _
& (", " + chrFirst) & (" " + chrMiddle)
Case "2", "LFMC"
strNewName = (chrLast & (", " + chrSuffix) _
& (", " + chrFirst) & (" " + chrMiddle) + " / ") _
& chrCompany
Case Else
strNewName = ""
End Select

NFormat = Trim(strNewName)
Exit Function

Err_NFormat:
NFormat = "#Error"
End Function


>-----Original Message-----
>Hi,
>
>I created a function to concatenate names using knowledge
>base article acc2000 Sample Function to Format Names
>Several Different Ways.
>
>I have 2 questions:
>1. I'm using the following in a report and it's not
>working. What am I doing wrong?
>
>=NFormat([chrFirst],[chrMiddle],[chrLast],[chrSuffix],
>[chrCompany],1)
>
>2. I have a few records that only have a company name
and
>no first and last name. When I run the function, I only
>want the company name to display and not a , or /.
>
>LFM displays: , (It should be blank)
>LFMC displays: / XYZ Company (It should be XYZ Company)
>
>*****
>
>Public Function NFormat(chrFirst As Variant, chrMiddle As
>Variant, chrLast As _
> Variant, chrSuffix As Variant, chrCompany As Variant,
>varStyle As Variant)
>
> On Error GoTo Err_NFormat
>
> Dim strNewName As String
>
> Select Case varStyle
> Case "0", "FML"
> strNewName = chrFirst & " " & (chrMiddle + " ")
&
>chrLast & (", " + chrSuffix)
> Case "1", "LFM"
> strNewName = chrLast & (", " + chrSuffix) &
(", "
>& chrFirst) & (" " + chrMiddle)
> Case "2", "LFMC"
> strNewName = chrLast & (", " + chrSuffix) &
(", "
>+ chrFirst) & (" " + chrMiddle) & (" / " + chrCompany)
> Case Else
> strNewName = ""
> End Select
>
> NFormat = Trim(strNewName)
> Exit Function
>
>Err_NFormat:
> NFormat = "#Error"
>End Function
>
>
>Thank you.
>
>jbc
>.
>