Results 1 to 5 of 5
  1. #1
    top1hat19 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2011
    Posts
    14

    Dynamic Data


    I have a database with a list of Names and 2 numbers and a date, like this:

    Name | Num1 | Num2 |__ Date
    _Billy_|__56__|___1__|_2/11/2010
    _Billy_|__78__|______|_2/12/2010
    ...


    When I add the second row, I want Num2 to automatically generate based on this:

    If Num1>50, check Billy's Num2 from yesterday and add 1, else copy Num2 from yesterday

    Thus Num2 should be 2 in the second row. I want to be able to do this with multiple names when I add new records every day. How can I do this?

  2. #2
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    If you're doing this on a set of multiple records in the table, you'll most likely need to open a recordset in vba and loop through it. Otherwise if you know the Name (hopefully a better field name than just Name and Date is something other than just Date), you could possibly utilize the dlookup command to do this combined with some vba code if this is on a form.
    ie. (after renaming Name field to NameField and Date to DateField...)

    dim vNum1 as variant
    vNum1 = dlookup("[Num1]","myTableName","[NameField] = 'Billy' and [DateField] = #" & Date()-1 & "#"")
    and then use this as part of your calculation for Num2.

    But I think your most likely going to need to open a recordset in vba and loop through it.

    Can you be more specific on the scenario on how you want to update this (ie. via a query, via a form,....????)

  3. #3
    top1hat19 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2011
    Posts
    14
    I think a query would be best for my purpose.

  4. #4
    top1hat19 is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2011
    Posts
    14
    Also, I need to use this for multiple names, so the part of your code that says:

    [NameField] = 'Billy'

    would only work for Billy's records. Is there a way to just specify something like "NameFields that are equals, and Date-1"?

  5. #5
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    I think you're best option is to write some vba code to open a recordset and loop through it. Unlike Excel, in a query, MSAccess can't grab a 'previous' value and base updating a field on that record based upon what the previous value was. To do this, you'll most likely need to open a recordset and loop through the records so you can save/use/calculate previous values in your calculation to update the current record in the recordset.

    Give a shot at writing a bit of vba code to do the looping and we can then help you on the syntax/logic.

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

Similar Threads

  1. Dynamic functions
    By bubu678 in forum Programming
    Replies: 2
    Last Post: 11-09-2010, 09:55 PM
  2. Dynamic reports
    By amonadi in forum Reports
    Replies: 1
    Last Post: 10-29-2010, 01:32 AM
  3. Dynamic Web page
    By KevinH in forum Access
    Replies: 3
    Last Post: 01-03-2010, 06:56 PM
  4. help with dynamic hyperlink to pdf
    By iresolver in forum Access
    Replies: 0
    Last Post: 12-24-2008, 01:21 PM
  5. dynamic textbox
    By vam in forum Programming
    Replies: 1
    Last Post: 06-04-2006, 08:59 AM

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