www.pudn.com > sqlcount.zip > FORM1.FRM

VERSION 5.00 
Begin VB.Form Form1  
   Caption         =   "Form1" 
   ClientHeight    =   2715 
   ClientLeft      =   1620 
   ClientTop       =   1545 
   ClientWidth     =   3855 
   LinkTopic       =   "Form1" 
   ScaleHeight     =   2715 
   ScaleWidth      =   3855 
   Begin VB.ComboBox cboName  
      Height          =   315 
      ItemData        =   "Form1.frx":0000 
      Left            =   720 
      List            =   "Form1.frx":0010 
      Style           =   2  'Dropdown List 
      TabIndex        =   12 
      Top             =   600 
      Width           =   1215 
   End 
   Begin VB.CommandButton cmdCount  
      Caption         =   "Count" 
      Height          =   495 
      Left            =   360 
      TabIndex        =   10 
      Top             =   1080 
      Width           =   1215 
   End 
   Begin VB.Label Label1  
      Alignment       =   2  'Center 
      Caption         =   "Total" 
      Height          =   255 
      Index           =   7 
      Left            =   2040 
      TabIndex        =   14 
      Top             =   2040 
      Width           =   495 
   End 
   Begin VB.Label lblCountTotal  
      BorderStyle     =   1  'Fixed Single 
      Height          =   255 
      Left            =   2880 
      TabIndex        =   13 
      Top             =   2040 
      Width           =   495 
   End 
   Begin VB.Label Label1  
      Alignment       =   2  'Center 
      Caption         =   "Name" 
      Height          =   255 
      Index           =   6 
      Left            =   120 
      TabIndex        =   11 
      Top             =   600 
      Width           =   495 
   End 
   Begin VB.Label lblCountO  
      BorderStyle     =   1  'Fixed Single 
      Height          =   255 
      Left            =   2880 
      TabIndex        =   9 
      Top             =   1680 
      Width           =   495 
   End 
   Begin VB.Label lblCountF  
      BorderStyle     =   1  'Fixed Single 
      Height          =   255 
      Left            =   2880 
      TabIndex        =   8 
      Top             =   1320 
      Width           =   495 
   End 
   Begin VB.Label lblCountC  
      BorderStyle     =   1  'Fixed Single 
      Height          =   255 
      Left            =   2880 
      TabIndex        =   7 
      Top             =   960 
      Width           =   495 
   End 
   Begin VB.Label lblCountP  
      BorderStyle     =   1  'Fixed Single 
      Height          =   255 
      Left            =   2880 
      TabIndex        =   6 
      Top             =   600 
      Width           =   495 
   End 
   Begin VB.Label Label1  
      Alignment       =   2  'Center 
      Caption         =   "O" 
      Height          =   255 
      Index           =   5 
      Left            =   2040 
      TabIndex        =   5 
      Top             =   1680 
      Width           =   495 
   End 
   Begin VB.Label Label1  
      Alignment       =   2  'Center 
      Caption         =   "F" 
      Height          =   255 
      Index           =   4 
      Left            =   2040 
      TabIndex        =   4 
      Top             =   1320 
      Width           =   495 
   End 
   Begin VB.Label Label1  
      Alignment       =   2  'Center 
      Caption         =   "C" 
      Height          =   255 
      Index           =   3 
      Left            =   2040 
      TabIndex        =   3 
      Top             =   960 
      Width           =   495 
   End 
   Begin VB.Label Label1  
      Alignment       =   2  'Center 
      Caption         =   "P" 
      Height          =   255 
      Index           =   2 
      Left            =   2040 
      TabIndex        =   2 
      Top             =   600 
      Width           =   495 
   End 
   Begin VB.Label Label1  
      Alignment       =   2  'Center 
      Caption         =   "Count" 
      Height          =   255 
      Index           =   1 
      Left            =   2880 
      TabIndex        =   1 
      Top             =   240 
      Width           =   495 
   End 
   Begin VB.Label Label1  
      Alignment       =   2  'Center 
      Caption         =   "Status" 
      Height          =   255 
      Index           =   0 
      Left            =   2040 
      TabIndex        =   0 
      Top             =   240 
      Width           =   495 
   End 
End 
Attribute VB_Name = "Form1" 
Attribute VB_GlobalNameSpace = False 
Attribute VB_Creatable = False 
Attribute VB_PredeclaredId = True 
Attribute VB_Exposed = False 
Option Explicit 
 
' Make some random test data. 
Private Sub MakeData() 
Dim dbString As String 
Dim db As Database 
Dim query As String 
Dim i As Integer 
Dim names(0 To 3) As String 
Dim statuses(0 To 3) As String 
Dim new_name As String 
Dim new_status As String 
Dim new_amount As Currency 
 
    ' Open the database. 
    dbString = App.Path & "\sales.mdb" 
    Set db = Opendatabase(dbString) 
     
    ' Make the random data. 
    statuses(0) = "P" 
    statuses(1) = "C" 
    statuses(2) = "F" 
    statuses(3) = "O" 
    names(0) = "Remkus" 
    names(1) = "Stephens" 
    names(2) = "Johnson" 
    names(3) = "Smythe" 
     
    Randomize 
    For i = 1 To 50 
        new_name = names(Int(Rnd * 4)) 
        new_status = statuses(Int(Rnd * 4)) 
        new_amount = 50 + Rnd * 1000 
        query = "INSERT INTO sales Values(" & _ 
            "'" & new_name & "', " & _ 
            "'" & new_status & "', " & _ 
            new_amount & ")" 
        db.Execute query 
    Next i 
     
    db.Close 
End Sub 
 
Private Sub cmdCount_Click() 
Dim dbString As String 
Dim db As Database 
Dim rs As Recordset 
Dim lstrSQL As String 
 
    lstrSQL = "" 
    lstrSQL = lstrSQL & "Select Count(*) as TotalCount, " 
    lstrSQL = lstrSQL & "Count(iif([status] = 'P', 1, null)) as PCount, " 
    lstrSQL = lstrSQL & "Count(iif([status] = 'C', 1, null)) as CCount, " 
    lstrSQL = lstrSQL & "Count(iif([status] = 'F', 1, null)) as FCount, " 
    lstrSQL = lstrSQL & "Count(iif([status] = 'O', 1, null)) as OCount " 
    lstrSQL = lstrSQL & "From Sales " 
    lstrSQL = lstrSQL & "Where [Employee] = '" & _ 
        cboName.Text & "'" 
 
    dbString = App.Path & "\sales.mdb" 
    Set db = Opendatabase(dbString) 
     
    ' Execute the query. 
    Set rs = db.OpenRecordset(lstrSQL, dbOpenSnapshot) 
 
    ' Display the results. 
    lblCountTotal.Caption = Format$(rs(0)) 
    lblCountP.Caption = Format$(rs(1)) 
    lblCountC.Caption = Format$(rs(2)) 
    lblCountF.Caption = Format$(rs(3)) 
    lblCountO.Caption = Format$(rs(4)) 
 
    rs.Close 
    db.Close 
    Set rs = Nothing 
    Set db = Nothing 
 
End Sub 
 
Private Sub Form_Load() 
    ' Uncomment the following to make some random data. 
    ' MakeData 
    ' End 
     
    cboName.ListIndex = 0 
End Sub