Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sort by calculated field (2003)

    I have used Access off and on during the past couple years, and am not really too familiar with it. I have created a form which contains 4 regular fields, and 2 calculated fields. The users would like to be able to sort by one of the calculated fields at times, but at times sort by other fields. Can anyone tell me how I can let them sort sometimes by the calculatef field?

    Thanks very much.

    Sue

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

    Re: sort by calculated field (2003)

    Are the calculated fields computed in a query that acts as record source for the form, or are they computed in the control source of text boxes on the form? If they are computed in a query, it should be possible to sort on them just like on the regular fields.

  3. #3
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort by calculated field (2003)

    Hans,

    I computed the fields in the control source of text boxes on the form. I'm not sure how to compute them in a query.

    Thanks,

    Sue

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

    Re: sort by calculated field (2003)

    You can't sort on a calculated control.
    The text boxes will have a formula as control source, let's say
    <code>
    =[Quantity]*[UnitPrice]
    </code>
    If the record source of the form is a table:
    - Create a query in design view.
    - Add the table to the query.
    - Add the asterisk * (first item in the field list) to the query grid.

    If the record source of the form is a query:
    - Open the query in design view.

    In both cases, continue as follows:
    - Go to the first empty column in the query grid.
    - Type the name you want for the calculated field, followed by a colon and a space.
    - Then type the formula without the initial =. For example:
    <code>
    Amount: [Quantity]*[UnitPrice]
    </code>
    - Repeat for each calculated field.
    - Test the query by switching to datasheet view.
    - If there are errors, switch back to design view and correct them.
    - Close and save the query.

    Next, modify the form:
    - Open the form in design view.
    - If the record source is currently a table, change it to the query you created.
    - (If the record source is already a query, leave it as it is)
    - Change the control source of the calculated text boxes to the name of the appropriate field from the column.
    - In the example I used above, you'd change <code>=[Quantity]*[UnitPrice]</code> to <code>Amount</code>
    - Save the form.
    - You should now be able to sort on the calculated fields.

  5. #5
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort by calculated field (2003)

    Hans,

    It worked perfectly. Thank you very much for taking the time to explain clearly the steps involved.

    Sue

Posting Permissions

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