Solved

How to sum column by SQL and DAO

Posted on 2004-09-07
5
492 Views
Last Modified: 2013-12-25
Hello,

I have a simple program in VB which uses DAO to connect to and Access 97 database.
I need to do a check on one column of records which contains certain amounts of money, so this sum doesn't go further than I want. So when the user wants to add more transactions, the program tells him that he can't input any more money amounts because he/she has already filled the daily permission

The table where I get the column is called "listici" and the row is called "ulog". In the table "parametri" are some other parameters along with the row "daycheck" which contains variable where program checks sum against daycheck, if sum is bigger than daycheck's value, he should display the message.

Here's the code when customer clicks the button for new transaction:

Private Sub Command4_Click(Index As Integer)

    Select Case Index
   
    Case 0  'new
       
        'check for daily revenue limit
        If Data1.RecordSource = "SELECT DISTINCT SUM(ulog) WHERE storno = " & False & "" >= parametri("daycheck") Then
        MsgBox "Your transactions are over limits", vbCritical + vbOKOnly
        Unload MDIForm1 'close program
        Else
       
        Option1.Value = True
        Option2.Value = False
        Option3.Value = False
       
        DBGrid2.Enabled = False
        Text1(1).SetFocus
        Command4(0).Enabled = False
        Command4(1).Enabled = False
        Command2.Enabled = False
        Command4(3).Enabled = True
        Command4(5).Enabled = True
        Command4(4).Enabled = False
        Check1.Enabled = True
        End If
       
    Case 1  
........

Thanks in advance for your kindness and help !
0
Comment
Question by:gereqi
  • 2
5 Comments
 
LVL 8

Expert Comment

by:mladenovicz
ID: 11997247
Here is the code (ADO, just convert it to use DAO)

Public Function GetAggregateValue(Qry As String) As Long
   
    Dim adoRS   As ADODB.Recordset
    Dim adoConn As ADODB.Connection
   
    Set adoConn = New ADODB.Connection
    adoConn.Open ConnectionString
   
    Set adoRS = New ADODB.Recordset
    adoRS.Open Qry, adoConn
   
    If Not (adoRS.EOF And adoRS.BOF) Then
        If Not IsNull(adoRS.Fields(0).Value) Then
            GetAggregateValue = adoRS.Fields(0).Value
        End If
    End If

    On Error Resume Next
    adoRS.Close
    Set adoRS = Nothing
    adoConn.Close
    Set adoConn = Nothing

End Function

I assume that parametri table has paramName and paramValue fields. Change this according to your needs.

Qry1 ="SELECT SUM(ulog) FROM Listici WHERE Storno = False" '--you shoud include date check here (e.g. all storno for today)
Qry2 = "SELECT  ParamValue FROM Parametri WHERE ParamName = 'daycheck'"

 If GetAggregateValue(qry1) > GetAggregateValue(Qry2) Then
        MsgBox "Your transactions are over limits", vbCritical + vbOKOnly
        Unload MDIForm1 'close program
End if
0
 

Author Comment

by:gereqi
ID: 11997836
Hi there,

thanks for your kind a fast reply.

now when I try to run the program (CTRL+F5), it displays the error message "ByRef argument type mismatch" and selects Qry1 at the script below:

 If GetAggregateValue(qry1) > GetAggregateValue(Qry2) Then
        MsgBox "Your transactions are over limits", vbCritical + vbOKOnly
        Unload MDIForm1 'close program
End if
 
Any idea how to solve this???

also, @ the Parametri table, paramName is "daycheck" and value is variable as days change, so I can chenge as much as I like the value and the customer after receiveng the new value won't input more transactions than permitted
0
 
LVL 8

Accepted Solution

by:
mladenovicz earned 250 total points
ID: 11997877
Declare variables

Dim Qry1 As String
Dim Qry2 As String
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now