Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    1

    Question Unanswered: assign query position to field

    Hi everyone, hopefully someone can help...

    The update query, qryRaceClassPosition, needs to sort the table, tblResult, into ascending order by RaceTimeCorrected (a time) and assign the position in the list to RaceClassPosition... how would i do this?

    But there's another thing to account for... some records in tblResult have a RaceTimeCorrected of 00:00:00 and a RaceStatus of DNF - they need to be placed penultimate equal, and some records have a RaceTimeCorrected of 00:00:00 and a RaceStatus of DNC - they need to be placed last equal.

    Any help would be greatly appreciated, thanks,
    Mat

  2. #2
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: assign query position to field

    Originally posted by mat334
    Hi everyone, hopefully someone can help...

    The update query, qryRaceClassPosition, needs to sort the table, tblResult, into ascending order by RaceTimeCorrected (a time) and assign the position in the list to RaceClassPosition... how would i do this?

    But there's another thing to account for... some records in tblResult have a RaceTimeCorrected of 00:00:00 and a RaceStatus of DNF - they need to be placed penultimate equal, and some records have a RaceTimeCorrected of 00:00:00 and a RaceStatus of DNC - they need to be placed last equal.

    Any help would be greatly appreciated, thanks,
    Mat
    I am not familiar with the race terminology. But it seems that you are attempting to order a list of mixed values. The key would be to get all the records to have one field that represents their place so they can be sorted. To place someone first give him/her a value of 0. Place someone last a value of 99999.

    Query:
    ORDERFIELD: iif(RaceStatus = DNF or RaceStatus = DNC,99999,iif(RaceTimeCorrected = “00:00:00”,0, RaceTimeCorrected)

    You need to step through the records in order to give them an actually position number.

    Function RacePosition()
    Dim Db as Database, Rs as recordset, Running%, place%, oldTime%
    Set Db = currentDB()
    Set Rs = Db.OpenRecordset(“MyQuerySortedOnORDERFIELD”)
    Do until Rs.eof
    Rs.edit
    Running% = Running% + 1
    If Rs.ORDERFIELD > oldTime then
    place% = Running%
    end if
    Rs!position = Place%
    Oldtime = Rs.ORDERFIELD
    Rs.update
    Rs.movenext
    Loop
    Rs.close
    Db.close
    Set rs = nothing
    Set db = nothing
    End Function

Posting Permissions

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