CODEWITHSUNDEEP
sqlms-accesscase
Zack
Zack

Reputation: 121

Using Case Select in a Select SQL Query

SELECT
  CASE Forms!FormName!ComboBox
   WHEN  Is Not Null THEN (ParentTable.Column)
     FROM (Parent)
      WHERE (((ParentTable.Column)=Forms!FormName!ComboBox))
  ELSE
   ((ParentTable.Column) FROM Parent END;

Okay, I have a form that displays records from the parent table and I want to be able to filter it. So what the SQL code above is trying to accomplish is to say that if there is a selection made in the combo box then select only the records that match that field. The else case is that the field is null and it just selects all records. I am not very familiar with SQL code so please explain different parameters used if possible.

Or is it possible that I could use some sort of if statement instead?

Upvotes: 2

Views: 1589

Answers (3)

Andriy M
Andriy M

Reputation: 77707

Seems like CASE is not needed here:

SELECT ParentTable.Column
FROM ParentTable
WHERE (Forms!FormName!ComboBox IS NULL)
   OR (Forms!FormName!ComboBox = ParentTable.Column)

Upvotes: 1

Chandu
Chandu

Reputation: 82933

NOTE: I assume you are binding the value of Forms!FormName!ComboBox parameter dynamically.

For MS Access

   SELECT <YOUR_SELECT_LIST>
     FROM ParentTable 
    WHERE NZ(Forms!FormName!ComboBox,  ParentTable.Column) = ParentTable.Column

For SQL Server

   SELECT <YOUR_SELECT_LIST>
     FROM ParentTable 
    WHERE ISNULL(Forms!FormName!ComboBox,  ParentTable.Column) = ParentTable.Column

For ORACLE

   SELECT <YOUR_SELECT_LIST>
     FROM ParentTable 
    WHERE NVL(Forms!FormName!ComboBox,  ParentTable.Column) = ParentTable.Column

Upvotes: 0

Milimetric
Milimetric

Reputation: 13549

You can't use your form parameters directly in SQL. You can parameterize your query and pass in a parameter, let's call it @FormName:

select
    case
         when @FormName is not null then ParentTable.column1
         else ParentTable.column2
    end
from ParentTable
where ParentTable.column3 = @FormName

Upvotes: 1

Related Questions