Difference between revisions of "Microsoft KB Archive/172593"

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - """ to """)
Line 82: Line 82:
 
       'This is the Path & Database filename.
 
       'This is the Path & Database filename.
 
       'Change to match your path and filename.
 
       'Change to match your path and filename.
       DatabaseFile = "C:\VB5\Nwind.mdb"
+
       DatabaseFile = "C:\VB5\Nwind.mdb"
  
 
       'Create the querydef SQL string
 
       'Create the querydef SQL string
       Sql = "Parameters [Enter first date] DateTime, " _
+
       Sql = "Parameters [Enter first date] DateTime, " _
         & "[Enter second date] DateTime;"
+
         & "[Enter second date] DateTime;"
       Sql = Sql & " TRANSFORM Sum(CCur([Order Details].[UnitPrice]" _
+
       Sql = Sql & " TRANSFORM Sum(CCur([Order Details].[UnitPrice]" _
         & " *[Quantity]*(1-[Discount])/100)*100) AS ProductAmount"
+
         & " *[Quantity]*(1-[Discount])/100)*100) AS ProductAmount"
       Sql = Sql & " SELECT Products.ProductName, Orders.CustomerID," _
+
       Sql = Sql & " SELECT Products.ProductName, Orders.CustomerID," _
         & " Year([OrderDate]) AS OrderYear"
+
         & " Year([OrderDate]) AS OrderYear"
       Sql = Sql & " FROM Products INNER JOIN (Orders INNER JOIN" _
+
       Sql = Sql & " FROM Products INNER JOIN (Orders INNER JOIN" _
         & " [Order Details] ON Orders.OrderID ="
+
         & " [Order Details] ON Orders.OrderID ="
       Sql = Sql & " [Order Details].OrderID) ON" _
+
       Sql = Sql & " [Order Details].OrderID) ON" _
         & " Products.ProductID = [Order Details].ProductID"
+
         & " Products.ProductID = [Order Details].ProductID"
       Sql = Sql & " WHERE (((Orders.OrderDate) Between" _
+
       Sql = Sql & " WHERE (((Orders.OrderDate) Between" _
         & " [Enter first date] And [Enter second date]))"
+
         & " [Enter first date] And [Enter second date]))"
       Sql = Sql & " GROUP BY Products.ProductName, Orders.CustomerID," _
+
       Sql = Sql & " GROUP BY Products.ProductName, Orders.CustomerID," _
         & " Year([OrderDate])"
+
         & " Year([OrderDate])"
       Sql = Sql & " PIVOT 'Qtr ' & DatePart('q',[OrderDate],1,0) In" _
+
       Sql = Sql & " PIVOT 'Qtr ' & DatePart('q',[OrderDate],1,0) In" _
         & " ('Qtr 1','Qtr 2','Qtr 3','Qtr 4');"
+
         & " ('Qtr 1','Qtr 2','Qtr 3','Qtr 4');"
  
 
       'This code creates the parameter query
 
       'This code creates the parameter query
  
 
       Set MyDB = DBEngine(0).OpenDatabase(DatabaseFile)
 
       Set MyDB = DBEngine(0).OpenDatabase(DatabaseFile)
       Set QD = MyDB.CreateQueryDef("CrossTabParamQuery")
+
       Set QD = MyDB.CreateQueryDef("CrossTabParamQuery")
 
       With QD
 
       With QD
 
           .Sql = Sql
 
           .Sql = Sql
           .Parameters("[Enter first date]") = #1/1/95#
+
           .Parameters("[Enter first date]") = #1/1/95#
           .Parameters("[Enter second date]") = #1/1/96#
+
           .Parameters("[Enter second date]") = #1/1/96#
 
       End With
 
       End With
 
       Set Rs = QD.OpenRecordset(dbOpenDynaset)
 
       Set Rs = QD.OpenRecordset(dbOpenDynaset)
Line 117: Line 117:
 
       errorHand:
 
       errorHand:
 
       If Err.Number = 3012 Then  ' The querydef exists
 
       If Err.Number = 3012 Then  ' The querydef exists
           MyDB.QueryDefs.Delete "CrossTabParamQuery"
+
           MyDB.QueryDefs.Delete "CrossTabParamQuery"
 
           Resume
 
           Resume
 
       Else
 
       Else
           MsgBox Err.Number & " " & Err.Description
+
           MsgBox Err.Number & " " & Err.Description
 
       End If
 
       End If
 
       End Sub
 
       End Sub
Line 138: Line 138:
 
== REFERENCES ==
 
== REFERENCES ==
  
Visual Basic Help topic &quot;PARAMETERS Declaration&quot;<br />
+
Visual Basic Help topic "PARAMETERS Declaration"<br />
 
For more information, please see the following article in the Microsoft Knowledge Base:<br />
 
For more information, please see the following article in the Microsoft Knowledge Base:<br />
  
 
<div class="indent">
 
<div class="indent">
  
'''[[../91710|91710]]''' : Can't Bind Name '[XXX]'&quot; Error with Crosstab Query
+
'''[[../91710|91710]]''' : Can't Bind Name '[XXX]'" Error with Crosstab Query
  
  

Revision as of 11:06, 21 July 2020

Knowledge Base


How To Populate DBGrid from Crosstab Parameter Query

Article ID: 172593

Article Last Modified on 7/1/2004



APPLIES TO

  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 16-bit Enterprise Edition
  • Microsoft Visual Basic 4.0 32-Bit Enterprise Edition



This article was previously published under Q172593

SUMMARY

This article demonstrates how to create a Parameter Crosstab Query using the sample Nwind.mdb Access database file. It also explains how to create a recordset in Visual Basic by running the query, passing the necessary parameters to it, and then populating a Visual Basic DBGrid with the resultant recordset.

MORE INFORMATION

The following example uses the sample Nwind database that ships with Visual Basic. Note that it is recommended to explicitly declare the parameters when creating and running Crosstab Parameter Queries. See the REFERENCES section of this article for more information.

  1. Create a new Project in Visual Basic.
  2. From the menu, select Project - References and make a reference to Microsoft DAO 3.5 Object Library. In Visual Basic 4.0, under Tools references make, a reference to DAO 3.0 object library.
  3. From the menu, select Project - Components and select a Data Bound Grid control. In Visual Basic 4.0, this is under Tools - Custom Controls.
  4. Add a CommandButton, a Data Control and a DBGrid Control to the new Form.
  5. Accept the default Names for the Command Button, DBGrid, and Data Control.
  6. Enter the sample code below into the General Declarations of the Form.
  7. In the properties Window, set the DataSource property of the DBGrid to Data1 to bind the DBGrid to the Data control.
  8. Run the application and click the CommandButton.

    Sample code:

          Private Sub Command1_Click()
            Dim Sql As String
            Dim DatabaseFile As String
            Dim MyDB As Database
            Dim RS As Recordset
            Dim QD As QueryDef
    
          On Error GoTo errorHand
    
          'This is the Path & Database filename.
          'Change to match your path and filename.
          DatabaseFile = "C:\VB5\Nwind.mdb"
    
          'Create the querydef SQL string
          Sql = "Parameters [Enter first date] DateTime, " _
             & "[Enter second date] DateTime;"
          Sql = Sql & " TRANSFORM Sum(CCur([Order Details].[UnitPrice]" _
             & " *[Quantity]*(1-[Discount])/100)*100) AS ProductAmount"
          Sql = Sql & " SELECT Products.ProductName, Orders.CustomerID," _
             & " Year([OrderDate]) AS OrderYear"
          Sql = Sql & " FROM Products INNER JOIN (Orders INNER JOIN" _
             & " [Order Details] ON Orders.OrderID ="
          Sql = Sql & " [Order Details].OrderID) ON" _
             & " Products.ProductID = [Order Details].ProductID"
          Sql = Sql & " WHERE (((Orders.OrderDate) Between" _
             & " [Enter first date] And [Enter second date]))"
          Sql = Sql & " GROUP BY Products.ProductName, Orders.CustomerID," _
             & " Year([OrderDate])"
          Sql = Sql & " PIVOT 'Qtr ' & DatePart('q',[OrderDate],1,0) In" _
             & " ('Qtr 1','Qtr 2','Qtr 3','Qtr 4');"
    
          'This code creates the parameter query
    
          Set MyDB = DBEngine(0).OpenDatabase(DatabaseFile)
          Set QD = MyDB.CreateQueryDef("CrossTabParamQuery")
          With QD
              .Sql = Sql
              .Parameters("[Enter first date]") = #1/1/95#
              .Parameters("[Enter second date]") = #1/1/96#
          End With
          Set Rs = QD.OpenRecordset(dbOpenDynaset)
          Set Data1.Recordset = Rs
          Exit Sub
    
          errorHand:
          If Err.Number = 3012 Then   ' The querydef exists
              MyDB.QueryDefs.Delete "CrossTabParamQuery"
              Resume
          Else
              MsgBox Err.Number & "  " & Err.Description
          End If
          End Sub
    
          Private Sub Form_Unload(Cancel As Integer)
             RS.Close
             QD.Close
             MyDB.Close
          End Sub
    
                            


REFERENCES

Visual Basic Help topic "PARAMETERS Declaration"
For more information, please see the following article in the Microsoft Knowledge Base:

91710 : Can't Bind Name '[XXX]'" Error with Crosstab Query



Additional query words: kbVBp500 kbVBp600 kbdse kbDSupport kbVBp kbVBp400

Keywords: kbhowto KB172593