Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    compiling several fields (Access 97)

    I have a table with 26 yes/no fields for periods of equipment maintenance. The user will be able to check multiple periods. What the user wants is a list of all periods that maintenance is do. For example a piece of equipment is marked for maintenance due in periods 4, 8,12, 16, 20, and 24. The report should show 4, 8,12, 16, 20, 24. What is the best approach to this result?

    Thank you.
    Fay

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

    Re: compiling several fields (Access 97)

    Having 26 yes/no field is not an ideal design, but it might not be easy to change it.

    Let's say that these fields are named M1, M2, ..., M26 (I'm lazy). You can use the following expression in a query:

    Ms: IIf([M1], "1, ", "") & IIf([M2], "2, ", "") & ... & IIf([M26], "26, ", "")

    You can't afford to be lazy and will have to write out M3, M4 etc. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Instead, you can calculate the concatenation directly in the control source of a text box on the report:

    =IIf([M1], "1, ", "") & IIf([M2], "2, ", "") & ... & IIf([M26], "26, ", "")

    In both cases, the string will end in a comma and a space. If this bothers you, add another field too the query (it becomes too complicated to do this directly):

    MaintenancePeriods: IIf([Ms] = "", "", Left([Ms], Len([Ms])-2))

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: compiling several fields (Access 97)

    Thanks Hans. I got it to work this morning. I couldn't get it to work last night. But it worked great this mornig. Fay

    PS cute evil grin!

Posting Permissions

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