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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
matthewrhoadesCommented:
And you want the user to supply the value for company ID and date?  
0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.