IIF in SQL Stroed Proceedure

  • Thread starter Thread starter Randy Shaw via AccessMonster.com
  • Start date Start date
R

Randy Shaw via AccessMonster.com

I want to conver the expression below from an Access MDB to use as an
expression in a SQL Stored Proceedure in an Access ADP and cannot seem to
get the syntax correct.

IIf([CheckBox1]=True,"Indiivindual Policy","Family Policy")
 
IIF() doesn't exist in T-SQL and must be replaced with the CASE statement.
Idem for the constant True. Also, strings are delimited with single quote '
, not double quote " . There is an option to override this but it's not a
recommended practice.

Finally, I'm not sure if CheckBox1 is the name of a control or of a column
and in what context you want to use that expression: in VBA code or in a SQL
Select statement?
 
I have a bound check box on my form and I want to use Select If statement
that will query the table and determine and return a vale in my query. Is
like an IIF expression

If [CkBox1] is checked ‘Check Box 1 is Checked’ or If [CkBox2] is checked
‘Check Box 2 is Checked’ ‘ELSE ‘No Boxes Checked’
 
I'm afraid to tell you that you don't work with ADP in the same way as with
Access. You will have to learn how to write stored procedures on the
SQL-Server.

You can also choose to build your Select dynamically and then set the
recordsource of your form to it, something like:

Dim stringSQL as String

If (Me.CheckBox1) then
stringSQL = "select * from MyTable where [Individual Policy] <> 0"
else
stringSQL = "select * from MyTable where [Family Policy] <> 0"
end if

Me.RecordSource = stringSQL

However, you'll still have to learn how to program SQL-Server if you want to
work with it in any case.
 
Back
Top