Solved

How to sum column by SQL and DAO

Posted on 2004-09-07
5
497 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

920 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

17 Experts available now in Live!

Get 1:1 Help Now