NULL Propogation

[Mike Labosh]

Yeah, I looked at the help file first, but Office help seems brain-dead to
me.

In SQL Server 2000 I can do this:

CREATE TABLE dbo.People (
FirstName NVARCHAR(50),
Middlename NVARCHAR(50),
LastName NVARCHAR(50)
)

INSERT INTO dbo.People
(FirstName, MiddleName, LastName)
VALUES
('Mike', NULL, 'Labosh')

SELECT FirstName + ' ' + MiddleName + ' ' + LastName
FROM People

The composite result of the select expression is null. How does Access do
it? Where can I find the fules about how NULL propogates through
expressions?

--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei

 

[Allen Browne]

There are 2 concatenation operators in Access, and they behave slightly
differently:
"A" & Null => "A"
"A" + Null => Null

Other info on Nulls:
http://allenbrowne.com/casu-12.html

 

[Mike Labosh]

There are 2 concatenation operators in Access, and they behave slightly

differently:
"A" & Null => "A"
"A" + Null => Null

Access is EVIL. OK, I guess that's good enough for now. And thanks for the
link
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei

 

[Arvin Meyer [MVP]]

It's evil to be able to avoid a null? Why insist on forcing non-existing
data? If you really don't want to return a value, then you use the same
syntax:

SELECT FirstName + ' ' + MiddleName + ' ' + LastName
FROM People

Personally, I prefer:

SELECT FirstName & (" " + MiddleName) & " " & LastName
FROM People

which will give the following result:

Mike A. Labosh
or
Mike Labosh

if there's no middle name or initial. It is little things like this, that
make the Access query engine superior to all others.

While a truck can carry more, a bicycle is far more efficient.

http://www.terragalleria.com/images/vietnam/viet8027.jpeg

Eventually, SQL-Server will learn Access ways.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Mike Labosh]

It's evil to be able to avoid a null? Why insist on forcing non-existing

data? If you really don't want to return a value, then you use the same
syntax:

SELECT FirstName + ' ' + MiddleName + ' ' + LastName
FROM People

Personally, I prefer:

SELECT FirstName & (" " + MiddleName) & " " & LastName
FROM People

Oh, I agree. I'm simply just trying to map the behavior that I know (SQL
Server) to behavior that I don't know (Access)

For example, your code above would yield a NULL in SQL Server. I happen to
already b familiar with the trick to & "" because that's the way VB deals
with nulls. I was simply asking if there is a list of rules that governs
the way Access deals with null values.
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei

 

[Douglas J. Steele]

Oh, I agree. I'm simply just trying to map the behavior that I know (SQL
Server) to behavior that I don't know (Access)

For example, your code above would yield a NULL in SQL Server. I happen
to already b familiar with the trick to & "" because that's the way VB
deals with nulls. I was simply asking if there is a list of rules that
governs the way Access deals with null values.

As Arvin said, if any of FirstName, MiddleName or LastName are Null, then

SELECT FirstName + ' ' + MiddleName + ' ' + LastName

will yield Null in Access, the same as in SQL Server.

In other words, as far as I can tell, Access is consistent with your
experiences in SQL Server and VB.

 

[Jim whitaker]

Use a if not isnull (whatever).
Look in help at NZ function also.
I've used access, sqlserver and mysql, and never had a problem. I do
however go to the help file from time to time.

 

[Jim whitaker]

Oh, one more thing. You are better off using "allow zero length string"
when it comes to using update and inserts in code. However running a built
in query seems to always work. I've spent hours and hours with this, and
notice if I attempt to insert a null it won't insert. That's if I use a
variable. Sorry I didn't read first post.

 

[Mike Labosh]

In other words, as far as I can tell, Access is consistent with your

experiences in SQL Server and VB.

That's even *better* than excellent.

THANKS!
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei