Solved

Report-SQLQuery Syntax

Posted on 2008-10-20
13
204 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
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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…

895 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

15 Experts available now in Live!

Get 1:1 Help Now