Null result when combining null field with non-null field in ADP View

Null result when combining null field with non-null field in ADP View

Post by laurenquan » Mon, 17 Nov 2003 11:44:18


I'm combining the field fldFirstName and the field fldLastName in an
Access2000 ADP view as such:
tblCustomers.fldFirstName + ' ' + tblCustomers.fldLastName AS
CustomerName
Works great UNLESS either fldFirstName or fldLastName is null, in
which case CustomerName is null, even though the other part oif the
name is not null.
Is there a way to write this so it returns a CustomerName even if one
of the parts is null?
Any help is appreciated.
lq
 
 
 

Null result when combining null field with non-null field in ADP View

Post by Dirk Goldg » Mon, 17 Nov 2003 12:10:44


How about

CASE WHEN tblCustomers.fldFirstName Is Null
THEN tblCustomers.fldLastName
ELSE tblCustomers.fldFirstName +
CASE WHEN tblCustomers.fldLastName Is Null
THEN ''
ELSE ' ' + tblCustomers.fldLastName
END
END
AS CustomerName

There's probably a more concise way to do it, but this occurs to me off
the top of my head.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 
 
 

Null result when combining null field with non-null field in ADP View

Post by Wayne Morg » Mon, 17 Nov 2003 13:06:15

Null will propagate through an equation. Try using & instead of +. You may
need to Trim() also to get rid of the space that would have been between the
values.

--
Wayne Morgan
Microsoft Access MVP
 
 
 

Null result when combining null field with non-null field in ADP View

Post by laurenquan » Mon, 17 Nov 2003 16:30:11

Thank you for your quick respose. Yes, I can do this in a stored
procedure, but how do I do the same thing in a view since "CASE" is
not supported...
lq
 
 
 

Null result when combining null field with non-null field in ADP View

Post by laurenquan » Tue, 18 Nov 2003 00:06:11

You can't use "&" to combine text fields in SQL Server.
 
 
 

Null result when combining null field with non-null field in ADP View

Post by Dan Artus » Tue, 18 Nov 2003 01:52:23

Hi,
I could've swore there was an NZ equivalent but I couldn't find it.
In any case, check this out:
 
 
 

Null result when combining null field with non-null field in ADP View

Post by Dan Artus » Tue, 18 Nov 2003 01:53:17

Hmmm, my Pc just went haywire and sent the message before I put in the link

http://www.yqcomputer.com/
 
 
 

Null result when combining null field with non-null field in ADP View

Post by Dirk Goldg » Tue, 18 Nov 2003 02:48:29


Huh? Sure it is -- at least, it is in a view created directly in SQL
Server 2000. What isn't supported is the graphical display of the view,
and it warns you about that, but you can put the CASE expression in the
SQL and it will work just fine. Mind you, this is in SQL Server 2000,
so I can't say for sure about earlier versions, and I can't swear that
it works when you create the view in Access -- I don't have an ADP handy
for testing it at the moment, but it works when the view is created
directly in SQL Server. Are you sure you aren't misinterpreting the
warning message?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
 
 

Null result when combining null field with non-null field in ADP View

Post by Wayne Morg » Tue, 18 Nov 2003 11:34:48

Sorry, I missed the "ADP".

--
Wayne Morgan
Microsoft Access MVP