Solved

Report-SQLQuery Syntax

Posted on 2008-10-20
13
207 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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

729 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