Converting an expression to Select Case statement

[stevekos07]

I have a rather complex expression in an unbound calculated field on a form. This started out much simpler than it is now, but as more and more conditions have been added to the expression it has now become too unwieldy to manage as an expression.

I am thinking of converting it to a Select Case statement either as an OnLoad event on the field, or as a public function that I can call from anywhere.

I am not unfamiliar with Select Case but I have not written one personally. Any help would be greatly appreciated.

This is the expression:

=IIf(Date()=[NoCallFrom]
Or Date()>=[NoCallFrom] And Date()<=[NoCallTo]
Or Date()=[NoCallFrom2]
Or Date()>=[NoCallFrom2] And Date()<=[NoCallTo2]
Or Weekday(Date(),1)=1 And [Sun]=True
Or Date()>=[NCUFN_Date]
Or [Status]=3
Or [Status]=4
Or Weekday(Date(),1)=2 And [Mon]=True
Or Weekday(Date(),1)=3 And [Tue]=True
Or Weekday(Date(),1)=4 And [Wed]=True
Or Weekday(Date(),1)=5 And [Thu]=True
Or Weekday(Date(),1)=6 And [Fri]=True
Or Weekday(Date(),1)=7 And [Sat]=True
Or [P/H]=True And DLookUp("HolDate","tblPublicHolidays","HolDate=Date()"),"No","Yes")

 

[pbaldy]

Does that actually work? It's very dangerous to mix And & Or without including parentheses to clarify the desired logic.

I'm not sure Select/Case is appropriate, since that would be useful when there are a variety of options, with a different action taken for each.

That all said, if you make it a public function, you'll have to pass it all the field values. Here's a primer:

http://www.baldyweb.com/Function.htm

 

[stevekos07]

It may not be the most perfect syntax, but it works.

How would you deal with this then? How would I convert it to VBA? Just as a standard If statement but as VBA rather than as field expression?

 

[pbaldy]

You haven't shown what it does, but yes, a standard If/Then/Else.

 

[CJ_London]

the case 'function' is the switch function but don't think it (or using a case statement in code) will simplify your code since you only have two outcomes, and a case statement determines different outcomes based on a single value - in your case each of your Or's would require a separate case statement.

You don't necessarily need the iif function since the returns are yes and no. Just remove the iif so your code becomes

=Date()=[NoCallFrom] 
Or Date()>=[NoCallFrom] And Date()<=[NoCallTo]
...
...
...
Or [P/H]=True And DLookUp("HolDate","tblPublicHolidays","HolDate=Date()")

then use the format property of the textbox as follows

;"Yes";"No"

and you not need to use =true when referencing a Boolean value

...
Or Weekday(Date(),1)=2 And [Mon]
...

will suffice or perhaps

...
Or Weekday(Date(),1)=2=[Mon]
...


I think you can simplify these lines

...
Or Weekday(Date(),1)=1 And [Sun]=True
Or Weekday(Date(),1)=2 And [Mon]=True
Or Weekday(Date(),1)=3 And [Tue]=True
Or Weekday(Date(),1)=4 And [Wed]=True
Or Weekday(Date(),1)=5 And [Thu]=True
Or Weekday(Date(),1)=6 And [Fri]=True
Or Weekday(Date(),1)=7 And [Sat]=True
...

to a single line

...
Or Choose(Weekday(Date(),1),[Sun],[Mon]....[Sat])
...


Another alternative perhaps (without knowing the purpose of your code) is instead of determining everything that is no, instead determine everything that is yes.

 

[plog]

Agree with pbaldy--mixing ANDs and ORs is very unkosher. Have you tested every case?

I would just make a series of If statements (No elses) in a function like so:

function get_YourValue() AS String
    ' returns yes or no based on conditions in form

ret="No"    ' default value, will test all Yes conditions below

If(Date()=Me![NoCallFrom]) Then ret="Yes"
If(Date()>=Me![NoCallFrom] And Date()<=Me![NoCallTo]) Then ret="Yes"
.... All conditions that evaluate to Yes below ...

get_YourValue = ret

End Function

 

[stevekos07]

Agree with pbaldy--mixing ANDs and ORs is very unkosher. Have you tested every case?

I would just make a series of If statements (No elses) in a function like so:

function get_YourValue() AS String
    ' returns yes or no based on conditions in form

ret="No"    ' default value, will test all Yes conditions below

If(Date()=Me![NoCallFrom]) Then ret="Yes"
If(Date()>=Me![NoCallFrom] And Date()<=Me![NoCallTo]) Then ret="Yes"
.... All conditions that evaluate to Yes below ...

get_YourValue = ret

End Function

Thanks for that. I will try this and see how I go.

By the way, yes, I have tested every case in the expression and it works flawlessly (so far). Such is the blissful ignorance of the self-taught!

The expression evaluates a range of conditions to determine whether a client needs to be called today (call centre operation) based a wide variety of conditions as per the field references. It displays each client as either "Yes" (requires a call) or "No" (does not require a call). The clients are printed on "call sheets" with the "Yes's" only showing.

This is the first stage of developing a true call centre database application.

 

[stevekos07]

Ok, I have written the code and placed it in a module called "CallTodayValue". I have added some additional code based on two fields that override any other considerations in the evaluation. I would appreciate someone just checking this to see if it should work:



Function CallTodayValue() As String

' returns yes or no based on conditions in form

ret = "Yes" ' default value, will test all No conditions below

If (Date = Me![SpecialCallFrom]) Then GoTo CallTodayValue
If (Date = Me![SpecialCallTo]) Then GoTo CallTodayValue
If (Date >= Me![SpecialCallFrom] And Date <= [SpecialCallTo]) Then GoTo CallTodayValue

If (Date = Me![NoCallFrom]) Then ret = "No"
If (Date >= Me![NoCallFrom] And Date <= Me![NoCallTo]) Then ret = "No"
If (Weekday(Date, 1) = 1 And [Sun] = True) Then ret = "No"
If (Weekday(Date, 1) = 2 And [Mon] = True) Then ret = "No"
If (Weekday(Date, 1) = 3 And [Tue] = True) Then ret = "No"
If (Weekday(Date, 1) = 4 And [Wed] = True) Then ret = "No"
If (Weekday(Date, 1) = 5 And [Thu] = True) Then ret = "No"
If (Weekday(Date, 1) = 6 And [Fri] = True) Then ret = "No"
If (Weekday(Date, 1) = 7 And [Sat] = True) Then ret = "No"
If ([P/H] = True And DLookup("HolDate", "tblPublicHolidays", "HolDate=Date()")) Then ret = "No"
'.... All conditions that evaluate to Yes below ...

CallTodayValue:
CallTodayValue = ret

End Function

 

[plog]

Computers are better at finding errors in code than humans. Run it.

 

[The_Doc_Man]

[COLOR="Red"]Or Weekday(Date(),1)=1 And [Sun]=True[/COLOR]
Or Date()>=[NCUFN_Date]
Or [Status]=3
Or [Status]=4
[COLOR="Red"]Or Weekday(Date(),1)=2 And [Mon]=True
Or Weekday(Date(),1)=3 And [Tue]=True
Or Weekday(Date(),1)=4 And [Wed]=True
Or Weekday(Date(),1)=5 And [Thu]=True
Or Weekday(Date(),1)=6 And [Fri]=True
Or Weekday(Date(),1)=7 And [Sat]=True[/COLOR]

First comment: I sometimes look at code with this many criterial and think, "Would it be easier to select what I want or to reject what I don't want?" PLOG has you on the right track by setting up code that assumes one answer but then tests for individual cases that merit the other answer. The reason you would NOT use select case for your ladder is because the cases are not commensurate. Those tests of [Status] and [P/H] are not of the same flavor as the date tests.

Second comment: This code SEEMS to say that if you are going to call someone, you only would do it if a callback date was the right day of the week. But if you miss that day, do you make the customer wait a whole freakin' week for the callback? This is not a call center policy I would want in place if I have a problem.

Third comment:

Or Date()>=[NoCallFrom] And Date()<=[NoCallTo]

Look in Date() BETWEEN [NoCallFrom] AND [NoCallTo] as a means of clarification of intent in case you need to walk away from this and then come back a few weeks from now. At which point you ask yourself, "What was the point of these items again?" By using the BETWEEN ... AND syntax, you tie down the intent that the date has to be between two dates, but the separated comparisons using <= and >= don't capture that point. OK, this one is more aesthetic than substantive, but the idea is to capture the intent in your code so that you can remember WHY you were making the test. Using BETWEEN...AND is more English-like and just reads better.

Fourth comment: Probably shouldn't have punctuation in a field name. [P/H] is probably not a good name choice.

 

[CJ_London]

@Doc - the OP was using this as a controlsource, so BETWEEN won't work. I suggest a much simpler formula for this bit but don't think OP has read it, just responding to the post that he receives in his inbox.

 

[kosch2015]

All good. I used the ret statement as suggested, and after passing some Nz's and other parameters to cover Null values in various fields, I got it to run as a Public Function I can now call from anywhere.

This might not be THE best way to code this but I understand what is going on and I can continue to add further parameters to the code as required.

Thanks everyone!

 

[kosch2015]

[COLOR=Red]Or Weekday(Date(),1)=1 And [Sun]=True[/COLOR]
Or Date()>=[NCUFN_Date]
Or [Status]=3
Or [Status]=4
[COLOR=Red]Or Weekday(Date(),1)=2 And [Mon]=True
Or Weekday(Date(),1)=3 And [Tue]=True
Or Weekday(Date(),1)=4 And [Wed]=True
Or Weekday(Date(),1)=5 And [Thu]=True
Or Weekday(Date(),1)=6 And [Fri]=True
Or Weekday(Date(),1)=7 And [Sat]=True[/COLOR]

First comment: I sometimes look at code with this many criterial and think, "Would it be easier to select what I want or to reject what I don't want?" PLOG has you on the right track by setting up code that assumes one answer but then tests for individual cases that merit the other answer. The reason you would NOT use select case for your ladder is because the cases are not commensurate. Those tests of [Status] and [P/H] are not of the same flavor as the date tests.

Second comment: This code SEEMS to say that if you are going to call someone, you only would do it if a callback date was the right day of the week. But if you miss that day, do you make the customer wait a whole freakin' week for the callback? This is not a call center policy I would want in place if I have a problem.

Third comment:

Or Date()>=[NoCallFrom] And Date()<=[NoCallTo]

Look in Date() BETWEEN [NoCallFrom] AND [NoCallTo] as a means of clarification of intent in case you need to walk away from this and then come back a few weeks from now. At which point you ask yourself, "What was the point of these items again?" By using the BETWEEN ... AND syntax, you tie down the intent that the date has to be between two dates, but the separated comparisons using <= and >= don't capture that point. OK, this one is more aesthetic than substantive, but the idea is to capture the intent in your code so that you can remember WHY you were making the test. Using BETWEEN...AND is more English-like and just reads better.

Fourth comment: Probably shouldn't have punctuation in a field name. [P/H] is probably not a good name choice.

Good points, but I have been successful in getting the code to run correctly. See my final comment above. It is hard to get a complete understanding of the logic of the code without the context, I do appreciate.
Cheers.

 

[The_Doc_Man]

CJ - Understood. I didn't catch the .ControlSource part. It was a bit confusing to see where it was going.