Results 1 to 2 of 2
Thread: Concatenate 3 fields (2007)
2009-08-13, 20:06 #1
- Join Date
- Jan 2001
- Virginia, USA
- Thanked 1 Time in 1 Post
I've built a query to display the names of periodical publications. Some of the periodicals have Title & Month & Year. A few have just Title & Year. I built the following field in the query:
Issue: [tblMagNames].[MagTitle] & (" "+[tblMonths].[PubMonth]) & " " & [IssueStartYear]
The SQL view reads as follows:
SELECT tblIssues.IssueID, [tblMagNames].[MagTitle] & (" "+[tblMonths].[PubMonth]) & " " & [IssueStartYear] AS Issue
FROM tblMonths INNER JOIN (tblIssues INNER JOIN tblMagNames ON tblIssues.IssueTitle = tblMagNames.MagID) ON tblMonths.PubMonthNbr = tblIssues.IssueStartMonth;
When I run the query, it displays every publication that has all three elements (title & month & year). The ones that do not have a month, however, do not appear. Where did I go wrong with my query?
2009-08-13, 20:24 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
You have an inner join of tblMonths and tblIssues. By definition, an inner join only returns records for which the join field is the same and not null on both sides. So you won't get records whose month field is null (blank, empty).
Try changing INNER JOIN to RIGHT JOIN for the join between tblMonths and tblIssues. You may have to change the other join too.
In design view, you change the join type by double-clicking the join line and selecting a different option.