Date Validation: IF's vs Case

[Daron]

I am doing so data validation, and need to check if a filed contains a
valid date in the format of "YYYY-MM-DD"

I would like to know if it is possible to convert this series of If's
to a Select Case. I am having trouble incorporating the various
functions (len(), isdate(). The IF's work, but I know this could be
better and more flexible. Any suggestions would be greatly appreciated.
:
'--------------------------------
If Len(str_CheckDate) <> 10 Then
'null check, field must contain something
fun_CheckDateFormat = False

ElseIf str_CheckDate = "" Then
'null check, field must contain something
fun_CheckDateFormat = False

ElseIf IsDate(str_CheckDate) Then
'Check if a valid date
' - If invalid format, need to indicate a 'formatting' error of
some kind

'must contain '-' as seperator character
If Mid(str_CheckDate, 5, 1) <> "-" Or _
Mid(str_CheckDate, 8, 1) <> "-" Then
fun_CheckDateFormat = False

Else

str_Year = Left(str_CheckDate, 4)
str_Month = Mid(str_CheckDate, 6, 2)
str_Day = Right(str_CheckDate, 2)

'check for valid year
If str_Year <= Year(Now()) - 25 Then
fun_CheckDateFormat = False
End If

'check for valid month
If str_Month < 1 Or str_Month > 12 Then
fun_CheckDateFormat = False
End If

'check for valid day in month
If Not (str_Day > 0 And str_Day <= Day(DateSerial(str_Year,
str_Month + 1, 0))) Then
fun_CheckDateFormat = False
End If
End If

ElseIf Not IsDate(str_CheckDate) Then
fun_CheckDateFormat = False
End If

'--------------------------------

Basically, can you do a CASE with the functions? How?:
Select Case str_CheckDate
Case Len() <> 10 ...
....
End Select

[Rick Brandt]

Daron wrote:

I am doing so data validation, and need to check if a filed contains a
valid date in the format of "YYYY-MM-DD"

You might be working too hard here. Are you using a DateTime type for the
field? If you do the entry of an invalid date would be impossible and the
formatting will have nothing to do with how the date is entered. It will only
control how it is displayed.

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

[Daron]

The work I am doing here is validating a text file that will later be
imported into an Oracle datawarehouse in a seperate process. All fields
are brought in as Text type, and then checked against a variety of
criteria (length, format, lookup tables, etc, etc...)

I could change to a date type variable in the procedure, but I still
need to check that the data is in the proper format, and then check
against other criteria. A Select Case would still be a great way to go,
if I can also check for length, IsDate, etc.

TIA

Daron

[Rick Brandt]

"Daron" <Da**********@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

The work I am doing here is validating a text file that will later be
imported into an Oracle datawarehouse in a seperate process. All fields
are brought in as Text type, and then checked against a variety of
criteria (length, format, lookup tables, etc, etc...)

I could change to a date type variable in the procedure, but I still
need to check that the data is in the proper format, and then check
against other criteria. A Select Case would still be a great way to go,
if I can also check for length, IsDate, etc.

Then all you need is to check against IsDate(). If that passes then set the
format regardless of which one was used.

If IsDate(str_CheckDate) Then
str_CheckDate = Format(CDate(str_CheckDate, "YYYY-MM-DD")
Else
'Handle error
End If

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

[Daron]

That helps! I was doing this before, but the logic needed to be
simplified.

Just curious, but I am still wondering. Is it possible incorporate
functions into a Case statement? Or is this a new topic?

psuedo example:

Select Case st_Check
Case Isdate
.....
Case Length
....
End Select

[Rick Brandt]

"Daron" <Da**********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

That helps! I was doing this before, but the logic needed to be
simplified.

Just curious, but I am still wondering. Is it possible incorporate
functions into a Case statement? Or is this a new topic?

Select Case True
Case (Len(strVar) < 10)
'
Case (Left(strVar,1) = "a")
'
etc..
End Select

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

[Daron]

Rick,

Thanks for your help!

[David W. Fenton]

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:Tx****************@newssvr11.news.prodigy.com :

"Daron" <Da**********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

That helps! I was doing this before, but the logic needed to be
simplified.

Just curious, but I am still wondering. Is it possible
incorporate functions into a Case statement? Or is this a new
topic?

Select Case True
Case (Len(strVar) < 10)
'
Case (Left(strVar,1) = "a")
'
etc..
End Select

In my opinion, this is abuse of SELECT CASE. What you want is nested
If/ElseIf/Else statements in this case.

CASE SELECT is for branching on multiple values of a single
expression.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Rick Brandt]

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:Tx****************@newssvr11.news.prodigy.com :

"Daron" <Da**********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

That helps! I was doing this before, but the logic needed to be
simplified.

Just curious, but I am still wondering. Is it possible
incorporate functions into a Case statement? Or is this a new
topic?

Select Case True
Case (Len(strVar) < 10)
'
Case (Left(strVar,1) = "a")
'
etc..
End Select

In my opinion, this is abuse of SELECT CASE. What you want is nested
If/ElseIf/Else statements in this case.

CASE SELECT is for branching on multiple values of a single
expression.

I have never actually used that type of Case statement. The question was simply
whether it was "possible". It clearly is.

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

[David W. Fenton]

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:4k*******************@newssvr13.news.prodigy. com:

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:Tx****************@newssvr11.news.prodigy.com :

"Daron" <Da**********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
That helps! I was doing this before, but the logic needed to be
simplified.

Just curious, but I am still wondering. Is it possible
incorporate functions into a Case statement? Or is this a new
topic?

Select Case True
Case (Len(strVar) < 10)
'
Case (Left(strVar,1) = "a")
'
etc..
End Select

In my opinion, this is abuse of SELECT CASE. What you want is
nested If/ElseIf/Else statements in this case.

CASE SELECT is for branching on multiple values of a single
expression.

I have never actually used that type of Case statement. The
question was simply whether it was "possible". It clearly is.

There are any number of things that are *possible* on any subject.
That doesn't mean that one should explain how to do something
without including an assessment of whether or not it's a good idea
or not.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Rick Brandt]

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...

There are any number of things that are *possible* on any subject.
That doesn't mean that one should explain how to do something
without including an assessment of whether or not it's a good idea
or not.

I have no knowledge of anything about using Case that way that makes it a "bad
idea". I just happen to never have used it in that fashion. If there are some
things that make it a bad idea I would love to hear them.

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

[Chuck Grimsby]

On Wed, 28 Dec 2005 12:52:27 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalid> wrote:

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:Tx****************@newssvr11.news.prodigy.co m:

"Daron" <Da**********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Just curious, but I am still wondering. Is it possible
incorporate functions into a Case statement? Or is this a new
topic? Select Case True
Case (Len(strVar) < 10)
'
Case (Left(strVar,1) = "a")
'
etc..
End Select

In my opinion, this is abuse of SELECT CASE. What you want is nested
If/ElseIf/Else statements in this case.
CASE SELECT is for branching on multiple values of a single
expression.

David, you and I have been down this road a few times. I understand
you don't agree with SELECT CASE use in this method, but it works and
is rather commonly used.

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

[rkc]

Chuck Grimsby wrote:

On Wed, 28 Dec 2005 12:52:27 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalid> wrote:

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:Tx****************@newssvr11.news.prodigy.c om:

"Daron" <Da**********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googl egroups.com...

Just curious, but I am still wondering. Is it possible
incorporate functions into a Case statement? Or is this a new
topic?

Select Case True
Case (Len(strVar) < 10)
'
Case (Left(strVar,1) = "a")
'
etc..
End Select

In my opinion, this is abuse of SELECT CASE. What you want is nested
If/ElseIf/Else statements in this case.
CASE SELECT is for branching on multiple values of a single
expression.

David, you and I have been down this road a few times. I understand
you don't agree with SELECT CASE use in this method, but it works and
is rather commonly used.

I think avoiding nested If/ElseIf statements is a worth while abuse of
Select Case.

Same goes for multiple exit statements in the same procedure.

[David W. Fenton]

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:53*******************@newssvr13.news.prodigy. com:

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...

There are any number of things that are *possible* on any
subject. That doesn't mean that one should explain how to do
something without including an assessment of whether or not it's
a good idea or not.

I have no knowledge of anything about using Case that way that
makes it a "bad idea". I just happen to never have used it in
that fashion. If there are some things that make it a bad idea I
would love to hear them.

It's a completely illogical use of CASE SELECT, as it turns it into
nothing more than an IF/THEN/ELSE.

Now, the only possible justification I could see is if you had
something like this, where you wanted the same branch in your code
from more than one expression (i.e., two different expression
results would branch into the same subroutine):

CASE SELECT TRUE
CASE <expression1>, <expression2>
...
CASE <expression3>, <expression4>
...
CASE ELSE
...
END SELECT

But, what is more difficult about:

IF <expression1> OR <expression2> THEN
...
ELSEIF <expression3> OR <expression4> THEN
...
ELSE
...
END IF

You type a comma instead of OR.

That's the only difference I can see.

The efficiency of CASE SELECT is supposed to come in the fact that
you evaluate an expression once, then compare the result to a number
of values, each of which branches in a different direction.
Evaluating TRUE once, then comparing it to a bunch of
non-independent expressions seems to me to turn the entire logic of
CASE SELECT inside-out. My suspicion is that CASE SELECT TRUE with
conditions in the cases means that it can't compile as efficiently,
and could mean that it is slower.

But I don't know that for a fact.

I do know that if your condition is there once, and the cases only
have literal values to compare to, those values must surely be
compiled as literal values in the VBA p-code. That's surely the kind
of thing that's highly optimized in VBA.

But that efficiency wouldn't really matter except if you were using
this approach in some function or subroutine that was called in a
loop or in a query result (so that it executed for each row, for
instance).

I just think that it's better to avoid that kind of upside-down CASE
SELECT than it is to try to figure out which circumstances in which
it will be OK and which it won't. I especially think that's the case
when there's a perfectly good and easy-to-write and -understand
alternative branching structure available.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:Go*******************@twister.nyroc.rr.com:

Chuck Grimsby wrote:

On Wed, 28 Dec 2005 12:52:27 -0600, "David W. Fenton"
<XX*******@dfenton.com.invalid> wrote:

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:Tx****************@newssvr11.news.prodigy. com:

"Daron" <Da**********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.goog legroups.com...

>Just curious, but I am still wondering. Is it possible
>incorporate functions into a Case statement? Or is this a new
>topic?

Select Case True
Case (Len(strVar) < 10)
'
Case (Left(strVar,1) = "a")
'
etc..
End Select

In my opinion, this is abuse of SELECT CASE. What you want is
nested If/ElseIf/Else statements in this case.
CASE SELECT is for branching on multiple values of a single
expression.

David, you and I have been down this road a few times. I
understand you don't agree with SELECT CASE use in this method,
but it works and is rather commonly used.

I think avoiding nested If/ElseIf statements is a worth while
abuse of Select Case.

What, exactly, is the problem with:

If <condition1> Then
...
ElseIf <condition2> Then
...
Else
...
End If

Why is that bad? There is absolutely *nothing* problematic about it,
especially since you can structure it either sequentially (i.e.,
string together non-exclusive conditions) or hierarchichally (i.e.,
the first condition branches into different results).

Using CASE SELECT to test multiple conditions is even more
convoluted, in my opinion.
Same goes for multiple exit statements in the same procedure.

Completely different issue, in my opinion.

But, yes, If/Then/Else can be abused, of course, and used to write
spaghetti code.

But to me CASE SELECT TRUE is by definition spaghetti code, since to
me, it's quite clearly a poor replacement for If/Then/Else.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/