Results 1 to 2 of 2
  1. #1
    Weelmaa is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2012
    Posts
    3

    Assigning Value to Unbound Textbox in Report based on a Sorted Column

    I have a database where it scores vessels based on criteria and their values. In the event of a tie, my tiebreaker is what time a vessel stopped.
    The tiebreaker is simple in concept. If there are 10 vessels in the table, I would have access count the number of vessels in my table, sort the date vessel stopped column from oldest to newest and then assing the value 10 to the top vessel, 9 to the one below it, 8 to the one below that and so forth.

    I have a report that has calculated a vessels priority, however, I do not know how to cycle through each record displayed in the report and then assign the tie breaking value to the priority score. In excel i would accomplish this with writing a for statement that would cycle from the first record to the last record...offseting to my priorty Score column each time and then adding the tiebreaking score to it.

    Dim x as integer
    Dim Acell as Range
    x = Application.WorksheetFunction.CountA(Sheets(1).Ran ge("A2:"A60000"))
    For Each Acell in Range("A2:A60000")
    Acell.Offset(0,WheremyPriorityScoreis).value = Acell.Offset(0,WheremyPriorityScoreis).value + x
    x = x-1
    Next Acell

    How do i accomplish something similar to a report in access? At first I thought the correct way would be something like this:

    x= Dcount("Stopped","Table")
    For each Record in Field
    me.priorityscore.value = me.priorityscore.value + x
    x = x - 1
    Next Record.



    But I found out miserably that this is totally wrong. Any help with this would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You have the report include the time stoped in the record sort? Sort by newest to oldest then use RunningSum property in a textbox with a ControlSource of =1.

    Anything else will require VBA custom function that can be called by query or textbox. The function would probably have to open a recordset and evaluate the current record's rank within the recordset. I don't see this would be simple.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 4
    Last Post: 12-25-2011, 06:31 PM
  2. Replies: 1
    Last Post: 12-08-2011, 08:03 AM
  3. Replies: 5
    Last Post: 06-28-2011, 06:40 PM
  4. Replies: 5
    Last Post: 06-22-2011, 08:47 PM
  5. Unbound textbox and report linked to vba
    By Shambler2 in forum Programming
    Replies: 7
    Last Post: 06-09-2011, 04:29 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