Solved

SQL Problem (Revisited)

Posted on 2002-06-09
7
137 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 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

828 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