Solved

Report-SQLQuery Syntax

Posted on 2008-10-20
13
206 Views
Last Modified: 2012-05-05
I Get Syntax error on line 1 and 3
Any suggestions would greatly be appreciated.

Private Sub Report_Open(Cancel As Integer)
Dim SQL As String

SQL = "SELECT a.MaintenanceDAte, a.COId, Count(a.chkR) AS CountOfchkR, DCount("chkP","Table2","chkP  and coid = " & a.coid) AS chkP, DCount("chky","Table3","chkY  and coid = " & a.coid) AS chkY, DCount("chkb","Table4","chkB  and coid = " & a.coid) AS chkB"
SQL = "FROM Table1 AS a"
SQL = "GROUP BY a.MaintenanceDAte, a.COId, DCount("chkP","Table2","chkP  and coid = " & a.coid), DCount("chky","Table3","chkY  and coid = " & a.coid), DCount("chkb","Table4","chkB  and coid = " & a.coid)"
SQL = "HAVING (((Count(a.chkR))=True));"
End Sub
0
Comment
Question by:ca1358
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 13

Expert Comment

by:AielloJ
ID: 22763300
Your double quotes in the DCount functions are terminating the double quotes you started the SQL statement with.  Try changing the double quotes in your DCount functions to single quotes.
0
 
LVL 82

Expert Comment

by:hielo
ID: 22763362

Private Sub Report_Open(Cancel As Integer)
Dim SQL As String
 
SQL = " SELECT a.MaintenanceDAte, a.COId, Count(a.chkR) AS CountOfchkR, DCount("chkP","Table2","chkP  and coid = " & a.coid) AS chkP, DCount("chky","Table3","chkY  and coid = " & a.coid & ") AS chkY, DCount("chkb","Table4","chkB  and coid = " & a.coid & ") AS chkB"
SQL = " FROM Table1 AS a"
SQL = " GROUP BY a.MaintenanceDAte, a.COId, DCount("chkP","Table2","chkP  and coid = " & a.coid & "), DCount("chky","Table3","chkY  and coid = " & a.coid & "), DCount("chkb","Table4","chkB  and coid = " & a.coid & ")"
SQL = " HAVING (((Count(a.chkR))=True));"
End Sub

Open in new window

0
 

Author Comment

by:ca1358
ID: 22763556
Tried the single quotes get a name field error   #Name?
Copied the second one still 1 and 3rd syntax error

Any other suggestions?
0
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!

 
LVL 13

Expert Comment

by:AielloJ
ID: 22763607
OK guys, we were all having a senior moment (regardless of age)!!  The primary problem is the SQL string variable is being written over in the 2nd, 3rd, and 4th lines of code instead of being appended to.  Borrowing hielo's code because it's formatted nicely - and assuming the ampersand is the append operator in VB (It's been awhile since I did VB) -  it should read:

SQL = " SELECT a.MaintenanceDAte, a.COId, Count(a.chkR) AS CountOfchkR, DCount('chkP','Table2','chkP  and coid = ' & a.coid) AS chkP, DCount("chky","Table3","chkY  and coid = " & a.coid & ") AS chkY, DCount("chkb","Table4","chkB  and coid = " & a.coid & ") AS chkB"
SQL = SQL & " FROM Table1 AS a"
SQL = SQL & " GROUP BY a.MaintenanceDAte, a.COId, DCount("chkP","Table2","chkP  and coid = " & a.coid & "), DCount("chky","Table3","chkY  and coid = " & a.coid & "), DCount("chkb","Table4","chkB  and coid = " & a.coid & ")"
SQL = SQL & " HAVING (((Count(a.chkR))=True));"

After making this correction try the single quotes again similar to what I started on the first line.  You'll notice, you're only getting the errors on lines that have quotes in the, - lines 1 and 3.  HINT: Put a print statement on the SQL variable for debugging purposes.  Seeing the actual SQL string going to attempt to execute points out all the major errors quickly.
0
 

Author Comment

by:ca1358
ID: 22763656
I am getting this error on the report    #Name?
But nothing lights up red anymore.


Private Sub Report_Open(Cancel As Integer)
Dim SQL As String
SQL = " SELECT a.MaintenanceDAte, a.COId, Count(a.chkR) AS CountOfchkR, DCount('chkP','Table2','chkP  and coid = ' & a.coid) AS chkP, DCount('chky','Table3','chkY  and coid = ' & a.COId & ') AS chkY, DCount('chkb','Table4','chkB  and coid = ' & a.COId & ') AS chkB"
SQL = SQL & " FROM Table1 AS a"
SQL = SQL & " GROUP BY a.MaintenanceDAte, a.COId, DCount('chkP','Table2','chkP  and coid = ' & a.COId & '), DCount('chky','Table3','chkY  and coid = ' & a.COId & '), DCount('chkb','Table4',chkB'  and coid = ' & a.COId & ')"
SQL = SQL & " HAVING (((Count(a.chkR))=True));"

End Sub
0
 
LVL 13

Expert Comment

by:AielloJ
ID: 22763672
ca1358:

Do that print statement on the SQL variable I suggested, then cut and paste the SQL statement here.  It's so much easier to work than looking at mixtures of string's, code, and variables.   It's been my experience that printing the SQL string makes the errors very obvious in most cases.

0
 
LVL 82

Expert Comment

by:hielo
ID: 22763701
the first coid=... still has the original problem. Try:
Private Sub Report_Open(Cancel As Integer)
Dim SQL As String
 
SQL = " SELECT a.MaintenanceDAte, a.COId, Count(a.chkR) AS CountOfchkR, DCount("chkP","Table2","chkP  and coid = " & a.coid & ") AS chkP, DCount("chky","Table3","chkY  and coid = " & a.coid & ") AS chkY, DCount("chkb","Table4","chkB  and coid = " & a.coid & ") AS chkB"
SQL = SQL & " FROM Table1 AS a"
SQL = SQL & " GROUP BY a.MaintenanceDAte, a.COId, DCount("chkP","Table2","chkP  and coid = " & a.coid & "), DCount("chky","Table3","chkY  and coid = " & a.coid & "), DCount("chkb","Table4","chkB  and coid = " & a.coid & ")"
SQL = SQL & " HAVING (((Count(a.chkR))=True));"
End Sub

Open in new window

0
 

Author Comment

by:ca1358
ID: 22763757
Having no luck.  
Also tried hielo code.

What do you put in the immediate window to get it to print?
0
 
LVL 13

Expert Comment

by:AielloJ
ID: 22765512
If you put a breakpoint after the SQL string variable is assigned you can issue the print (?) command in the Immediate Window:

  ? SQL

or you can put a Watch on the SQL variable in the Watch Window.
0
 

Author Comment

by:ca1358
ID: 22767771
Attaching the Test DB.
I am at a lost.
TestCount.mdb
0
 

Author Comment

by:ca1358
ID: 22767807
I did try the Print Statement.  It only print the SQL statement in one long line and I did not see anything to indicate an error.
0
 
LVL 13

Accepted Solution

by:
AielloJ earned 500 total points
ID: 22768260
Try the attached.  It's your file with corrections.  The syntax errors are gone.  I would look at the HAVING clause.  It reads: HAVING (((Count(a.chkR))=True));"  This should probably be a numeric value instead of True.
TestCount-JRA.mdb
0
 

Author Closing Comment

by:ca1358
ID: 31508082
Thank you!
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

Suggested Solutions

Title # Comments Views Activity
Updating a field based from a if exist.... 2 33
sql update 2 37
online  environment for testing sql queries 5 31
Help with Progress 4gl Rounding Function 6 26
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…

733 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