Solved

access 2000 sql question for select from clause using outer join

Posted on 2013-12-30
2
410 Views
Last Modified: 2013-12-30
I am having a problem with an access 2000 sql query using two tables and an outer join.
This sql statement is from within a VB6 program.

I am getting a syntax error in the FROM clause.

See sql statements below.

When I change the OUTER LEFT JOIN clause to INNER JOIN, I do not get the syntax error, but I chose the OUTER JOIN because I want the records from the TblInsured whether there are corresponding  TblPolicy records or not. It is my understanding that the INNER JOIN will only return records if there is a match with the TblInsuredID in both tables. There are Insureds with no policy records, i.e. no policy records where TblInsuredID matches TblInsuredID in an Insured record.



=======================================================================

sSelect = "SELECT TblInsured.*, TblPolicy.* "

sfrom = "FROM TblInsured OUTER LEFT JOIN TblPolicy on TblInsured.TblInsuredID = TblPolicy.TblInsuredID "

sWhere = "WHERE (((Account_Type)=" & bu & " Or (Account_Type)=" & pe & " Or " _
& "(Account_Type)=" & PR & ") AND ((Account_Status)=" & Chr(39) & incl & Chr(39) & " Or " _
& "(Account_Status)=" & Chr(39) & excl & Chr(39) & ") AND ((Insured_Name2)>=" & Chr(39) & n1 & Chr(39) & " And " _
& "(Insured_Name2)<=" & Chr(39) & n2 & Chr(39) & ")) "
   
sOrder = "ORDER BY Insured_Name2, Insured_Name; "

sSQL$ = sSelect & sfrom & sWhere & sOrder

rs.Open sSQL, Conn, adOpenStatic, adLockPessimistic, adCmdText
---------------------------------------------------------------------
The ERROR MESSAGE is when rs.Open statement above is executed.
----------------------------------------------------------------------------------------------------
Syntax error in FROM clause. in ProcessRecordsCustomersLocation Procedure
----------------------------------------------------------------------------------------------------

when I change the sFrom statement wording from OUTER LEFT JOIN to INNER JOIN the action works, i.e. no Systax error. If I just remove the LEFT qualifier from the OUTER JOIN I still get the syntax error.

The data base is Access2000.  Procedure is in a VB6 program

Thanks for any assistance with this problem.

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

Table: TblInsured                                                                                    Page: 1

Columns

         Name                                                     Type                                         Size

         TblInsuredID                                          Long Integer                             4
         TblAgencyID                                           Long Integer                             4
         TblLocationID                                         Long Integer                             4
         TblUserID                                               Text                                        20
         Account_Number                                    Text                                       20
--------------------------------------------------------------------------------------------------------------

Table: TblPolicy                                                                                     Page: 1

Columns

         Name                                                  Type                                         Size

         TblPolicyID                                          Long Integer                             4
         TblAgencyID                                        Long Integer                             4
         TblInsuredID                                       Long Integer                             4
         TblCompanyID                                     Long Integer                            4
         TblPolicyTypeID                                   Long Integer                            4
0
Comment
Question by:morrisbo
2 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39747607
try using  LEFT JOIN only

sfrom = "FROM TblInsured LEFT JOIN TblPolicy on TblInsured.TblInsuredID = TblPolicy.TblInsuredID "
0
 

Author Closing Comment

by:morrisbo
ID: 39747637
capricorn1

That worked - thanks for the quick solution

morrisbo
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now