[SOLVED] How can I use a case statement with a Drop Box? Access Database

How can I use a case statement with a Drop Box? Access Database

I have a drop box with various items when the value is set to null I want to select multiple items using the control parameter to fill the data source like below. This Select Command will not work can anyone help me to see what I'm doing in error? EDIT THE CODE BELOW IS NOW CORRECT AND FUNCTIONS.....

<asp:DropDownList ID="FilterList1" runat="server">

                    <asp:ListItem  Selected="True" Value=" ">Produce</asp:ListItem>
                            <asp:ListItem>Fruits</asp:ListItem>
                            <asp:ListItem>Vegetables</asp:ListItem>
                            <asp:ListItem>Milk</asp:ListItem>
                            <asp:ListItem>Pop</asp:ListItem>

</asp:DropDownList>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" 
    DataFile="~/AFOLDER/db1.mdb" 
    SelectCommand="SELECT * FROM [Table1] WHERE IIF( (@Food1 ='  '), [Food] In ('Fruit','Vegetable'), (Food =@Food1) )">
    <SelectParameters>
      <asp:ControlParameter ControlID="FilterList1" Name="Food1" 
            PropertyName="SelectedValue" Type="String" />
    </SelectParameters>
</asp:AccessDataSource>

Solution

As you are using AccessDataSource1, then try Access SQL IIF() Statement, something like:

SelectCommand="SELECT * FROM [Table1] WHERE (Service =@Service1) OR IIF((@Service1 ='  '), IIF( [Service] In ('In Service','Out of Service'), True, False), False)

Also, make sure you are using correct syntax @Service1 =' ' (may be @Service1 ='' ?)

Hope this will help.

Regards