Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieving multiple Yes/No records (2000/SR1)

    I keep a db of building inspections carried out and each individual building record has about 30 Y/N fields I tick off for broad areas which need follow-up. In order to recall these, I've created a form which has the 30 Y/N fields with the idea that if I want to report on buildings which need follow-up on eg tiles, carpets and window locks, I could tick those Y/N fields on the form, and the results would select those records which have the same Y/N fields selected.

    Unfortunately, I haven't had any success with the required query.

    Can anyone pls help?

    Many thanks.
    Nick

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving multiple Yes/No records (2000/SR1)

    If you want to compare all the 30 fields enter the following code in the on Open Event of the report::
    <pre>Me.Filter = "Me!yesnofield1 = Forms!Myform!checkbox1 And " & _
    "Me!yesnofield2 = Forms!Myform!checkbox2 And " & _
    "Me!yesnofield3 = Forms!Myform!checkbox3 And " & _
    'Repeat untill 29
    "Me!yesnofield30 = Forms!Myform!checkbox30"
    Me.FilterOn = True</pre>

    If you want to compare only the checked field enter the following code :
    <pre>dim strFilter as String
    StrFilter = ""
    If Forms!Myform!checkbox1 = True Then
    strFilter = strFilter & "Me!yesnofield1 = Forms!Myform!checkbox1 And "
    End If
    If Forms!Myform!checkbox2 = True Then
    strFilter = strFilter & "Me!yesnofield2 = Forms!Myform!checkbox2 And "
    End If
    If Forms!Myform!checkbox3 = True Then
    strFilter = strFilter & "Me!yesnofield3 = Forms!Myform!checkbox3 And "
    End If
    ' Repeat the If ... End It until 30
    strFilter = Left(strFilter,Len(strFilter - 5))
    Me.Filter = strFilter
    Me.FilterOn = True</pre>

    Replace Myform with the name of the form containing the checkboxes.
    Replace the YesNoFieldsX with the name of the fields of the table.
    Replace the CheckBoxX with the names of the checkboxes on the form.
    If your fields and your checkboxes have the same name with a numbering from 1 to 30, you could simplify this code with a for next loop.
    If you want help for the for next loop, post again.
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving multiple Yes/No records (2000/SR1)

    Thanks very much. This is making sense, I think.

    I would probably need an OR condition for each Y/N field. If I modify your first example code to use OR instead of &, I would retrieve the correct records. I suppose I ought ot test it.

    I'm not quite sure of the difference between the two samples of code. Could you shed some light on this pls?

    Also, in the second sample, what is the significance of the line:

    strFilter = Left(strFilter,Len(strFilter - 5)) ?

    Thanks again
    Nick

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving multiple Yes/No records (2000/SR1)

    In the first code you will compare every of the 30 checkbox's to the 30 yes/no Fields of the table.
    In the second code you will only compare the checkbox's that are checked. As you don't know witch one will be the lasted, you have to remove the " And " part at the end of the string.strFilter = Left(strFilter,Len(strFilter - 5)) remove the 2 space and 3 characters of at the end of the string.
    Using And or Or depends of the result you want.
    Francois

Posting Permissions

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