Solved

SQL Problem (Revisited)

Posted on 2002-06-09
7
138 Views
Last Modified: 2012-05-04
Hello,

I can't get the examples that you all have provided to work. Here is the sql I'm working with. I'm trying to select all records from from one table, to all the records of anonther table.

All the fields are (named) correctly, but it won't work. The RecordSet won't open using this sql statement, but will open using the simple "Select * from tbl_DR_Records" statement. So, I know the connection and openning (a) recordset is working. Something in the sql below is incorrect.

Both the tbl_DR_Records table and the tbl_DR_ExplainAction table has a record (8729). All fields in both tables have information.

g_iLinkReference = 8729
           
sSql = "Select Recs.DReferID, Recs.DDate, Recs.DTime, Recs.DPage, Recs.DStation, " _
        & "Recs.DProgram, Recs.DSubject, Recs.DBrief, Recs.DContact, Recs.DDepartment, " _
        & "Recs.DNotified, Recs.DStatus, Recs.DAuthor"
sSql = sSql & " Action.DEAReferID, Action.DExplain, Action.DAction, Action.DOpComment, " _
        & " Action.DSuperComment"
sSql = sSql & " From tbl_DR_Records Recs Left Outer Join tbl_DR_ExplainAction Action On Recs.DReferID = Action.DEAReferID " _
& "Where Recs.DReferID = " & CStr(g_iLinkReference)

I've used many points trying to get this to work. Please, any ideas are appreciated.

ADawn
0
Comment
Question by:ADawn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7065486
Your query is looking good. At least for Access and MS-SQL.

Are your fields numeric?

What is your database engine?
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7065978
if yu SQL is asyou wnat it, then the problem is in the Where Clause, as you indicate the useof cStr(), so your Whare clause should look like this:

"Where Recs.DReferID = '" & CStr(g_iLinkReference) & "' "

(enclose the CStr(g_iLinkReference) in single quotes)


Are you sure that Recs.DReferID is a TEXT value?

if it is not, then you do NOT need the cSTr() function:

"Where Recs.DReferID = " & g_iLinkReference)

ethr way, the SQL String wouldbe WRONG as you hav shown it.

Arthur Wood
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 7066191
Agree

mlmcc
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 18

Expert Comment

by:deighton
ID: 7066406
what is your backend database, and what error message do you receive?
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7070365
The SQL looks OK, but if it's not returning any rows, then you have to check each column that is part of the where clause and also each column in the join clauses.  Since DReferID in the Recs table is in both, start there, check it's data type.  If it's a numeric column then you don't have to wrap single quotes around it, but if it's a text or memo column you will.  

Next, check the DEAReferID column in the Action table.  Is it defined exactly the same as the DReferID column in the Recs table?  If they are different data types or lengths, then the join may not be working (however, you should at least get all the qualifying records from the Recs table).

Just to make sure your join is working, you should run the query after commenting out the Where Clause entirely.  This should give you every row in the Recs table, and in the result set you should see the Action columns populated for every Recs record where there was a match.  If all of your Action columns are null, then you know your Join is not working right.

Lastly (or maybe you want to do this earlier), manually sift through the data and compare the ID fields in both tables to ensure that you do, in fact, have Actions that map to Recs.  Look carefully at the data in the column to ensure that one doesn't have an embedded space at the beginning or end.  Sometimes you can pull your hair out becuase the keys look equal, but one has an extra space at the end which the database sees but you do not....

0
 
LVL 18

Accepted Solution

by:
mdougan earned 50 total points
ID: 7070384
Also, I noticed that you don't have a comma after the DAuthor field in the select criteria list.  This may be generating a SQL error.  Change this code:

       & "Recs.DNotified, Recs.DStatus, Recs.DAuthor"
sSql = sSql & " Action.DEAReferID, Action.DExplain, Action.DAction, Action.DOpComment, " _

To This:

       & "Recs.DNotified, Recs.DStatus, Recs.DAuthor,"
sSql = sSql & " Action.DEAReferID, Action.DExplain, Action.DAction, Action.DOpComment, " _
0
 

Author Comment

by:ADawn
ID: 7099639
Again, thanks for the help. ADawn
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

739 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