[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem with Report

Posted on 2006-04-01
8
Medium Priority
?
144 Views
Last Modified: 2010-04-07
Hi all,

I have a report that works fine until i had to add/change the query that generates the information.

I get a syntax error when the query executes.  However when the query statement is on 1 line it works.  My query is now too long to fit on 1 line...what can you do?

The code looks like this:

   Dim conn As ADODB.Connection
    Dim rs1 As ADODB.Recordset
   
    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "SERVER=192.168.100.101;database=umas;uid=umas;pwd=umas"
    conn.Open

    ' Open the Recordset
    Set rs1 = conn.Execute("select fixtures.gameno, fixtures.roundno, fixtures.gamedate, fixtures.leagueno, fixtures.hteamno, fixtures.ateamno, fixtures.venue, fixtures.gradecode, fixtures.starttime, fixtures.gamenotes, fixtures.gamecompleted, (select teamname from clublist where fixtures.hteamno = clublist.id) as hometeam, (select teamname from clublist where fixtures.ateamno = clublist.id) as awayteam, (Select gradename from competitiongrades where fixtures.gradecode = competitiongrades.id) as gradename, (select leaguename from competitions where fixtures.leagueno = competitions.leagueno) as leaguename, (select groundname from grounds where fixtures.venue = grounds.groundid) as groundname, (select groundtown from grounds where fixtures.venue = grounds.groundid) as groundsuburb, (select groundmelways from grounds where fixtures.venue = grounds.groundid) as groundmelways from fixtures"
        "where fixtures.gamecompleted = 0, fixtures.leagueno = " & (league.ItemData(league.ListIndex) & ", fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & " group by fixtures.gameno", , _
        adCmdText)
    ' Connect the Recordset to the DataReport.
    Set rptblankapp.DataSource = rs1
    rptblankapp.WindowState = vbMaximized
    rptblankapp.Show vbModal

    rs1.Close
    conn.Close
0
Comment
Question by:andrewl77
  • 5
  • 3
8 Comments
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16349468
Set rs1 = conn.Execute("select fixtures.gameno, fixtures.roundno, fixtures.gamedate, " & _
"  fixtures.leagueno, fixtures.hteamno, fixtures.ateamno, fixtures.venue, fixtures.gradecode, " & _
"  fixtures.starttime, fixtures.gamenotes, fixtures.gamecompleted, (select teamname from clublist where " & _
"  fixtures.hteamno = clublist.id) as hometeam, (select teamname from clublist where fixtures.ateamno = clublist.id) as " & _
" awayteam, (Select gradename from competitiongrades where fixtures.gradecode = competitiongrades.id) as gradename, " & _
" (select leaguename from competitions where fixtures.leagueno = competitions.leagueno) as leaguename, " & _
"  (select groundname from grounds where fixtures.venue = grounds.groundid) as groundname, " & _
" (select groundtown from grounds where fixtures.venue = grounds.groundid) as groundsuburb, " & _
" (select groundmelways from grounds where fixtures.venue = grounds.groundid) as groundmelways from fixtures" & _
" where fixtures.gamecompleted = 0, fixtures.leagueno = " & (league.ItemData(league.ListIndex) & _
", fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & " group by fixtures.gameno", , _
  adCmdText)

something like this?
0
 

Author Comment

by:andrewl77
ID: 16353771
I have been able to simplfy this a bit, however still get an error.  I tried the above but it got stuck on the change of the first line to the second.

I have tried this and get this error:

Run-time error '-2147217900 (80040e14)':

[MySQL][ODBC Driver 3.51][mysqld-5.0.15-nt] You have an error in your SQL Syntax; check the manual the corresponds to your mysql server version for the right syntax to use near '.gamecompleted = 0 and fixtures.leagueno = 45 And fixtures.roundno = 1 group by' at line 1

Set rs1 = conn.Execute("select fixtures.*, (select teamname from clublist where fixtures.hteamno = clublist.id) as hometeam, (select teamname from clublist where fixtures.ateamno = clublist.id) as awayteam, (Select gradename from competitiongrades where fixtures.gradecode = competitiongrades.id) as gradename, (select leaguename from competitions where fixtures.leagueno = competitions.leagueno) as leaguename, (select groundname from grounds where fixtures.venue = grounds.groundid) as groundname, (select groundtown from grounds where fixtures.venue = grounds.groundid) as groundsuburb, (select groundmelways from grounds where fixtures.venue = grounds.groundid) as groundmelways from fixtures" & _
    "where fixtures.gamecompleted = 0 and fixtures.leagueno = " & (league.ItemData(league.ListIndex)) & " And fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & " group by fixtures.gameno", , adCmdText)

This queries fits on 1 line however i have one that has more information added to it that doesn't so i want to write them the same.
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16353788
where fixtures.gamecompleted = 0, fixtures.leagueno = " & (league.ItemData(league.ListIndex) & _
", fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & " group by fixtures.gameno", , _
  adCmdText)

I didn't check your SQL syntax, but I think you should try to change the , into AND
so you will have :

where fixtures.gamecompleted = 0 AND fixtures.leagueno = " & (league.ItemData(league.ListIndex) & _
" AND fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & " group by fixtures.gameno", , _
  adCmdText)
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!

 
LVL 14

Accepted Solution

by:
PockyMaster earned 500 total points
ID: 16353791

ALSO :
Fixtures does NOT end with a space, and WHERE does NOT begin with a space
so it will be fixtureswhere fixtures gamecompleted....etc..etc..

grounds.groundid) as groundmelways from fixtures" & _
    "where fixtures.gamecompleted

So add a space on either side
0
 

Author Comment

by:andrewl77
ID: 16353793
I have already....

"where fixtures.gamecompleted = 0 and fixtures.leagueno = " & (league.ItemData(league.ListIndex)) & " And fixtures.roundno = " & (roundno.ItemData(roundno.ListIndex)) & " group by fixtures.gameno", , adCmdText)
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16353799
So type a space in front of your WHERE

my first version had a space there, you probably accidently erased it.
0
 

Author Comment

by:andrewl77
ID: 16353817
Thanks for that....
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16353818
You're welcome!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
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…
Suggested Courses

830 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