String Concatenation

[Rebecca Smith]

In past versions of Access it was a simple matter to create a persons full
name from the three strings 'FirstName' 'MiddleInit' and 'LastName' and
include a period after the middle initial. Using a plus sign before the
middle initial kept the period from showing up if the middle initial was
blank. Seems Access 2003 doesn't recognize this simple trick.

So the question is, how do create a full name in a query without having a
period show up between FirstName and LastName if there is no middle name?

Thank you

[James Arnold]

Rebecca Smith wrote:

In past versions of Access it was a simple matter to create a persons full
name from the three strings 'FirstName' 'MiddleInit' and 'LastName' and
include a period after the middle initial. Using a plus sign before the
middle initial kept the period from showing up if the middle initial was
blank. Seems Access 2003 doesn't recognize this simple trick.

So the question is, how do create a full name in a query without having a
period show up between FirstName and LastName if there is no middle name?

Thank you

If [MiddleInit] <> Null Then
sFullName = [FirstName] & " " & [MiddleInit] & " " & [LastName]
Else
sFullName = [FirstName] & " " & [LastName]
End If

--
James

[Keith]

"Rebecca Smith" <rp*****@pcez.com> wrote in message
news:11************@corp.supernews.com...

In past versions of Access it was a simple matter to create a persons full
name from the three strings 'FirstName' 'MiddleInit' and 'LastName' and
include a period after the middle initial. Using a plus sign before the
middle initial kept the period from showing up if the middle initial was
blank. Seems Access 2003 doesn't recognize this simple trick.

So the question is, how do create a full name in a query without having a
period show up between FirstName and LastName if there is no middle name?

Use an IsNull test in an in-line "If" statement.

Regards,
Keith.
www.keithwilby.com

[Rick Brandt]

Rebecca Smith wrote:

In past versions of Access it was a simple matter to create a persons
full name from the three strings 'FirstName' 'MiddleInit' and
'LastName' and include a period after the middle initial. Using a
plus sign before the middle initial kept the period from showing up
if the middle initial was blank. Seems Access 2003 doesn't recognize
this simple trick.

So the question is, how do create a full name in a query without
having a period show up between FirstName and LastName if there is no
middle name?

Something else is going on because there is no change to Access 2003
affecting how the + and & operators work with respect to Null propogation.
I just performed the following test in the immediate window of Access
2003...

?"test" & ("." & Null) & "TEST"
test.TEST

?"test" & ("." + Null) & "TEST"
testTEST

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Trevor Best]

James Arnold wrote:

Rebecca Smith wrote:

In past versions of Access it was a simple matter to create a persons
full name from the three strings 'FirstName' 'MiddleInit' and
'LastName' and include a period after the middle initial. Using a plus
sign before the middle initial kept the period from showing up if the
middle initial was blank. Seems Access 2003 doesn't recognize this
simple trick.

So the question is, how do create a full name in a query without
having a period show up between FirstName and LastName if there is no
middle name?

Thank you

If [MiddleInit] <> Null Then
sFullName = [FirstName] & " " & [MiddleInit] & " " & [LastName]
Else
sFullName = [FirstName] & " " & [LastName]
End If

Of course, you know why you will never get the middle initial displayed
there. ;-)

[Trevor Best]

Rick Brandt wrote:

Something else is going on because there is no change to Access 2003
affecting how the + and & operators work with respect to Null propogation.
I just performed the following test in the immediate window of Access
2003...

?"test" & ("." & Null) & "TEST"
test.TEST

?"test" & ("." + Null) & "TEST"
testTEST

Perhaps there are zero length strings in the middle initial?

Opposite of Nz is...
Function Zn(pvar As Variant, Optional pvarZLS As Variant = "")
If pvarZLS = pvar Then
Zn = Null
Else
Zn = pvar
End If
End Function

[David W. Fenton]

Trevor Best <no****@nospam.invalid> wrote in
news:42***********************@news.zen.co.uk:

Rick Brandt wrote:

Something else is going on because there is no change to Access
2003 affecting how the + and & operators work with respect to
Null propogation. I just performed the following test in the
immediate window of Access 2003...

?"test" & ("." & Null) & "TEST"
test.TEST

?"test" & ("." + Null) & "TEST"
testTEST

Perhaps there are zero length strings in the middle initial?

Opposite of Nz is...
Function Zn(pvar As Variant, Optional pvarZLS As Variant = "")
If pvarZLS = pvar Then
Zn = Null
Else
Zn = pvar
End If
End Function

Zero-length strings are an abomination.

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

[Trevor Best]

David W. Fenton wrote:

Zero-length strings are an abomination.

Actually they're not, in true relational style, if the middle initial is
null, it means the data is missing, could be that the middle name is
unknown. If it's a zero length string it means the person doesn't have a
middle name.

The only problem is that nulls are indistinguishable from zero length
strings to the human eye.

If it were a SSN say, and the person is not a US national then they
probbly don't have one, it's acceptible to have "N/A" entered into the
SSN field but you don't want to see "Joe N/A Blow" on a report.

(yes I just noticed my spelling mistake above but it looks so funny I
don't want to correct it)

[David W. Fenton]

Trevor Best <no****@nospam.invalid> wrote in
news:42**********************@news.zen.co.uk:

David W. Fenton wrote:

Zero-length strings are an abomination.
Actually they're not, in true relational style, if the middle
initial is null, it means the data is missing, could be that the
middle name is unknown. If it's a zero length string it means the
person doesn't have a middle name.

I've never had an application where that distinction was of any use
whatsoever.
The only problem is that nulls are indistinguishable from zero
length strings to the human eye.
Well, I encounter the problem most often with people who are too
inexperienced to write code that enters data into tables without
needing to write ZLS's into them, so they allow ZLS's in their field
definitions. This leads to massive problems.

These are mostly web developers, who, in my experience, have a
particular variety of stupidity when it comes to database design.
If it were a SSN say, and the person is not a US national then
they probbly don't have one, it's acceptible to have "N/A" entered
into the SSN field but you don't want to see "Joe N/A Blow" on a
report.
I can't think of a type of data where a ZLS used to distinguish
UNKNOWN from NONE would be at all useful. Like you say, you'd
probably want some default value other than ZLS. It certainly would
make coding a lot simpler to do that.
(yes I just noticed my spelling mistake above but it looks so
funny I don't want to correct it)

My eyes are acting up today and I can't see your misspelling.

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

[Trevor Best]

David W. Fenton wrote:

My eyes are acting up today and I can't see your misspelling.

probbly :-)