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

  • Thread starter Thread starter Lauren Quantrell
  • Start date Start date
L

Lauren Quantrell

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
 
Lauren Quantrell said:
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
Click to expand...

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.
 
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.
 
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
 
Hi,
I could've swore there was an NZ equivalent but I couldn't find it.
In any case, check this out:
 
Lauren Quantrell said:
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
Click to expand...

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?
 
Back
Top