MS Access Forum / General 1 / December 2005
Date Validation: IF's vs Case
|
|
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/
|
|
|