Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Incrementing Expression Field in Query (Access 2002 SP-2)

    Occasionally, I'd like to add a field to a query that results in a sort of record index value in the query result. That is the result of the query might look something like:
    <font face="Georgia">
    1 George
    2 Mary
    3 Ralph
    4 Sally

    etc.
    </font face=georgia>
    where the 1,2,3,4,... doesn't come from a source table, but rather from a calculation; something like Index:Index+1 (which doesn't work, but hopefully you get the idea).

    Can this be done?

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

    Re: Incrementing Expression Field in Query (Access 2002 SP-2)

    Users should never see a query directly. If you want to do this in a form, see HOW TO: Display Line Numbers on Subform Records in Access 2002.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Expression Field in Query (Access 2002 SP-2)

    Hans,

    I agree. In this case, I'm using this just for my day-to-day "use-Access-as-a-tool-to-do-my-job" rather than building "finished" applications. Sometimes I 'd just like to number the records to be shown on a form or report or just added to a table or query result. My workaround has been to make a temporary table with the query results and a new index field, then use VBA code to step through the table records (in the desired order) and increment this new field (this seems to be similar to the approach used in the link you provided). I was just hoping there might be a way to build this into the Make Table query and avoid the stepwise VBA routine.

    Certainly, if the end result is a report, the incrementing could be done independent of the source table/query using a calculated field or just a label and assigning it in the format or print event routine. And your link shows how to do it (using stepwise VBA) for forms.

    Thanks.

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

    Re: Incrementing Expression Field in Query (Access 2002 SP-2)

    In a report, you don't need these tricks at all. You can easily number records in a report, using a text box with Control Source set to =1 and Running sum set to Over All or Over Groups, depending on the desired scope of the numbering.

    But in a query, there is no "natural" way to create a record number.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incrementing Expression Field in Query (Access 2002 SP-2)

    Good idea (as usual!) for the report record number. Thanks, Hans. I guess there's some consolation in the fact that I apparently wasn't overlooking some "no brainer" solution for the query problem...

Posting Permissions

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