Is if() functionally equivalent to if, then, else?

General
1

I want to save a chosen date or a NULL value to the DB. I have a snippet of code that works:

if dcPolicyEffectiveDate.Checked then ps.Bind(5, dcPolicyEffectiveDate.DateValue.SQLDate) else ps.Bind(5, nil) end if

I’ve tried to replace that code with this:

ps.Bind(5, (if(dcPolicyEffectiveDate.Checked, dcPolicyEffectiveDate.DateValue.SQLDate, nil)))

However, the replacement code throws a compile time error… [quote]Type Mismatch Error. Expected String, but got Nil[/quote]

I was expecting the same result from either style. What am I missing?

[Update]

OK, I see the LR states if() must have a common return type. Should I cast the nil as string? How would I do that?

2

doesn’t seem you can cast NIL as a string… so you may just be stuck with your original code…

IF(cond,true_value,false_value) is similar to IF/THEN, but true_value and false_value MUST be the same datatype, and STRING datatype cannot be NIL… you might try “” (two double quotes)… but that will depend on what the BIND really needs

3

Thanks Dave. I tried the empty string “”, as well as str(nil), but both resulted in a date of "1/1/1900` being saved on the SQL Server. I’ll live with the original code - it saves the desired NULL value.

4

The If expression’s type is calculated based off of the common type between the two values. There is no common type between String and Nil, which is why the compiler complains.

The reason that using an empty string doesn’t work is because the database API treats Nil and an empty string differently, as you’ve noticed.

5

You could cast the string value:

ps.Bind(5, (If(dcPolicyEffectiveDate.Checked, CType(dcPolicyEffectiveDate.DateValue.SQLDate, Variant), Nil)))
6

Joe and Eli, thanks for the responses. I was just trying to condense some code, and it intrigued my why it wasn’t working. I’ll stick with the original working code for now, but I did learn something this morning.

7

Condense to the point clarity of purpose and readability is retained & no further :slight_smile:

The inline If, IMHO, doesn’t always lead to more readable code.

In some cases it can and in others it reduces it