SQL Syntax 2003 Access VBA

I have a Report that I attached a Access query but I need to turn the query into code(SQL query).  I am attaching a test file.  Cant not get the syntax right.

The report name is SQL_ReportByCompany

Any help would greatly be appreciated.


**************************************************
Private Sub Report_Open(Cancel As Integer)
Dim SQL
'SQL = "SELECT Yields.MaintenanceYieldDate, Yields.CompanyNumber, DCount('chkYield','Yields','chkYield  and CompanyNumber = ' & a.CompanyNumber) AS chkYield, DCount('Yield_Blend','Yields','Yield_Blend  and CompanyNumber = ' & a.CompanyNumber) AS Yield_Blend, DCount('BlendOnly','Yields','BlendOnly  and CompanyNumber = ' & a.CompanyNumber) AS BlendOnly, DCount('MoveMoney','Yields','MoveMoney  and CompanyNumber = ' & a.CompanyNumber) AS MoveMoney, DCount('chkExisting','Yields','chkExisting  and CompanyNumber = ' & a.CompanyNumber) AS chkExisting, DCount('chkNewCommit','Yields','chkNewCommit  and CompanyNumber = ' & a.CompanyNumber) AS chkNewCommit, DCount('chkBlendForOverDelivery','BlendsForOverDelivery','chkBlendForOverDelivery  and CompanyNumber = ' & a.CompanyNumber) AS chkBlendForDelivery, DCount('chkRoll','RollsCombined','chkRoll  and CompanyNumber = ' & a.CompanyNumber) AS chkRoll, DCount('chkPairOut','PairOutCombined','chkPairout  and CompanyNumber = ' & a.CompanyNumber) AS chkPairOut"
 
SQL = "SELECT Yields.MaintenanceYieldDate, Yields.CompanyNumber, DCount("chkYield','Yields','chkYield  and CompanyNumber = " & a.CompanyNumber) AS chkYield, DCount('Yield_Blend','Yields','Yield_Blend  and CompanyNumber = " & a.CompanyNumber) AS Yield_Blend, DCount('BlendOnly','Yields','BlendOnly  and CompanyNumber = " & a.CompanyNumber) AS BlendOnly, DCount('MoveMoney','Yields','MoveMoney  and CompanyNumber = " & a.CompanyNumber) AS MoveMoney, DCount('chkExisting','Yields','chkExisting  and CompanyNumber = " & a.CompanyNumber) AS chkExisting, DCount('chkNewCommit','Yields','chkNewCommit  and CompanyNumber = " & a.CompanyNumber) AS chkNewCommit, DCount('chkBlendForOverDelivery','BlendsForOverDelivery','chkBlendForOverDelivery  and CompanyNumber = " & a.CompanyNumber) AS chkBlendForDelivery, DCount('chkRoll','RollsCombined','chkRoll  and CompanyNumber = " & a.CompanyNumber) AS chkRoll, DCount('chkPairOut','PairOutCombined','chkPairout  and CompanyNumber = " & a.CompanyNumber) AS chkPairOut"
SQL = SQL & "FROM Yields"
SQL = SQL & "WHERE (((Yields.MaintenanceYieldDate) = [Enter Date]) And ((Yields.CompanyNumber) = [Enter CompanyNumber]))"
SQL = SQL & "GROUP BY Yields.MaintenanceYieldDate, Yields.CompanyNumber, DCount('chkYield','Yields','chkYield  and CompanyNumber =  a.CompanyNumber), DCount('Yield_Blend','Yields','Yield_Blend  and CompanyNumber =  a.CompanyNumber), DCount('BlendOnly','Yields','BlendOnly  and CompanyNumber =  a.CompanyNumber), DCount('MoveMoney','Yields','MoveMoney  and CompanyNumber = a.CompanyNumber), DCount('chkExisting','Yields','chkExisting  and CompanyNumber =  a.CompanyNumber), DCount('chkNewCommit','Yields','chkNewCommit  and CompanyNumber =  a.CompanyNumber), DCount('chkBlendForOverDelivery','BlendsForOverDelivery','chkBlendForOverDelivery  and CompanyNumber =  a.CompanyNumber), DCount('chkRoll','RollsCombined','chkRoll  and CompanyNumber =  a.CompanyNumber), DCount('chkPairOut','PairOutCombined','chkPairout  and CompanyNumber =  a.CompanyNumber);"
CurrentDb.Execute SQL, dbFailOnError
End Sub

Open in new window

OpsMaintenance.mdb
ca1358Asked:
Who is Participating?
 
dwe761Connect With a Mentor Software EngineerCommented:
ca1358,
Try this.  It is better to prompt once before needing the value because otherwise, it will prompt you twice.  Also, take a look at the change in syntax.

Private Sub Report_Open(Cancel As Integer)
Dim SQL As String, sCompany As String, dYldDate As Date
sCompany = Trim(Str(InputBox("Enter CompanyNumber")))
dYldDate = InputBox("Enter Date")
SQL = "SELECT Yields.MaintenanceYieldDate, Yields.CompanyNumber, " & _
      "DCount('chkYield','Yields','chkYield  and CompanyNumber = " & sCompany & "') AS chkYield, " & _
      "DCount('Yield_Blend','Yields','Yield_Blend  and CompanyNumber = " & sCompany & "') AS Yield_Blend, " & _
      "DCount('BlendOnly','Yields','BlendOnly  and CompanyNumber = " & sCompany & "') AS BlendOnly, " & _
      "DCount('MoveMoney','Yields','MoveMoney  and CompanyNumber = " & sCompany & "') AS MoveMoney, " & _
      "DCount('chkExisting','Yields','chkExisting  and CompanyNumber = " & sCompany & "') AS chkExisting, " & _
      "DCount('chkNewCommit','Yields','chkNewCommit  and CompanyNumber = " & sCompany & "') AS chkNewCommit, " & _
      "DCount('chkBlendForOverDelivery','BlendsForOverDelivery','chkBlendForOverDelivery  and CompanyNumber = " & sCompany & "') AS chkBlendForDelivery, " & _
      "DCount('chkRoll','RollsCombined','chkRoll  and CompanyNumber = " & sCompany & "') AS chkRoll, " & _
      "DCount('chkPairOut','PairOutCombined','chkPairout  and CompanyNumber = " & sCompany & "') AS chkPairOut " & _
   "FROM Yields " & _
   "WHERE (((Yields.MaintenanceYieldDate) = #" & dYldDate & "#) And ((Yields.CompanyNumber) = " & sCompany & ")) " & _
   "GROUP BY Yields.MaintenanceYieldDate, Yields.CompanyNumber, DCount('chkYield','Yields','chkYield  and CompanyNumber = " & sCompany & "'), DCount('Yield_Blend','Yields','Yield_Blend  and CompanyNumber = " & sCompany & "'), DCount('BlendOnly','Yields','BlendOnly  and CompanyNumber = " & sCompany & "'), DCount('MoveMoney','Yields','MoveMoney  and CompanyNumber = " & sCompany & "'), DCount('chkExisting','Yields','chkExisting  and CompanyNumber = " & sCompany & "'), DCount('chkNewCommit','Yields','chkNewCommit  and CompanyNumber = " & sCompany & "'), DCount('chkBlendForOverDelivery','BlendsForOverDelivery','chkBlendForOverDelivery  and CompanyNumber = " & sCompany & "'), DCount('chkRoll','RollsCombined','chkRoll  and CompanyNumber = " & sCompany & "'), DCount('chkPairOut','PairOutCombined','chkPairout  and CompanyNumber = " & sCompany & "');"
   
Me.RecordSource = SQL
 
End Sub

Open in new window

0
 
matthewrhoadesCommented:
The DCount is the issue as it is not a valid SQL statement, what data are you trying to get there?
0
 
ca1358Author Commented:
If the check boxes are True then give me the number that are True

If you run the query "QueryTestFinal", that is the result I want.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
matthewrhoadesCommented:
And you want the user to supply the value for company ID and date?  
0
 
ca1358Author Commented:
Yes
0
 
matthewrhoadesCommented:
Did you try setting the recordsource of the report to the QueryTestFinal?

That will prompt the user for the company ID and date and produce a report that matches the criteria.
0
 
ca1358Author Commented:
Yes and that works but they want me to hide the query(using SQL) within the code.
0
 
dwe761Software EngineerCommented:
Also, you do not want to use CurrentDb.Execute because you do not want to run an action query or to show the results of a SQL statement.  Rather, you want to create a Sql statement that will be used as the recordsource of a report and let the report show the results in your prescribed format.
Also, I used the continuation character of "& _" rather than using SQL = SQL & ...
I like it because it saves a few keystrokes.  But you can do as you like.
0
 
ca1358Author Commented:
Thank you for the Solution and the Advise.  Very much appreciated!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.