Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Unanswered: If..Then in a list box query

    Hi, newbie here and I have a question.

    There're 4 options in an option group. For option 1,2 and 3 I want to use Query1 as the row source of the list box. For option 4, it'll use Query2.
    So I type in the following in the SQL query builder, but Access says it is invalid:

    If (Forms![Form1]![OptionGroupName]=4) then
    Select [Query2].*
    From [Query2];
    else
    Select [Query1].*
    From [Query1];
    end if

    What's the proper way to use If....then conditon in SQL statments?

  2. #2
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Arrow

    Try removing the brackets around Form1, and replacing the following exclamation sign with a dot, like this:

    Code:
    If (Forms!Form1.[OptionGroupName]=4) then
    Select [Query2].*
    From [Query2];
    else
    Select [Query1].*
    From [Query1];
    end if
    D.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  3. #3
    Join Date
    Jun 2004
    Location
    Saratoga Springs
    Posts
    24
    Another solution is to use the immediate if operator "IIF". This is a good solution for generating if, then, else functionality inside a query. It works the same way as a case statement.
    Jon Jaussi, OCP DBA
    D4L Data Managent Solutions
    D4L_solutions@comcast.net

  4. #4
    Join Date
    Jul 2004
    Posts
    5
    It still give me a message that says:
    "Invalid SQL statment; Expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'.

    ??

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Just a thought, try

    Code:
    If (Forms![Form1]![OptionGroupName]=4) then
    ListBox1.rowSource= ”Select * From Query2”
    else
    ListBox1.rowSource= ”Select * From Query1”
    end if
    MTB

Posting Permissions

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