ACCESS FEED: RE: [MS_AccessPros] Re: Field not meeting the join criteria

Rabu, 25 Mei 2011

RE: [MS_AccessPros] Re: Field not meeting the join criteria

Paul-

Good answer! And to avoid the extra space on the end, you could take advantage
of Null propogation by doing:

SELECT tblContact.ContactID, [FirstName] & " " & [LastName] & (" " + [Suffix])
AS
ContactName
FROM tblContact LEFT JOIN tlkpSuffixes ON tblContact.SuffixID =
tlkpSuffixes.SuffixID;

When you use + as the concatenation operator, it returns a Null if any of the
arguments are null.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Paul
Sent: Wednesday, May 25, 2011 11:31 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Field not meeting the join criteria

Does this do what you want?

SELECT tblContact.ContactID, [FirstName] & " " & [LastName] & " " & [Suffix] AS
ContactName
FROM tblContact LEFT JOIN tlkpSuffixes ON tblContact.SuffixID =
tlkpSuffixes.SuffixID;

Paul

--- In MS_Access_Professionals@yahoogroups.com, "Art" <dbalorenzini@...> wrote:
>
> I have this query:
>
> SELECT tblContact.ContactID, [FirstName] & " " & [LastName] & " " & [Suffix]
AS ContactName
> FROM tblContact INNER JOIN tlkpSuffixes ON tblContact.SuffixID =
tlkpSuffixes.SuffixID;
>
> It is returning nothing because there is no records with a Suffix. How can I
work around that and display records that don't have a suffix?
>
> Thanks
>
> Arthur Lorenzini
> Sioux Falls, SD
>

------------------------------------

Yahoo! Groups Links