Results 1 to 2 of 2
  1. #1
    txrules is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Dec 2010
    Posts
    3

    Subtract From Previous Record Using a Date

    I have a chemical usage database that I need some help with.



    I have the following table:

    tblInventory
    InvDate | TankID | Gallons

    I want to subtract the newest inventory from the previous inventory.
    How do I get the Max date from tblInventory to subtract from the previous date?

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    I would assume that if you've already posted something like this, you should be able to answer this question on your own.

    to get a max date:
    Code:
    max([datefield])
    to subtract a max date from the previous date, it's too tough with the query builder alone. You need code:
    Code:
    set rs = db.openrecordset("select top 2 * from table order by [datefield] desc")
    
    rs.movelast
       variable = rs!value
    rs.moveprevious
       variable2 = rs!value
    
    result = variable2 - variable
    Again, it is possible with the query builder, but the specs of your problem are not clear, but the idea to solve the problem is certainly there.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 07-06-2013, 05:13 PM
  2. Replies: 9
    Last Post: 03-19-2010, 10:37 AM
  3. SQL Subquery on Previous Record
    By GoVols02 in forum Queries
    Replies: 1
    Last Post: 01-05-2010, 02:40 PM
  4. Populate date from previous record
    By Lianes in forum Access
    Replies: 3
    Last Post: 09-19-2009, 03:42 PM
  5. using value from previous record
    By dollars in forum Queries
    Replies: 0
    Last Post: 12-10-2008, 03:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums