Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup Previous Date (2003)

    I am trying to lookup a date and the dates are not continuous. I am entering data for an account and want to calculate a balance but I need to look at the previous record balance adjust for activity and post the ending balance. Can anyone help?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Previous Date (2003)

    Welcome to Woody's Lounge (and sorry about the delay).

    Do you have the balance in a table or query? If so, you can use DLookup, either in a query, or in the control source of a control on the form used to enter the data. For example as a control source:

    DLookUp("[Balance]","NameOfTable","[DateField]=# " & Format(Nz(DMax("[DateField]","NameOfTable","[DateField]<#" & Format([DateField],"mm/dd/yyy") & "#"),#1/1/100#),"mm/dd/yyyy") & "#")

    where NameOfTable, Balance and DateField must be replaced with the appropriate names. 1/1/100 is just an arbitrary non-occurring date.

  3. #3
    New Lounger
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Previous Date (2003)

    What is different if anything if I have it in a query?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Previous Date (2003)

    You'd use the same expression. You can prefix the expression by the desired column name followed by a colon:

    PreviousBalance: DLookup(...)

  5. #5
    New Lounger
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Previous Date (2003)

    I put that formula in the Field section of the query and my return value is 0 for every row. It is not pulling the max date and returning the balance.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Previous Date (2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    New Lounger
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Previous Date (2003)

    The formula works - I got that far but I think I asked the wrong question.

    I have a continuous form that I enter activity (deposits and withdrawals). I am trying to create a balance to show on the form after the activity is entered. This is where I need to pull the ending balance on the previous record to be the beginning balance on the current record.

    I may be going about this the wrong way. Can you help?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Previous Date (2003)

    That might in fact be easier. You could take the sum of all deposits minus the sum of all withdrawals - that should yield the balance. You can use DSum for this.

Posting Permissions

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