SQL leading strange results 1

[THWatson]

Using Access 2010 for a church database
The following SQL pulls names from tblTrinity

SELECT tblTrinity.UniqueID, tblTrinity.LastName, tblTrinity.FirstName, tblTrinity.AptNbr, IIf(IsNull([tblTrinity].[AptNbr]),"",[tblTrinity].[AptNbr] & " - ") & [tblTrinity].[HouseNbr] & " "+[tblTrinity].[Street] & IIf(IsNull([Address2]+[tblTrinity].[Street]),"",Chr(13)+Chr(10)) & [Address2] AS AddressCheck
FROM tblTrinity
WHERE (((tblTrinity.LastName)="Hartung")) OR (((tblTrinity.LastName)="Jenkinson")) OR (((tblTrinity.LastName)="Cameron"));

In the 3 instances I will show below, the husband has died and, the widow moved to a new record. For whatever reason, the widow's address has a " - " appended prior to the address, yielding these results. The " - " does not show in the table.
UniqueID LastName FirstName AptNbr AddressCheck
85 Cameron Cliff 15 Cathcart ST
264 Hartung Kenneth 6 Parkside DR
308 Jenkinson Michael 47 Rickson AVE
942 Cameron Hazel - 15 Cathcart ST
927 Jenkinson Joan - 5 Somerset Glen
975 Hartung Inez - 6 Parkside DR

The problem would seem to occur in the piece of code that creates the AddressCheck column

AddressCheck: IIf(IsNull([tblTrinity].[AptNbr]),"",[tblTrinity].[AptNbr] & " - ") & [tblTrinity].[HouseNbr] & " "+[tblTrinity].[Street] & IIf(IsNull([Address2]+[tblTrinity].[Street]),"",Chr(13)+Chr(10)) & [Address2]

Does anybody spot what is causing this to occur? And only when new records have been created by moving the widow to a new record?

Thanks.

Tom

 

[PHV]

Seems like AptNbr isn't null in the new record.
I'd replace this :
IIf(IsNull([tblTrinity].[AptNbr]),"",[tblTrinity].[AptNbr] & " - ")
with this :
IIf(Trim([tblTrinity].[AptNbr] & "")="","",[tblTrinity].[AptNbr] & " - ")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
Well, what you suggested works.
Although I'm not at all sure why the AptNbr isn't null in the new record. Because nothing shows in the table after the new record is created.

And here's an SQL:

SELECT tblTrinity.UniqueID, tblTrinity.LastName, tblTrinity.FirstName, tblTrinity.AptNbr, tblTrinity.HouseNbr, tblTrinity.Street
FROM tblTrinity
WHERE (((tblTrinity.LastName)="Cameron")) OR (((tblTrinity.LastName)="Jenkinson")) OR (((tblTrinity.LastName)="Hartung"));

that results in the following:
UniqueID LastName FirstName AptNbr HouseNbr Street
85 Cameron Cliff 15 Cathcart ST
264 Hartung Kenneth 6 Parkside DR
308 Jenkinson Michael 47 Rickson AVE
942 Cameron Hazel 15 Cathcart ST
975 Hartung Inez 6 Parkside DR
927 Jenkinson Joan 5 Somerset Glen

So why the " - " is thrown in there when my original formation is applied is unclear to me.

Thanks, PHV

Tom

 

[PHV]

Don't confuse Null value and ZeroLengthString.

 

[THWatson]

Yep, you are so right. I just ran the following SQL

SELECT tblTrinity.UniqueID, tblTrinity.LastName, tblTrinity.FirstName, tblTrinity.AptNbr, tblTrinity.HouseNbr, tblTrinity.Street, IIf(IsNull([AptNbr]),"","-") AS Expr1
FROM tblTrinity
WHERE (((tblTrinity.LastName)="Cameron")) OR (((tblTrinity.LastName)="Jenkinson")) OR (((tblTrinity.LastName)="Hartung"));

And voila...I got "-" in front of the 3 widows' names.

Thanks, PHV.

Tom