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

Report-SQLQuery Syntax

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
ca1358
Asked:
ca1358
  • 6
  • 5
  • 2
1 Solution
 
AielloJCommented:
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
 
hieloCommented:

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

Any other suggestions?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
AielloJCommented:
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
 
ca1358Author Commented:
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
 
AielloJCommented:
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
 
hieloCommented:
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
 
ca1358Author Commented:
Having no luck.  
Also tried hielo code.

What do you put in the immediate window to get it to print?
0
 
AielloJCommented:
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
 
ca1358Author Commented:
Attaching the Test DB.
I am at a lost.
TestCount.mdb
0
 
ca1358Author Commented:
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
 
AielloJCommented:
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
 
ca1358Author Commented:
Thank you!
0

Featured Post

Technology Partners: 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!

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