• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

SQL Problem (Revisited)

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
ADawn
Asked:
ADawn
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
Your query is looking good. At least for Access and MS-SQL.

Are your fields numeric?

What is your database engine?
0
 
Arthur_WoodCommented:
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
 
mlmccCommented:
Agree

mlmcc
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
deightonCommented:
what is your backend database, and what error message do you receive?
0
 
mdouganCommented:
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
 
mdouganCommented:
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
 
ADawnAuthor Commented:
Again, thanks for the help. ADawn
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now