Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Find Month & Year Help

    Hi,

    To perform a particular task I need to dsiplay sum for debit column in Loantable where DATE is an important factor so that it would show me how much loan given in this month to a person.

    My below idea was to display on a form's unbound text box which worked for the whole year. But since the year 2008 started, it shows the sum for last year's (2007). because my criteria is set to match for Month only.

    LoanGivenThisMonth = DSum("Debit", "LOANTABLE", "Month([PDate]) = " & Month(Now()))

    I would request you to show me how can I change this code that could display the result based on current pc month and Year as well. Bcz data stored in the table is for more than 12 months.

    Hopefully I clearly explained.

    Thank you,
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Dim myCriteria As String
    
    myCriteria = "Month([PDate]) = " & Month(Now())
    myCriteria = myCriteria & " AND Year([PDate]) = " & Year(Now())
    
    LoanGivenThisMonth = DSum("Debit", "LOANTABLE", myCriteria)
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Dunno much about the Domain Aggregates, but if they're able to utilize indices, and they work similarly SQL aggregates and the field in question is indexed, then perhaps

    myCriteria = "[PDate] between #" & format$(dateserial(year(date), month(date), 1), "yyyy-mm-dd") & "# and #" & format$(dateserial(year(date), month(date) +1, 0), "yyyy-mm-dd") & "#"

    (stole the criteria part from georgev)

    Note - DSum returns Null if there's no match - do you handle that any way?
    Roy-Vidar

  4. #4
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks to both gentlmen,

    Yes RoyVidar, I did not handled yet. I believe it shoud be:

    LoanGivenThisMonth = Nz(DSum("Debit", "LOANTABLE", myCriteria),0)

    Correct ?
    With kind regards,
    Ashfaque

  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Yup, that looks right.

    I would probably use a recordset.

    dim rs as dao.recordset
    dim sqlstring as string
    ' <assigning the criterion>
    sqlstring = "SELECT Sum(Debit) FROM LOANTABLE WHERE " & myCriteria
    set rs = currentdb.openrecordset(sqlstring)
    if rs.recordcount>0 then
    LoanGivenThisMonth = rs.fields(0).value
    else
    LoanGivenThisMonth = 0
    end if
    Roy-Vidar

Posting Permissions

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