Left Join acting like inner join???

[Vacation's Over]

I am having trouble with what should be a simple querry. I have a database
with info from many locations holding period information.

I am trying left outer join with dates table on left and data on right but I
can't get dates to show up unless data is present. I thought left join would
allow right to be empty?say including the periods through current year end in
table so printout will allow users to input forecasts.

"Generic" names used for readability:

Set cn = New ADODB.Connection
set rs as new ADODB.recordset

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBName &
";"

myquery = ""
myquery = myquery + " SELECT Date.PeriodID, testdata.Data "
myquery = myquery + " FROM Date LEFT JOIN testdata ON Date.PeriodID =
testdata.PeriodID"
myquery = myquery + " WHERE Date. Year >= " & StartYear & " And Date.
Year <= " & EndYear & " AND testdata.LocName = " & Chr(34) & locname &
Chr(34) & " And testdata.COAName = " & Chr(34) & COAName & Chr(34)
myquery = myquery + " ORDER BY Date.PeriodID;"

Set rst = New ADODB.Recordset
With rst
.Open myquery, cn, adOpenKeyset, adLockReadOnly
aryHold1 = .GetRows()
end with

''cleanup

aryhold1 does not show what I expect......

For 2000 startyear and 2005 endyear - I expect 60 months in "left column"
and only data from querry data in the "right column" of the array say through
may 05. What I get is only matching dates to go with data as if inner join
was used?Array ends at May 05.

(I have confirmed that Date table has next few years of months in it)

Thanks

 

[Allen Browne]

Your query has criteria on the fields from the outer side of the join.

Where this table has no matches, these fields will be Null.
Because you specfied criteria, the nulls are excluded.
The result is therefore the same as an inner join.

For more info about handling nulls, see:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html
The first of the 6 cases covers this issue.

 

[Dave Patrick]

http://allenbrowne.com/tips.html#Serious

Allen,
Nice site. I'm sure I'll be back to use some of these.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
<snip>
| For more info about handling nulls, see:
| Common Errors with Null
| at:
| http://allenbrowne.com/casu-12.html
| The first of the 6 cases covers this issue.
|
| --
| Allen Browne - Microsoft MVP. Perth, Western Australia.
|
| Reply to group, rather than allenbrowne at mvps dot org.

 

[Vacation's Over]

Allen - I should have read your entire web site a while ago instead of most
of it. Thanks

PS: One follow up question is it necessary to close a connection and
recordset if you set them to nothing? seems like overkill and trying to close
if already closed causes an error...

 

[Allen Browne]

In a perfect world, the compiler would close every open thing and
dereference every object when it goes out of scope.

Access is very good, but not perfect. There was a bug a few versions back
where Access 97 became unclosable if you did not explicitly close your
recordsets and set the variable to nothing. It was a real devil to trace:
there were actually 2 possible causes, and if you had lots of code it became
a nightmare.

Since then, most of us have been very careful to follow the really basic
programming practice of explicitly closing everything you open (but only
what you opened), and setting your objects to Nothing.

 

[Vacation's Over]

I see th eNull point but, I still can't get the results I want by adding the
"Is Null Or" . Tried it just about everywhere in the Where line of my SQL.

should I join 2 subQueries instead. select full dates left join to select
data?

 

[Allen Browne]

Drop all the criteria on the related table out of the first query. Save.
Then create another query, using the first one as an input "table".
Add the criteria there:
Is Null Or ...
Does that get you going?

 

[Vacation's Over]

thanks so much for the help

I ended up with a Query provided time periods I had data for
and another query that had all relevant periods with """" as the second column
Then Union Where the second query date was not in the first query.

Now I have a complete date table with data or ""

Sounds so simple on a Friday afternoon.