?
Solved

How to sum column by SQL and DAO

Posted on 2004-09-07
5
Medium Priority
?
517 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 total points
ID: 11997877
Declare variables

Dim Qry1 As String
Dim Qry2 As String
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month12 days, 18 hours left to enroll

777 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