Solved

Report-SQLQuery Syntax

Posted on 2008-10-20
13
203 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
  • 6
  • 5
  • 2
13 Comments
 
LVL 13

Expert Comment

by:AielloJ
Comment Utility
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
Comment Utility

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
Comment Utility
Tried the single quotes get a name field error   #Name?
Copied the second one still 1 and 3rd syntax error

Any other suggestions?
0
 
LVL 13

Expert Comment

by:AielloJ
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 82

Expert Comment

by:hielo
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Attaching the Test DB.
I am at a lost.
TestCount.mdb
0
 

Author Comment

by:ca1358
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now