A97... not important, just curious (2 if's or select case)

[MLH]

Suppose the following...
Dim A as Date
A=#7/24/2005#

I wish to compare value of A against 2 other values:
1) 8/1/2005
2) 9/1/2005

Which is better and why...
First:
If A < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
End If
If A < #9/1/2005# Then
MsgBox "Date is earlier than 9/1/2005"
Exit Sub
End If

Or Second:
Select Case A
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
Case Is < #9/1/2005#
MsgBox "Date is earlier than 9/1/2005"
End Select
Exit Sub

[Rick Brandt]

MLH wrote:

Suppose the following...
Dim A as Date
A=#7/24/2005#

I wish to compare value of A against 2 other values:
1) 8/1/2005
2) 9/1/2005

Which is better and why...
First:
If A < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
End If
If A < #9/1/2005# Then
MsgBox "Date is earlier than 9/1/2005"
Exit Sub
End If

Or Second:
Select Case A
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
Case Is < #9/1/2005#
MsgBox "Date is earlier than 9/1/2005"
End Select
Exit Sub

Technically Case is more efficient in this case because it only has to evaluate
the value of A one time. Each If block is evaluating A separately. Not a big
deal when just using a variable as you are, but consider the following example.

If (some big long complex expression) < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
End If

If I had several If-Then blocks in a row then it becomes more obvious why the
Case statement would be more efficient. It would only have to evaluate the
expression once and then simply compare the result in each Case line. A series
of If-Thens would have to evaluate the expression over and over.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[MLH]

Sorry, Rick. It was a subtle point. Notice the Exit Sub buried in each
If statement. That prevents further processing on the FIRST occurrence
of a True comparison. So, if there were 4 comparisons being made and
for argument's sake, the last one evaluating to True and the others
False, wouldn't both the chain of If's and the sequence of Select's
each have 4 complete comparison's to make?

- OR -

As in my example where BOTH comparisons evaluate to True, don't
both approaches require only 1 comparison to be made? It seems
the Select stops on the first one that's True (even though the next
one would evaluate to True if it were to be evaluated).

Technically Case is more efficient in this case because it only has to evaluate
the value of A one time. Each If block is evaluating A separately. Not a big
deal when just using a variable as you are, but consider the following example.

If (some big long complex expression) < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
End If

If I had several If-Then blocks in a row then it becomes more obvious why the
Case statement would be more efficient. It would only have to evaluate the
expression once and then simply compare the result in each Case line. A series
of If-Thens would have to evaluate the expression over and over.

[Rick Brandt]

MLH wrote:

Sorry, Rick. It was a subtle point. Notice the Exit Sub buried in each
If statement. That prevents further processing on the FIRST occurrence
of a True comparison. So, if there were 4 comparisons being made and
for argument's sake, the last one evaluating to True and the others
False, wouldn't both the chain of If's and the sequence of Select's
each have 4 complete comparison's to make?

I was not talking about the comparisons. If the last test is true then both are
performing the same number of tests. I was talking about evaluating the value
that is being tested against. If you look at my previous post again this is the
(some big long complex expression) that I am talking about. If I was doing that
in a series of If-Then blocks the (some big long complex expression) is
evaluated on the first line of ALL of the If-Then blocks but is only evaluated
once in a Select Case block.

Yes, this would not make a difference if the first test is satisfied and it
likely would not make any difference perceptable to the user if the last test
were satisfied. All of this is "best practice theory" where the differences are
going to be measured in microseconds. There are curcumstances though where it
could make a difference (a looping operation for example) so it is still good to
know what the best procedures to use are.

As an aside having multiple exit points in a procedure is considered poor form
anyway so I would not have an Exit Sub in each If-Then block as in your example.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:fk********************************@4ax.com:

Suppose the following...
Dim A as Date
A=#7/24/2005#

I wish to compare value of A against 2 other values:
1) 8/1/2005
2) 9/1/2005

Which is better and why...
First:
If A < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
End If
If A < #9/1/2005# Then
MsgBox "Date is earlier than 9/1/2005"
Exit Sub
End If

Or Second:
Select Case A
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
Case Is < #9/1/2005#
MsgBox "Date is earlier than 9/1/2005"
End Select
Exit Sub

I prefer not to use CASE SELECT with anything but absolutely
exclusive criteria. You are depending on the order of the CASE
statements to get a correct result. If you re-ordered it as:

Select Case A
Case Is < #9/1/2005#
MsgBox "Date is earlier than 9/1/2005"
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
End Select

You'd get incorrect results.

That's why I'd want to make it be something like this:

Select Case A
Case Is >= #8/1/2005# And (A < #9/1/2005#)
MsgBox "Date is earlier than 9/1/2005"
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
End Select

Now, you see that is bad, because it means you're doing two
different kinds of tests. What you'd like to do is:

Case Is >= #8/1/2005# And Is < #9/1/2005#

but that isn't valid code.

I think it's simpler to code this as a single If/Then/Else
statement:

If A < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
ElseIf A < #9/1/2005# Then
MsgBox "Date is earlier than 9/1/2005"
Exit Sub
End If

Maybe you didn't know about ElseIf? It's certainly not new in Access
97.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Chuck Grimsby]

On Sun, 24 Jul 2005 15:34:50 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

MLH <CR**@NorthState.net> wrote in
news:fk********************************@4ax.com :

Suppose the following...
Dim A as Date
A=#7/24/2005#

I wish to compare value of A against 2 other values:
1) 8/1/2005
2) 9/1/2005

Which is better and why...
First:
If A < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
End If
If A < #9/1/2005# Then
MsgBox "Date is earlier than 9/1/2005"
Exit Sub
End If
Or Second:
Select Case A
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
Case Is < #9/1/2005#
MsgBox "Date is earlier than 9/1/2005"
End Select
Exit Sub

I prefer not to use CASE SELECT with anything but absolutely
exclusive criteria. You are depending on the order of the CASE
statements to get a correct result. If you re-ordered it as:
Select Case A
Case Is < #9/1/2005#
MsgBox "Date is earlier than 9/1/2005"
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
End Select
You'd get incorrect results.
That's why I'd want to make it be something like this:
Select Case A
Case Is >= #8/1/2005# And (A < #9/1/2005#)
MsgBox "Date is earlier than 9/1/2005"
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
End Select
Now, you see that is bad, because it means you're doing two
different kinds of tests. What you'd like to do is:
Case Is >= #8/1/2005# And Is < #9/1/2005#
but that isn't valid code.
I think it's simpler to code this as a single If/Then/Else
statement:
If A < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
ElseIf A < #9/1/2005# Then
MsgBox "Date is earlier than 9/1/2005"
Exit Sub
End If
Maybe you didn't know about ElseIf? It's certainly not new in Access
97.

Select Case True
Case A >= #8/1/2005# And A <= #9/30/2005#
Debug.Print "Yes"
Case Else
Debug.Print "No"
End Select

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[David W. Fenton]

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in
news:7g********************************@4ax.com:

On Sun, 24 Jul 2005 15:34:50 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

MLH <CR**@NorthState.net> wrote in
news:fk********************************@4ax.co m:

Suppose the following...
Dim A as Date
A=#7/24/2005#

I wish to compare value of A against 2 other values:
1) 8/1/2005
2) 9/1/2005

Which is better and why...
First:
If A < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
End If
If A < #9/1/2005# Then
MsgBox "Date is earlier than 9/1/2005"
Exit Sub
End If
Or Second:
Select Case A
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
Case Is < #9/1/2005#
MsgBox "Date is earlier than 9/1/2005"
End Select
Exit Sub

I prefer not to use CASE SELECT with anything but absolutely
exclusive criteria. You are depending on the order of the CASE
statements to get a correct result. If you re-ordered it as:
Select Case A
Case Is < #9/1/2005#
MsgBox "Date is earlier than 9/1/2005"
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
End Select
You'd get incorrect results.
That's why I'd want to make it be something like this:
Select Case A
Case Is >= #8/1/2005# And (A < #9/1/2005#)
MsgBox "Date is earlier than 9/1/2005"
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
End Select
Now, you see that is bad, because it means you're doing two
different kinds of tests. What you'd like to do is:
Case Is >= #8/1/2005# And Is < #9/1/2005#
but that isn't valid code.
I think it's simpler to code this as a single If/Then/Else
statement:
If A < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
ElseIf A < #9/1/2005# Then
MsgBox "Date is earlier than 9/1/2005"
Exit Sub
End If
Maybe you didn't know about ElseIf? It's certainly not new in
Access 97.

Select Case True
Case A >= #8/1/2005# And A <= #9/30/2005#
Debug.Print "Yes"
Case Else
Debug.Print "No"
End Select

Well, yes, with only two conditions, but I assumed there were likely
3 or more choices and the example had been simplified to two.

And, of course, your example isn't correct, either, as there are
three conditions:

1. < 8/1
2. between 8/1 and 9/1
3. > 9/1

You can't do that with testing a single value, except in an
If/Then/ElseIf structure.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Chuck Grimsby]

On Mon, 25 Jul 2005 21:13:13 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in
news:7g********************************@4ax.com :

On Sun, 24 Jul 2005 15:34:50 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

MLH <CR**@NorthState.net> wrote in
news:fk********************************@4ax.com :

Suppose the following...
Dim A as Date
A=#7/24/2005#

I wish to compare value of A against 2 other values:
1) 8/1/2005
2) 9/1/2005

Which is better and why...
First:
If A < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
End If
If A < #9/1/2005# Then
MsgBox "Date is earlier than 9/1/2005"
Exit Sub
End If
Or Second:
Select Case A
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
Case Is < #9/1/2005#
MsgBox "Date is earlier than 9/1/2005"
End Select
Exit Sub

I prefer not to use CASE SELECT with anything but absolutely
exclusive criteria. You are depending on the order of the CASE
statements to get a correct result. If you re-ordered it as:
Select Case A
Case Is < #9/1/2005#
MsgBox "Date is earlier than 9/1/2005"
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
End Select
You'd get incorrect results.
That's why I'd want to make it be something like this:
Select Case A
Case Is >= #8/1/2005# And (A < #9/1/2005#)
MsgBox "Date is earlier than 9/1/2005"
Case Is < #8/1/2005#
MsgBox "Date is earlier than 8/1/2005"
End Select
Now, you see that is bad, because it means you're doing two
different kinds of tests. What you'd like to do is:
Case Is >= #8/1/2005# And Is < #9/1/2005#
but that isn't valid code.
I think it's simpler to code this as a single If/Then/Else
statement:
If A < #8/1/2005# Then
MsgBox "Date is earlier than 8/1/2005"
Exit Sub
ElseIf A < #9/1/2005# Then
MsgBox "Date is earlier than 9/1/2005"
Exit Sub
End If
Maybe you didn't know about ElseIf? It's certainly not new in
Access 97.

Select Case True
Case A >= #8/1/2005# And A <= #9/30/2005#
Debug.Print "Yes"
Case Else
Debug.Print "No"
End Select

Well, yes, with only two conditions, but I assumed there were likely
3 or more choices and the example had been simplified to two.
And, of course, your example isn't correct, either, as there are
three conditions:
1. < 8/1
2. between 8/1 and 9/1
3. > 9/1
You can't do that with testing a single value, except in an
If/Then/ElseIf structure.

Bull.

How complex would you like to make this?

Select Case True
Case Weekday(A) = 1
Debug.Print "Any Sunday"
Case A < #8/1/2005#
Debug.Print "Less then 8/1"
Case A = #8/25/2005#
Debug.Print "8/25"
Case A >= #8/6/2005# And A <= #8/13/2005#
Debug.Print "In Range 1"
Case A >= #8/10/2005# And A <= #10/3/2005#
Debug.Print "In Range 2"
Case A >= #8/1/2005# And A <= #9/30/2005# And Weekday(A) = 3
Debug.Print "Tuesday In Range 3"
Case A >= #8/1/2005# And A <= #9/30/2005#
Debug.Print "In Range 3"
Case A > #9/30/2005#
Debug.Print "Greater Then 9/30"
Case Else
Debug.Print "No Selection"
End Select

Gee! Look Ma! No If/Then/Else (or If/Then/ElseIf) required. And...
And... it works!

Will wonders never cease?
--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[David W. Fenton]

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in
news:lq********************************@4ax.com:

How complex would you like to make this?

Select Case True
Case Weekday(A) = 1
Debug.Print "Any Sunday"
Case A < #8/1/2005#
Debug.Print "Less then 8/1"
Case A = #8/25/2005#
Debug.Print "8/25"
Case A >= #8/6/2005# And A <= #8/13/2005#
Debug.Print "In Range 1"
Case A >= #8/10/2005# And A <= #10/3/2005#
Debug.Print "In Range 2"
Case A >= #8/1/2005# And A <= #9/30/2005# And Weekday(A) = 3
Debug.Print "Tuesday In Range 3"
Case A >= #8/1/2005# And A <= #9/30/2005#
Debug.Print "In Range 3"
Case A > #9/30/2005#
Debug.Print "Greater Then 9/30"
Case Else
Debug.Print "No Selection"
End Select

Gee! Look Ma! No If/Then/Else (or If/Then/ElseIf) required.
And... And... it works!

Will wonders never cease?

OK, don't know why I had the brain fart on that.

I don't see why anyone would write the CASE SELECT outlined above,
as you save nothing in terms of evaluation time. The beauty of CASE
SELECT in comparison to If/Then/Else is that it evaluates the test
case once, then compares the result to the various CASEs. Given that
all the complexity is in the CASEs themselves, you gain nothing at
all.

But, yes, my statement was, in fact, wrong.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Chuck Grimsby]

On Tue, 26 Jul 2005 14:20:07 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in
news:lq********************************@4ax.com :

Select Case True
Case Weekday(A) = 1
Debug.Print "Any Sunday"
Case A < #8/1/2005#
Debug.Print "Less then 8/1"
Case A = #8/25/2005#
Debug.Print "8/25"
Case A >= #8/6/2005# And A <= #8/13/2005#
Debug.Print "In Range 1"
Case A >= #8/10/2005# And A <= #10/3/2005#
Debug.Print "In Range 2"
Case A >= #8/1/2005# And A <= #9/30/2005# And Weekday(A) = 3
Debug.Print "Tuesday In Range 3"
Case A >= #8/1/2005# And A <= #9/30/2005#
Debug.Print "In Range 3"
Case A > #9/30/2005#
Debug.Print "Greater Then 9/30"
Case Else
Debug.Print "No Selection"
End Select

I don't see why anyone would write the CASE SELECT outlined above,
as you save nothing in terms of evaluation time. The beauty of CASE
SELECT in comparison to If/Then/Else is that it evaluates the test
case once, then compares the result to the various CASEs. Given that
all the complexity is in the CASEs themselves, you gain nothing at
all.

Actually, I do statements like that all the time, which is why I can
write them without a great deal of difficulty.

Usually, these kinds of structures appear (in my code anyways) when a
If/Then/Else statement might miss an occurrence, and/or when the
_order_ of evaluation is a concern as well as what information is
being passed to the function. That sort of thing tends to be fairly
common in Payroll, Pricing and Scheduling applications.

Personally, I happen to think the code looks a bunch cleaner then a
series of nested If/Then/Else statements as well, but that's just me.

It's also rather nice to know that I can place one "ELSE" statement at
the end to trap anything missed, rather then a whole set/series of
them within the nested If/Then/Else structures.
--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[David W. Fenton]

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in
news:46********************************@4ax.com:

On Tue, 26 Jul 2005 14:20:07 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in
news:lq********************************@4ax.co m:

Select Case True
Case Weekday(A) = 1
Debug.Print "Any Sunday"
Case A < #8/1/2005#
Debug.Print "Less then 8/1"
Case A = #8/25/2005#
Debug.Print "8/25"
Case A >= #8/6/2005# And A <= #8/13/2005#
Debug.Print "In Range 1"
Case A >= #8/10/2005# And A <= #10/3/2005#
Debug.Print "In Range 2"
Case A >= #8/1/2005# And A <= #9/30/2005# And Weekday(A) = 3
Debug.Print "Tuesday In Range 3"
Case A >= #8/1/2005# And A <= #9/30/2005#
Debug.Print "In Range 3"
Case A > #9/30/2005#
Debug.Print "Greater Then 9/30"
Case Else
Debug.Print "No Selection"
End Select

I don't see why anyone would write the CASE SELECT outlined above,
as you save nothing in terms of evaluation time. The beauty of
CASE SELECT in comparison to If/Then/Else is that it evaluates the
test case once, then compares the result to the various CASEs.
Given that all the complexity is in the CASEs themselves, you gain
nothing at all.

Actually, I do statements like that all the time, which is why I
can write them without a great deal of difficulty.

Usually, these kinds of structures appear (in my code anyways)
when a If/Then/Else statement might miss an occurrence, and/or
when the _order_ of evaluation is a concern as well as what
information is being passed to the function. That sort of thing
tends to be fairly common in Payroll, Pricing and Scheduling
applications.

Personally, I happen to think the code looks a bunch cleaner then
a series of nested If/Then/Else statements as well, but that's
just me.

It's also rather nice to know that I can place one "ELSE"
statement at the end to trap anything missed, rather then a whole
set/series of them within the nested If/Then/Else structures.

Huh?

If ... Then
ElseIf ... Then
ElseIf ... Then
ElseIf ... Then
Else
End If

I don't see any difference here.

A SELECT CASE with the evalated expression in each CASE (instead of
in the SELECT) will have to test through all of them until it comes
to a match, falling through the whole set of them, just like with
If/Then/ElseIf.

Your criticism applies only if you're doing what I was explicitly
advising against by recommending ElseIf, which was to avoid
independent If/Then/Else tests in succession.

If ... Then
[]
Exit Sub
End If
If ... Then
[]
Exit Sub
End If
If ... Then
[]
Exit Sub
End If
If ... Then
[]
End If

That does indeed have the problem you suggested, but making it a
single If/Then block eliminates that problem entirely.

It really looks like an If/Then/ElseIf structure to me, much more
than it looks like a SELECT CASE, since each of your CASEs is
evaluating a different set of criteria.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc