UnWanted Commer

[Bob V]

On my first line blank of my drop down Combo Box list I getting just a
Commer, Is it possible not to show Commer if therir is NO OwnerLastName

SELECT tblOwnerInfo.OwnerID,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName) & ","
& ' ' &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
') AS OwnerName FROM tblOwnerInfo ORDER BY
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName) & ","
& ' ' &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
');

 

[Beetle]

Try moving the comma inside the parentheses like this;

SELECT tblOwnerInfo.OwnerID,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & "," )
& ' ' &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
') AS OwnerName FROM tblOwnerInfo ORDER BY
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & "," )
& ' ' &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
');

 

[Bob V]

Thanks Beetle tried this but the same result ............Bob

SELECT tblOwnerInfo.OwnerID, IIf(IsNull(tblOwnerInfo.OwnerLastName &
","),'',tblOwnerInfo.OwnerLastName & ",") & ' ' &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
') AS OwnerName FROM tblOwnerInfo ORDER BY
IIf(IsNull(tblOwnerInfo.OwnerLastName & ","),'',tblOwnerInfo.OwnerLastName &
",") & ' ' &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
');

 

[Bob V]

Thanks Beetle tried this but the same result ............Bob

SELECT tblOwnerInfo.OwnerID, IIf(IsNull(tblOwnerInfo.OwnerLastName &
","),'',tblOwnerInfo.OwnerLastName & ",") & ' ' &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
') AS OwnerName FROM tblOwnerInfo ORDER BY
IIf(IsNull(tblOwnerInfo.OwnerLastName & ","),'',tblOwnerInfo.OwnerLastName &
",") & ' ' &
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
');

 

[Ken Sheridan]

You can make use of the fact that Nulls propagate when the + addition
operator is used rather than the & concatenation operator, i.e. Null + "," =
Null, whereas Null & ", " = ", ". Parenthesise the addition of the comma and
space to the last name so that the parenthesised expression will evaluate
independently of the whole expression:

SELECT OwnerID, (OwnerLastName + ", ") &
OwnerFirstName AS OwnerName
FROM tblOwnerInfo
ORDER BY OwnerLastName, OwnerFirstName;

Ken Sheridan
Stafford, England

 

[Beetle]

You concantenated an additional comma to the end of OwnerLastName in the
first part of the argument (immediately following the IsNull...) which wasn't
there in your first post or my first response. This would cause the
expression to never be null, therefore it will implement the second argument
and attemt to display the OwnerLastName plus a comma. If there is no
OwnerLastName it will just display a comma.

However, having said that, I thing Ken Sheridans solutionis better.

 

[Bob V]

Thanks Guys used Kens worked Brilliant...........Bob