Solved

SQL Problem (Revisited)

Posted on 2002-06-09
7
135 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
7 Comments
 
LVL 69

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB error "Type mismatch" 2 50
Put text in a picture ASP.NET C# 2 50
How does CurrentUser work? 10 31
Getting warning: You are about to delete 1 row(s) 9 43
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

920 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

15 Experts available now in Live!

Get 1:1 Help Now