Results 1 to 3 of 3
  1. #1
    SMC's Avatar
    SMC is offline Master Ninja
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Columbus, OH
    Posts
    28

    Dlookup Help

    I want to add a second criteria inside my criteria inside my Dlookup code.

    The first criteria is an item number which is input into my form.
    The second criteria is the date. I need the most recent date, but not a date more recent than the record I am working on.

    The reason for the second criteria is due to missing a weekly entry and going back to enter it at a later time. I don't want data pulled from the newest date, since technically it didnt exist when the data should have been entered.
    Example: Data from 6/21 and 6/25 have already been entered. Forwhat ever reason, data for 6/23 did not get entered. When I got back to enter my 6/23 data I need the highest date less than 6/23 to be specified.


    Here is what I have so far. It is also an addition function as I need two values from the previous date added to get a new total in the current date.



    NEWTotal = DLookup("[OLDTotal]", "Table", "[Record Date] =" & DMax("[Record Date]", "Table", "[IDNumber] =" & Forms!Input!txtIDNumber)) + _
    DLookup("[OLDCurrent]", "Table", "[Record Date] =" & DMax("[Record Date]", "Table", "[IDNumber] =" & Forms!Input!txtIDNumber))
    Forms!Input!txtOLDTotal = NEWTotal

    In summary:
    The idea is to add the old total and the previous days increase to get a new total. But I want the ability to be able to add records in non-sequential order and it will always pull the highest date less than the inputdate for that record.

    I hope this makes sense,

    Thanks!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    What is the purpose of your database - in general terms?

    What is the purpose of this calculation?
    If you are storing calculated fields in a database table, people here will advise you that that is a poor technique.
    Do your calculations in queries.

  3. #3
    SMC's Avatar
    SMC is offline Master Ninja
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Columbus, OH
    Posts
    28
    Thanks, orange.

    I have reassessed my approach. I guess I was so determined on solving my problem the way I originally imagined, I was not considering other options which would make it more simple. I have developed a caculation in a query to obtain the data I need.
    -SMC

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

Similar Threads

  1. Dlookup help
    By justhininabouti in forum Programming
    Replies: 2
    Last Post: 11-26-2011, 10:53 PM
  2. Dlookup
    By cbrsix in forum Forms
    Replies: 2
    Last Post: 11-01-2011, 02:59 PM
  3. DLookup
    By neil45156 in forum Forms
    Replies: 1
    Last Post: 04-11-2011, 11:31 AM
  4. DLookup()
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 09-15-2010, 07:20 AM
  5. Need help with dlookup.
    By Keith in forum Database Design
    Replies: 8
    Last Post: 05-24-2010, 06:28 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