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

Is the reason for this error that I have a variable as a join?

I am getting a general ODBC error on running this with a horse name in cell A1.  Is that because I am trying to join on a variable - asking the impossible?
Dim sName As String
    Dim sSql As String
 Range("A1").Select
      
    sName = Sheets("Sheet1").Range("A1").Value
    sName = Replace(sName, "'", "''")
 
  sSql = _
   "SELECT DISTINCT runners.`horse name`, RPW.NumOfRuns   "
 
 
sSql = sSql & _
   "  FROM runners LEFT JOIN VDW ON runners.`horse name` = RPW.Expr1     "
   
sSql = sSql & _
   " WHERE runners.`horse name` = '" & sName & "'                 "
 
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Robin\My Documents\\Newflatdatabase.mdb;DefaultDir=C:\Documents and Settings\Robin\My Documents;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        )), Destination:=Range("E16"))
        .CommandText = sSql
        
        
    End With

Open in new window

0
robinbernett
Asked:
robinbernett
  • 4
  • 2
2 Solutions
 
Roger BaklundCommented:
You are joining the table VDW, bust select a column from RPW (non-existant). Change RPW to VDW.
0
 
robinbernettAuthor Commented:
Thank you, you are right.  I have changed it, but am getting the same error
0
 
Roger BaklundCommented:
Try removing "DISTINCT runners.`horse name`," from the query, so that it becomes "SELECT VDW.NumOfRuns ". You allready know the name of the horse, it is in A1...

If you still get an error, post the error message.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
robinbernettAuthor Commented:
ok, have removed "DISTINCT runners.`horse name`." from the query.

The error message is
Run-time error '1004'
General ODBC error
0
 
robinbernettAuthor Commented:
I think I need to explain:
I want to retrieve all fields from query VDW in this sql routine using the parameter runners.`horse name` in cell A1
This is my first attempt to combine in a query requests from both a table (runners) and a query (VDW) in sql.  I could well be making a fundamental error here.  I attach the sql for the query VDW in the attached code snippet.   (The table runners  is As T2)

Using the sql for the VDW query I have now got this to work:
sSql = _
   "SELECT DISTINCT T2.[horse name] AS Expr1, (SELECT COUNT(*) FROM runners T2a WHERE T2.`horse name`=T2a.`horse name`) AS NumOfRuns  "
sSql = sSql & _
   "  FROM runners AS T2    "
   
sSql = sSql & _
   " WHERE T2.`horse name` = '" & sName & "'                 "

however I do not know how to adapt it to include the other fields from the query VDW that I want returned also, ie NumOfWins and TotalFirstPrizeMoney
SELECT DISTINCT T2.[horse name] AS Expr1, (SELECT COUNT(*) FROM runners T2a WHERE T2.`horse name`=T2a.`horse name`) AS NumOfRuns, (SELECT COUNT(*) FROM  runners T2a WHERE T2.`horse name`=T2a.`horse name` AND T2a.`finish posn`="1") AS NumOfWins, (SELECT Sum(T3.`prize 1`) FROM races T3 INNER JOIN runners T2a ON T3.`race number` = T2a.`race number` WHERE T2a.`finish posn`="1" AND T2a.`horse name`=T2.`horse name` GROUP BY T2a.`horse name`) AS TotalFirstPrizeMoney
FROM runners AS T2;

Open in new window

0
 
robinbernettAuthor Commented:
I've worked it out at last - learning is a steep curve at times. I just needed to use the VDW query sql and the proper use of finish posn`='" & 1 & "'.
Points on their way.
Thank you so much for sticking with me
Cheers
Robin

0

Featured Post

Independent Software Vendors: 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!

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