Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to sum column by SQL and DAO

Posted on 2004-09-07
5
Medium Priority
?
534 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
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

604 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