[RESOLVED] SUM in a query???-VBForums
Results 1 to 7 of 7

Thread: [RESOLVED] SUM in a query???

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2016
    Posts
    335

    Resolved [RESOLVED] SUM in a query???

    Hello programmers
    I want to calculate the sum in a numeric field.
    I used this query
    Code:
    sSQL = "select Sum(Total) As TotalSum from transac WHERE date_Transac = #" & Format(Date, "mm/dd/yyyy") & "#"
    RS.open sSQL, DB, adOpenStatic, adLockOptimistic
    For testing the valididty of the querry I added the following:
    Code:
    If Not RS.EOF Then
    MsgBox "yes"
    Else
    MsgBox "no"
    End If
    The result of this query is driving me mad.
    The msgbox is always YES even no case matches the query.
    If I drop the sum(Total) from the query , the result is perfect.
    I get the message NO in case EOF is True and Yes message if EOF is False.
    Code:
    sSQL = "select * from transac WHERE date_Transac = #" & Format(Date, "mm/dd/yyyy") & "#"
    RS.open sSQL, DB, adOpenStatic, adLockOptimistic
    Even if I select another item, the result is correct too.
    Code:
    sSQL = "select Total from transac WHERE date_Transac = #" & Format(Date, "mm/dd/yyyy") & "#"
    RS.open sSQL, DB, adOpenStatic, adLockOptimistic
    My trouble with Sum(total) seems without explanation.
    Thanks for any help.

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,181

    Re: SUM in a query???

    if there are no matches then the sum would be 0. It will always return one record

    so rather than
    Code:
    If Not RS.EOF Then
    Try
    Code:
    If rs(0)>0 Then

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,875

    Re: SUM in a query???

    Why are you asking for a writeable cursor?

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2016
    Posts
    335

    Re: SUM in a query???

    DataMiser
    Thanks a lot
    Your suggestion seems working perfectly.
    I even tested
    Code:
    If RS.RecordCount > 0 Then
    And it is working perfectly.
    Thanks a lot sir
    Last edited by Mustaphi; Mar 9th, 2017 at 10:32 AM.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2016
    Posts
    335

    Re: SUM in a query???

    Quote Originally Posted by dilettante View Post
    Why are you asking for a writeable cursor?
    Sorry I did not understand the question.
    If you are asking about :
    Code:
    If Not RS.EOF Then
    MsgBox "yes"
    Else
    MsgBox "no"
    End If
    Then I'm doing so because I was having trouble with the query and I did so to test its validity.
    Thanks

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,181

    Re: [RESOLVED] SUM in a query???

    He was referring to this
    adOpenStatic

    that opens a writeable cursor to the database where clearly you do not need one for this.

    A forward only cursor would be more suitable here

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,875

    Re: [RESOLVED] SUM in a query???

    Actually I was asking about adLockOptimistic which is telling ADO to tell the Provider to return a writable-cursor rowset if possible. Since the query is not writable this gets ignored, but may result in unnecessary locking overhead and is misleading to anyone reading your code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.