[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Syntax  2003 Access VBA

Posted on 2008-11-13
9
Medium Priority
?
189 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:ca1358
  • 4
  • 3
  • 2
9 Comments
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 22955297
The DCount is the issue as it is not a valid SQL statement, what data are you trying to get there?
0
 

Author Comment

by:ca1358
ID: 22955334
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
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 22955380
And you want the user to supply the value for company ID and date?  
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.

 

Author Comment

by:ca1358
ID: 22955395
Yes
0
 
LVL 6

Expert Comment

by:matthewrhoades
ID: 22955410
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
 

Author Comment

by:ca1358
ID: 22955425
Yes and that works but they want me to hide the query(using SQL) within the code.
0
 
LVL 10

Accepted Solution

by:
dwe761 earned 2000 total points
ID: 22960669
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
 
LVL 10

Expert Comment

by:dwe761
ID: 22960746
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
 

Author Closing Comment

by:ca1358
ID: 31516604
Thank you for the Solution and the Advise.  Very much appreciated!!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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