Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Wigan, Lancashire, England
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number records in query (A2000 SR-1)

    I want to be able to number records in a query, rather than a form or a report, so that I can use the query as basis for subsequent queries. Clues would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number records in query (A2000 SR-1)

    If your query is sorted by a single field that has unique values, you can create the record number as a calculated field in the query. Let's say the query is based on tblSource and that it is sorted by UniqueID.

    RecNo: DCount("UniqueID", "tblSource", "UniqueID<=" & [UniqueID])

    If your table has many records, this will slow down execution of the query considerably.

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Location
    Wigan, Lancashire, England
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number records in query (A2000 SR-1)

    Hi

    query is sorted by multiple fields, Date & event number (on that date), and I want to number the first record as 1, second as 2 etc. Had tried Dcount but can only get the total number of records in the record set

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number records in query (A2000 SR-1)

    If your query is sorted on more than one field, it gets more complicated.

    Try

    RecNo: Val(DCount("*","tblSource","[Date]<#" & Format([Date],"m/d/yyyy") & "# Or ([Date]=#" & Format([Date],"m/d/yyyy") & "# And [Event number]<=" & [Event number] & ")"))

    Explanation:
    <UL><LI>DCount has 3 arguments:
    1. <LI>The field on which to count. In this case, I use "*".
      <LI>The recordset. Replace tblSource by the name of your table.
      <LI>(Optional) a WHERE condition. See the next bullet.
    <LI>To get the record number, we use the WHERE condition to count:
    1. <LI>The number of records with date before the date of the current record, plus
      <LI>The number of records with date equal to the date of the current record AND event number less than or equal to the event number of the current record.
    <LI>Dates must be enclosed in # symbols. If all your users have US date format in their Regional Settings, you can use [Date] instead of Format([Date], "m/d/yyyy") twice. But if there is a chance that some of your users have a custom date format, you MUST use the Format function to convert the date to US format.
    <LI>Val(DCount(...)) is used to return a numeric value.
    <LI>If you have null (empty) values in either the Date or Event number field,, you'll probably need to add Nz's.[/list]HTH, Hans

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Location
    Wigan, Lancashire, England
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number records in query (A2000 SR-1)

    "Thank you" seems insufficient but "thank you" nevertheless.

    Perfect, just what I wanted.

Posting Permissions

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