TWO iiF EXPRESSIONS

[Guest]

Help wwith this expression:
IIf([STATUS]="EXPIRED" OR "WITHDRAWN" OR "CANCELED",[LISTDATE]-[OFFMKT]

If [status]="expired" or "canceled" or "withdrawn" then [LISTDATE]-[OFFMKT]
If[STATUS]="PENDING" OR "SOLD" then [listdate]-[saledate]
if [status]="active" then [listdate]-date()

 

[Tom Ellison]

Dear Sierra:

LISTDATE -
IIf(STATUS = "EXPIRED" OR STATUS = "WITHDRAWN" OR STATUS = "CANCELLED",
OFFMKT, IIf(STATUS = "PENDING" OR STATUS = "SOLD", saledate, date()))

Tom Ellison

 

[Marshall Barton]

Help wwith this expression:
IIf([STATUS]="EXPIRED" OR "WITHDRAWN" OR "CANCELED",[LISTDATE]-[OFFMKT]

If [status]="expired" or "canceled" or "withdrawn" then [LISTDATE]-[OFFMKT]
If[STATUS]="PENDING" OR "SOLD" then [listdate]-[saledate]
if [status]="active" then [listdate]-date()

Try something more like:

IIf([status] IN("expired","canceled","withdrawn"),
LISTDATE - OFFMKT, IIf([status] IN("PENDING","SOLD"),
listdate - saledate, IIf([status]="active", listdate -
date(), 0)))

 

[david epsom dot com dot au]

or

SWITCH
([status] IN("expired","canceled","withdrawn")),LISTDATE - OFFMKT,
([status] IN("PENDING","SOLD")),listdate - saledate,
([status]="active"),listdate - date()

(david)

Help wwith this expression:
IIf([STATUS]="EXPIRED" OR "WITHDRAWN" OR "CANCELED",[LISTDATE]-[OFFMKT]

If [status]="expired" or "canceled" or "withdrawn" then
[LISTDATE]-[OFFMKT]
If[STATUS]="PENDING" OR "SOLD" then [listdate]-[saledate]
if [status]="active" then [listdate]-date()

Try something more like:

IIf([status] IN("expired","canceled","withdrawn"),
LISTDATE - OFFMKT, IIf([status] IN("PENDING","SOLD"),
listdate - saledate, IIf([status]="active", listdate -
date(), 0)))

 

[Tom Ellison]

I definitely prefer Marsh's use of IN(). I was trying to keep it more like
the original to be, perhaps, less confusing. But I would certainly write my
own code this way.

Tom Ellison

Help wwith this expression:
IIf([STATUS]="EXPIRED" OR "WITHDRAWN" OR "CANCELED",[LISTDATE]-[OFFMKT]

If [status]="expired" or "canceled" or "withdrawn" then
[LISTDATE]-[OFFMKT]
If[STATUS]="PENDING" OR "SOLD" then [listdate]-[saledate]
if [status]="active" then [listdate]-date()

Try something more like:

IIf([status] IN("expired","canceled","withdrawn"),
LISTDATE - OFFMKT, IIf([status] IN("PENDING","SOLD"),
listdate - saledate, IIf([status]="active", listdate -
date(), 0)))

 

[Gary Walter]

IN is good.

How many times have you wished IN
was also a VB operator that you could use
in your VB code with If/Then/Else code?

No reply is necessary...
just that the other day I wrote an If/Then/Else
using IN w/o thinking...

gary

I definitely prefer Marsh's use of IN(). I was trying to keep it more like
the original to be, perhaps, less confusing. But I would certainly write
my own code this way.

Tom Ellison

Help wwith this expression:
IIf([STATUS]="EXPIRED" OR "WITHDRAWN" OR "CANCELED",[LISTDATE]-[OFFMKT]

If [status]="expired" or "canceled" or "withdrawn" then
[LISTDATE]-[OFFMKT]
If[STATUS]="PENDING" OR "SOLD" then [listdate]-[saledate]
if [status]="active" then [listdate]-date()

Try something more like:

IIf([status] IN("expired","canceled","withdrawn"),
LISTDATE - OFFMKT, IIf([status] IN("PENDING","SOLD"),
listdate - saledate, IIf([status]="active", listdate -
date(), 0)))

 

[John Spencer]

I've found that in the case that I need to use IN in VBA that a CASE
statement will often satisify the need.

If A=5 or A=8 or A=12 then

End If

Can also be written as follows and if I need to add an additional matching
value, it is one comma and the additional value.

SELECT Case A
Case 5,8,12

END SELECT

IN is good.

How many times have you wished IN
was also a VB operator that you could use
in your VB code with If/Then/Else code?

No reply is necessary...
just that the other day I wrote an If/Then/Else
using IN w/o thinking...

gary

I definitely prefer Marsh's use of IN(). I was trying to keep it more
like the original to be, perhaps, less confusing. But I would certainly
write my own code this way.

Tom Ellison

sierralightfoot wrote:

Help wwith this expression:
IIf([STATUS]="EXPIRED" OR "WITHDRAWN" OR "CANCELED",[LISTDATE]-[OFFMKT]

If [status]="expired" or "canceled" or "withdrawn" then
[LISTDATE]-[OFFMKT]
If[STATUS]="PENDING" OR "SOLD" then [listdate]-[saledate]
if [status]="active" then [listdate]-date()


Try something more like:

IIf([status] IN("expired","canceled","withdrawn"),
LISTDATE - OFFMKT, IIf([status] IN("PENDING","SOLD"),
listdate - saledate, IIf([status]="active", listdate -
date(), 0)))

 

[Marshall Barton]

Actually, I prefer David's combination of Switch and IN ;-)
--
Marsh
MVP [MS Access]

I definitely prefer Marsh's use of IN(). I was trying to keep it more like
the original to be, perhaps, less confusing. But I would certainly write my
own code this way.

Help wwith this expression:
IIf([STATUS]="EXPIRED" OR "WITHDRAWN" OR "CANCELED",[LISTDATE]-[OFFMKT]

If [status]="expired" or "canceled" or "withdrawn" then
[LISTDATE]-[OFFMKT]
If[STATUS]="PENDING" OR "SOLD" then [listdate]-[saledate]
if [status]="active" then [listdate]-date()

Try something more like:

IIf([status] IN("expired","canceled","withdrawn"),
LISTDATE - OFFMKT, IIf([status] IN("PENDING","SOLD"),
listdate - saledate, IIf([status]="active", listdate -
date(), 0)))

 

[Marshall Barton]

IN is good.

How many times have you wished IN
was also a VB operator that you could use
in your VB code with If/Then/Else code?

No reply is necessary...
just that the other day I wrote an If/Then/Else
using IN w/o thinking...

You can get to IN using VBA by hiding it within Eval. It's
usually too messy to bother with because of the
concatenations and quoted quotes, but it can be done.

 

[Guest]

How many times have you wished IN

was also a VB operator that you could use

:~} Never.

The syntax is a little different, but Select Case provides the same
functionality as the In operator:

Select Case j$: Case "a", "b", "c"
MsgBox "hello"
End Select

If eval(j$ & " In " & "('x','y','z')") then
MsgBox "hello"
End If

Furthermore, unlike languages where a Case statement is a simple Jump Table,
in BASIC the case 'labels' are expressions which are evaluated at runtime,
allowing you to simply write set expressions of enormous complexity. This is
a worthy trade off for the slightly different features of a simple In
operator:

select case True
case j$ like "[abcd][1234]"
...
case j$ like "fred*"
...
case 0 <> instr(j$,"abcd"), 0 <> instr(j$,sInString)
...
case 0 <> instr("a",j$)
...
case len(j$) > 5
...
case eval(j$ & " In " & "('x','y','z')")
...
case else
select case j$
case "1"
...
case str(MyFunc(j$) or MyArray(val(j$)))
...
case eval(j$)
...
end select
end select

(david)

IN is good.

How many times have you wished IN
was also a VB operator that you could use
in your VB code with If/Then/Else code?

No reply is necessary...
just that the other day I wrote an If/Then/Else
using IN w/o thinking...

gary

I definitely prefer Marsh's use of IN(). I was trying to keep it more like
the original to be, perhaps, less confusing. But I would certainly write
my own code this way.

Tom Ellison

sierralightfoot wrote:

Help wwith this expression:
IIf([STATUS]="EXPIRED" OR "WITHDRAWN" OR "CANCELED",[LISTDATE]-[OFFMKT]

If [status]="expired" or "canceled" or "withdrawn" then
[LISTDATE]-[OFFMKT]
If[STATUS]="PENDING" OR "SOLD" then [listdate]-[saledate]
if [status]="active" then [listdate]-date()


Try something more like:

IIf([status] IN("expired","canceled","withdrawn"),
LISTDATE - OFFMKT, IIf([status] IN("PENDING","SOLD"),
listdate - saledate, IIf([status]="active", listdate -
date(), 0)))

 

[Gary Walter]

CASE is good.

I don't recall seeing CASE examples illustrated
as well as you have here.

I work in the book world where titles/authors
can have any number of quotes, so shy away
from eval (as expressed here, why make it more
complicated when CASE is so powerful and simple).

I guess I'm getting old though. I just finish
some Transact-SQL stuff where the expression
after the IF can be something like

IF @p NOT IN (SELECT x FROM y WHERE z=0)

and I have a "senior moment" in VB.

How many times have you wished IN
was also a VB operator that you could use

:~} Never.

The syntax is a little different, but Select Case provides the same
functionality as the In operator:

Select Case j$: Case "a", "b", "c"
MsgBox "hello"
End Select

If eval(j$ & " In " & "('x','y','z')") then
MsgBox "hello"
End If

Furthermore, unlike languages where a Case statement is a simple Jump
Table,
in BASIC the case 'labels' are expressions which are evaluated at runtime,
allowing you to simply write set expressions of enormous complexity. This
is
a worthy trade off for the slightly different features of a simple In
operator:

select case True
case j$ like "[abcd][1234]"
...
case j$ like "fred*"
...
case 0 <> instr(j$,"abcd"), 0 <> instr(j$,sInString)
...
case 0 <> instr("a",j$)
...
case len(j$) > 5
...
case eval(j$ & " In " & "('x','y','z')")
...
case else
select case j$
case "1"
...
case str(MyFunc(j$) or MyArray(val(j$)))
...
case eval(j$)
...
end select
end select

(david)

IN is good.

How many times have you wished IN
was also a VB operator that you could use
in your VB code with If/Then/Else code?

No reply is necessary...
just that the other day I wrote an If/Then/Else
using IN w/o thinking...

gary

I definitely prefer Marsh's use of IN(). I was trying to keep it more like
the original to be, perhaps, less confusing. But I would certainly
write
my own code this way.

Tom Ellison


sierralightfoot wrote:

Help wwith this expression:
IIf([STATUS]="EXPIRED" OR "WITHDRAWN" OR
"CANCELED",[LISTDATE]-[OFFMKT]

If [status]="expired" or "canceled" or "withdrawn" then
[LISTDATE]-[OFFMKT]
If[STATUS]="PENDING" OR "SOLD" then [listdate]-[saledate]
if [status]="active" then [listdate]-date()


Try something more like:

IIf([status] IN("expired","canceled","withdrawn"),
LISTDATE - OFFMKT, IIf([status] IN("PENDING","SOLD"),
listdate - saledate, IIf([status]="active", listdate -
date(), 0)))

 

[david epsom dot com dot au]

'SELECT x FROM y' would be a worthy language extension.

Array's could be replaced with a native dictionary
structure (not an 'object' - I want implicit instantiation,
like DIM s AS STRING), and the DATA statement should
of course be brought back.

(david)

CASE is good.

I don't recall seeing CASE examples illustrated
as well as you have here.

I work in the book world where titles/authors
can have any number of quotes, so shy away
from eval (as expressed here, why make it more
complicated when CASE is so powerful and simple).

I guess I'm getting old though. I just finish
some Transact-SQL stuff where the expression
after the IF can be something like

IF @p NOT IN (SELECT x FROM y WHERE z=0)

and I have a "senior moment" in VB.

How many times have you wished IN
was also a VB operator that you could use

:~} Never.

The syntax is a little different, but Select Case provides the same
functionality as the In operator:

Select Case j$: Case "a", "b", "c"
MsgBox "hello"
End Select

If eval(j$ & " In " & "('x','y','z')") then
MsgBox "hello"
End If

Furthermore, unlike languages where a Case statement is a simple Jump
Table,
in BASIC the case 'labels' are expressions which are evaluated at
runtime,
allowing you to simply write set expressions of enormous complexity. This
is
a worthy trade off for the slightly different features of a simple In
operator:

select case True
case j$ like "[abcd][1234]"
...
case j$ like "fred*"
...
case 0 <> instr(j$,"abcd"), 0 <> instr(j$,sInString)
...
case 0 <> instr("a",j$)
...
case len(j$) > 5
...
case eval(j$ & " In " & "('x','y','z')")
...
case else
select case j$
case "1"
...
case str(MyFunc(j$) or MyArray(val(j$)))
...
case eval(j$)
...
end select
end select

(david)

IN is good.

How many times have you wished IN
was also a VB operator that you could use
in your VB code with If/Then/Else code?

No reply is necessary...
just that the other day I wrote an If/Then/Else
using IN w/o thinking...

gary

I definitely prefer Marsh's use of IN(). I was trying to keep it more like
the original to be, perhaps, less confusing. But I would certainly
write
my own code this way.

Tom Ellison


sierralightfoot wrote:

Help wwith this expression:
IIf([STATUS]="EXPIRED" OR "WITHDRAWN" OR
"CANCELED",[LISTDATE]-[OFFMKT]

If [status]="expired" or "canceled" or "withdrawn" then
[LISTDATE]-[OFFMKT]
If[STATUS]="PENDING" OR "SOLD" then [listdate]-[saledate]
if [status]="active" then [listdate]-date()


Try something more like:

IIf([status] IN("expired","canceled","withdrawn"),
LISTDATE - OFFMKT, IIf([status] IN("PENDING","SOLD"),
listdate - saledate, IIf([status]="active", listdate -
date(), 0)))