Function ??? (Access 2000)

  • Thread starter Thread starter jbc
  • Start date Start date
J

jbc

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)
Click to expand...

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 said:
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
Click to expand...
 
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
 
Back
Top