Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 1 / December 2005

Tip: Looking for answers? Try searching our database.

Date Validation: IF's vs Case

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Daron - 27 Dec 2005 16:46 GMT
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 - 27 Dec 2005 17:09 GMT
> 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.

Signature

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

Daron - 28 Dec 2005 12:58 GMT
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 - 28 Dec 2005 13:50 GMT
> 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
[quoted text clipped - 5 lines]
> 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

Signature

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

Daron - 28 Dec 2005 15:39 GMT
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 - 28 Dec 2005 15:50 GMT
> 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

Signature

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

Daron - 28 Dec 2005 17:26 GMT
Rick,

Thanks for your help!
David W. Fenton - 28 Dec 2005 18:52 GMT
>> That helps! I was doing this before, but the logic needed to be
>> simplified.
[quoted text clipped - 10 lines]
>     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.

Signature

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

Rick Brandt - 28 Dec 2005 19:03 GMT
>>> That helps! I was doing this before, but the logic needed to be
>>> simplified.
[quoted text clipped - 16 lines]
> 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.

Signature

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

David W. Fenton - 28 Dec 2005 21:53 GMT
>>>> That helps! I was doing this before, but the logic needed to be
>>>> simplified.
[quoted text clipped - 19 lines]
> 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.

Signature

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

Rick Brandt - 28 Dec 2005 22:10 GMT
> 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.

Signature

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

David W. Fenton - 29 Dec 2005 02:01 GMT
>> There are any number of things that are *possible* on any
>> subject. That doesn't mean that one should explain how to do
[quoted text clipped - 5 lines]
> 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.

Signature

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

Chuck Grimsby - 28 Dec 2005 23:10 GMT
>>> Just curious, but I am still wondering. Is it possible
>>> incorporate functions into a Case statement?  Or is this a new
[quoted text clipped - 6 lines]
>>     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.

Signature

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

rkc - 29 Dec 2005 00:49 GMT
>>>>Just curious, but I am still wondering. Is it possible
>>>>incorporate functions into a Case statement?  Or is this a new
[quoted text clipped - 16 lines]
> 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 - 29 Dec 2005 02:05 GMT
>>>>>Just curious, but I am still wondering. Is it possible
>>>>>incorporate functions into a Case statement?  Or is this a new
[quoted text clipped - 19 lines]
> 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.

Signature

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

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.