Solved

How to sum column by SQL and DAO

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

813 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

8 Experts available now in Live!

Get 1:1 Help Now