Results 1 to 4 of 4

Thread: SQL Troubles

  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: SQL Troubles

    I have an access db with a form on it that is used to search records. One of the fields on this form is a drop-dwon menu with three options, which for the sake of generality we'll call "Option A", "Option B", and "Option C". These options reflect a field in the table that is being searched (each record needs to be one of these options).

    I was asked to add an option (call it "Option D") to the search form that would allow users to search for all records that have either "Option A" OR "Option B".

    So I tried using this syntax in the query builder.

    Like IIf([Forms]![Frm_Name]![Fld_On_Form] = "Option D", "Option A" OR "Option B", [Forms]![Frm_Name]![Fld_On_Form])

    This works if the users selects Options A, B, or C from the dropdown, but not D (the true part of the statement). Can anyone tell me why? Or more importantly, how to fix it? Thank you very much in advance.
    Me.Geek = True

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    When you use Like you need to put it in front of each criteria. I think something like this would work:

    Like IIf([Forms]![Frm_Name]![Fld_On_Form] = "Option D", "Option A" OR Like "Option B", [Forms]![Frm_Name]![Fld_On_Form])


    I just added the like before the Option B.

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Hey DC,

    Thanks for the help. Unfortunately, it didn't seem to change anything, I still get the same error when selecting Option D (see attached).

    If it helps, when I changed my criteria to what you suggested, then closed the criteria and reopened it, Access reformated it to look like:

    Like IIf([Forms]![Frm_Name]![Fld_On_Form] = "Option D", ([Qry_Name].[Fld_Name])="Option A" OR ([Qry_Name].[Fld_Name]) Like "Option B", [Forms]![Frm_Name]![Fld_On_Form])

    This still works for Options A-C, but Option D still does not work.

    Any other suggestions? Thank you again in advance to anyone who can help.
    Attached Thumbnails Attached Thumbnails err2.bmp  
    Me.Geek = True

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    By the way, I have also tried

    Like IIf([Forms]![Frm_Name]![Fld_On_Form] = "Option D", ([Qry_Name].[Fld_Name])="Option A" OR "Option B", [Forms]![Frm_Name]![Fld_On_Form])

    and Access reformats that to look like

    Like IIf([Forms]![Frm_Name]![Fld_On_Form] = "Option D", ([Qry_Name].[Fld_Name])="Option A" OR ([Qry_Name].[Fld_Name])="Option B", [Forms]![Frm_Name]![Fld_On_Form])

    Which gives me the same error again for Option D.

    Any suggestions?
    Me.Geek = True

Posting Permissions

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