Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    West Yorkshire - UK
    Posts
    6

    Thumbs up Unanswered: query last record

    I have a query which contains account numbers with payments against them, the list also contains a system time stamp which increases each time a new payment is made. What I want to do is pull out the last payment for each account as this will be used to show the balance on a statement.
    Can you help me???

    Thanks, Dan

  2. #2
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91

    Re: query last record

    Originally posted by DesperateDan
    I have a query which contains account numbers with payments against them, the list also contains a system time stamp which increases each time a new payment is made. What I want to do is pull out the last payment for each account as this will be used to show the balance on a statement.
    Can you help me???

    Thanks, Dan
    You should create a recordset:

    Dim rs as adodb.recordset
    set rs = New RecordSet

    With rs
    .CursorType = adOpenForwardOnly
    .LockType = adLockOptimistic
    .Source = "SELECT * FROM YourQueryName Order By YourFieldOrderName where AccountNumer =" & VariableName
    .Open , CurrentProject.Connection
    .MoveLast

    Your code

    .Close

    end with

    Set rs = Nothing


    It should work !

  3. #3
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: query last record

    SELECT ACCOUNT_NO, MAX(DATE_STAMP)
    FROM MY_TABLE
    GROUP BY ACCOUNT_NO
    ORDER BY ACCOUNT_NO, MAX(DATE_STAMP)


    Originally posted by DesperateDan
    I have a query which contains account numbers with payments against them, the list also contains a system time stamp which increases each time a new payment is made. What I want to do is pull out the last payment for each account as this will be used to show the balance on a statement.
    Can you help me???

    Thanks, Dan

  4. #4
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91

    Re: query last record

    The source line in my code will become:

    .Source = "SELECT ACCOUNT_NO, DATE_STAMP FROM my_Table Order By Date_Stamp where Account_No =" & cAccNo

    Important: cAccNo is a variable that has the account number you´re working on.
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

Posting Permissions

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