Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post
    I have a table wherein each record has six fields "A, B, C, ..." that are populated from another table containing a couple dozen entries. I need to create a select query that returns records that have a specific entry in any one of the six. For example, if any of the six fields in a record contains the word "book". I suppose I could use a very long condition like "IF A = "book" OR B = "book" OR C= "book" .... etc and pass "book" as a parameter, but is there a better way?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Perhaps a different table structure would have been better, but with the present design, you do need six separate conditions:
    [attachment=84488:x.png]
    You could use a reference to a text box on a form instead of the parameter [Enter Value].
    Attached Images Attached Images
    • File Type: png x.png (2.2 KB, 0 views)

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks Hans, I'll give it a try.

  4. #4
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post
    I have a combo box on a form from which I select the entry that I want the query to find. In the query conditions how do I refer to the item that's been selected in the combo box on the form? I've tried Form!frmName.cboSelect where cboSelect is the combo box on the form frmName. But this doesn't seem to work.

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

    [Forms]![frmName]![cboSelect]

    Note the use of Forms instead of Form, and the use of ! instead of . between the name of the form and the name of the combo box.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='Chuck Tucker' post='782223' date='29-Jun-2009 10:22']I have a combo box on a form from which I select the entry that I want the query to find. In the query conditions how do I refer to the item that's been selected in the combo box on the form? I've tried Form!frmName.cboSelect where cboSelect is the combo box on the form frmName. But this doesn't seem to work.[/quote]

    Try Forms!frmName.cboSelect
    Regards
    John



  7. #7
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post
    I think I outsmarted myself. The term returned by the select CBO is actually the ID from the table with various entries. So how do I get the actual entry rather than the ID number?

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='Chuck Tucker' post='782229' date='29-Jun-2009 11:00']That still doesn't work. After selecting an item in the combo box do I need to do something like "Refresh" to set the value prior to running the query?[/quote]

    No you don't need a refresh. It should work straight away.
    Does it give an error? or just not return any results?

    Initially try it with just one field, where you are sure there is matching data, then when it works, add it to the other fields.

    One possibility is that the combo box has a hidden first column holding a numerical key. How many columns does the combo box have?
    Regards
    John



  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='Chuck Tucker' post='782231' date='29-Jun-2009 11:07']I think I outsmarted myself. The term returned by the select CBO is actually the ID from the table with various entries. So how do I get the actual entry rather than the ID number?[/quote]
    I see that my 'possibility' is what happened.
    Change the row source query of the combo to exclude the ID field, reduce the number of columns to 1, and remove the 0cm in the column widths.
    Regards
    John



  10. #10
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post
    I'm still rather confused. Here's the setup.

    I have a form called frmMainMenu on which I have a combo box named cboGroup. It selects and displays a field from one column of a table called Group. As a test I set up another form (called from the first) that has a text box bound to Forms!frmMainMenu.cboGroup. After selecting a record in the combo Box I go to the test form and it displays "=Name?" rather than the contents of the main menu combo box. Where am I going wrong here?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Set the control source of the text box to

    =[Forms]![frmMainMenu]![cboGroup]

    If that still doesn't work, could you attach a stripped down and zipped copy of your database to a reply?

  12. #12
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Hans, that was it. I forgot the = sign.

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='Chuck Tucker' post='782260' date='29-Jun-2009 16:33']Hi Hans, that was it. I forgot the = sign.[/quote]

    The most reliable way to do things like this is to use the Expression Builder. It gets the syntax right for you.
    Right click in the criteria line, and choose Build. Navigate to the correct form, then to the right control on the form.
    [attachment=84497:Builder.gif]
    Attached Images Attached Images
    Regards
    John



  14. #14
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks, I'll try to remember that in the future.

Posting Permissions

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