Select Case vs. IIF (need help)

[dkuntz]

I think the follwong IIF would be better placed in a module. I just need a little help in getting started. 4 fields in this query are expressions.

JSGrade: (see below)
5pmBillCodes: [bill_code] In ("BR","RB","TB","B2","VB","B3")
11pmBillCodes: [bill_code] In ("B6")
12pmBillCodes: [bill_code] In ("B5")


This is the expression where there is a lengthy IIF statement:

JSGrade: IIf([5pmBillCodes] And [target_pickup_time]<="17:00","100%",IIf([5pmBillCodes] And [target_pickup_time]<="17:15","95%",IIf([5pmBillCodes] And [target_pickup_time]<="17:30","90%",IIf([5pmBillCodes] And [target_pickup_time]<="17:45","85%",IIf([5pmBillCodes] And [target_pickup_time]<="18:00","80%",IIf([5pmBillCodes] And [target_pickup_time]<="18:15","75%",IIf([5pmBillCodes] And [target_pickup_time]<="18:30","70%",IIf([5pmBillCodes] And [target_pickup_time]<="18:45","65%",IIf([5pmBillCodes] And [target_pickup_time]<="19:00","60%",IIf([11pmBillCodes] And [target_pickup_time]<="23:00","100%",IIf([11pmBillCodes] And [target_pickup_time]<="23:15","95%",IIf([11pmBillCodes] And [target_pickup_time]<="23:30","90%",IIf([12pmBillCodes] And [target_pickup_time]<="12:00","100%","0%")))))))))))))

Let let me know if I need to better clarify myself. Any help or suggestions is much appreciated!

 

[FoFa]

How about a function you can use in the query to do this.

 

[dkuntz]

I made this Function but I am getting an error that says:
"Undefined function GetDeliveryGrade in expression".

This is the Function: (reduced in size for simplicity)

Public Function GetDeliveryGrade(ByVal sBillCode As String, ByVal DelTIme As String) As Integer

On Error GoTo Err_GetDeliveryGrade

Select Case sBillCode
Case Is = "BR" Or "RB" Or "TB" Or "B2" Or "VB" Or "B3" And DelTIme >= "17:00"
GetDeliveryGrade = 100

Case Is = "BR" Or "RB" Or "TB" Or "B2" Or "VB" Or "B3" And DelTIme >= "17:15"
GetDeliveryGrade = 95

Case Is = "B6" And DelTIme >= "23:00"
GetDeliveryGrade = 100

Case Is = "B6" And DelTIme >= "23:15"
GetDeliveryGrade = 95

Case Is = "B5" And DelTIme >= "12:00"
GetDeliveryGrade = 100

Case Is = "B5" And DelTIme >= "12:15"
GetDeliveryGrade = 95

Case Else
GetDeliveryGrade = 0

End Select

Exit_GetDeliveryGrade:
Exit Function

Err_GetDeliveryGrade:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_GetDeliveryGrade


End Function

..And here is the Expression that is calling the Function:

JSGrade: GetDeliveryGrade([bill_code],[target_pickup_time])
Expression


Thank you!

 

[The_Doc_Man]

Rather than a lengthy IIf, one of three things come to mind.

As FoFa suggested, you could implement this in VBA. That is what I would do personally, but VBA isn't always the right answer if you aren't comfortable with VBA.

The next thought: Look up the ACCESS "SWITCH" function, which lets you do something close to a SELECT CASE in a single, but still cumbersome, statement.

Third, it looks like you could make a short table out of the times you are showing. Try a Dlookup where the criteria field includes a "x BETWEEN y AND z" sub-clause.

 

[Elana]

Here is an example of a Select Case syntax that would work on a form event (like the AfterUpdate event of the TargetPickupTime field).

Select Case bill_code

Case "BR", "RB", "TB", "B2", "VB", "B3"

Select Case target_pickup_time

case 17:00

jsgrade = 100%

case 17:15

jsgrade = 95%

case etc etc -- put other jsgrade calcs as in the example


End Select

Case "B6"

Select Case target_pickup_time

case 23:00

jsgrade = 95%

case 23:30

jsgrade = 90%

case etc., etc., - put other jsgrade calcs as in the example

End Select

Case "B5"

Select Case target_pickup_time

case 24:00

jsgrade = 100%

case etc etc.- put other jsgrade calcs as in the example


End Select

End Select

This is a very rough example and you will obviously need to put quotes around strings, etc. but this should give you an idea of the syntax.

HTH
E

 

[dkuntz]

Elana - Thanks for the example. I like the simplicity of it. I changed my Select statement in my function the way you have it, but I am still getting the same error as before:

"Undefined function GetDeliveryGrade in expression".

Functions can be used in Queries correct?

Is this set up wrong:
Public Function GetDeliveryGrade(ByVal sBillCode As String, ByVal DelTIme As String) As Integer

or maybe this is wrong:
JSGrade: GetDeliveryGrade([bill_code],[target_pickup_time])

Expression


At any rate, thanks for the help.

 

[Elana]

Hi,

Sorry about that - when I started composing my last post to you with my rudimentary example, I walked away for awhile and then came back to it. Never noticed that in the meantime you had posted your code! Sorry - it's pretty obvious you didn't need such a basic example. Note to myself: don't walk away from your PC when composing a reply to someone's post!



Anyway, I'm not sure I can help with the error message tho - the only information I can see for this error have to do with Data Access Pages. If you are working with a DAP, then search the help file for info on this error. If not, perhaps one of the great minds browsing this forum can help.

E

 

[raskew]

This is a scenario just waiting for the Switch() function.

Since your example shows that the percentage consistently decrements 5% for every 15 minutes late, try working this in:

JSGrade: Switch([5pmBillCodes], 1 - (datediff("n", #17:00#, cdate(target_pickup_time))/15 * 0.05),
[11pmBillCodes], 1 - (datediff("n", #23:00#, cdate(target_pickup_time))/15 * 0.05),
[12pmBillCodes], 1 - (datediff("n", #12:00#, cdate(target_pickup_time))/15 * 0.05))

 

[dkuntz]

Raskew -

Unfortunately this is resulting in very random numbers. I like the idea of using the Switch function if only the output can be accurate.

These are just a few results I am getting:
JSGrade:
1.3, -0.18, 1.6, 0.87, 1.7, 1.85, 1.72, 1.73, 1.91, 0.92, 1.63

Let me know if I should try something else -

Thanks!

 

[raskew]

A very interesting problem—more complex than it initially appears. Allow me to restate it in more generic terms.

We’ve got goal-times [XpmBillCodes] and actual-times (target_pickup_time). Goal-times being (in your example) 1700 (5:00 PM), 2300 (11:00 PM) or 2400 (mid-night).

The idea is that if actual-time <= goal-time (the delivery is on or ahead of schedule) it returns 1 or 100%. However, if actual-time > goal-time (e.g., the delivery is late) then for each 15 minutes (or portion thereof) that the delivery is late, the percentage decrements by 5%. For example, a delivery that’s 14 minutes late would return 0.95, one that’s late by 1 hour and 30 minutes would return 0.70.

My original response assumed (incorrectly) that actual-times would be expressed precisely on the quarter-hour, i.e. 00, :15, :30: 45). Obviously that wasn’t the case so when applied against your data you were returned: 0.18, 0.87, 0.92 when you were expecting 0.15, 0.85, 0.90.

Compounding the problem, I erroneously allowed the code to create returns greater than 1 (100%) when the actual-time was significantly earlier than the goal time, thus the returns of: 1.3, 1.6, 1.7, 1.85, 1.72, 1.73, 1.91, 1.63.

These problems are fairly easily resolved. However, the killer which I’ve been wrestling with for the past couple of days is with the potential of before-midnight / after-midnight dates which could occur with [11pmBillCodes] and most certainly will occur with [12pmBillCodes]. (And this is where the –0.18 response comes from.)

My goal was to write tight, concise code that would correctly deal with the following scenarios, while returning correct results when both the Goal-Time and Actual-Time coexist in the same day. Thus far, it’s been a losing battle.

<table>
Goal-Time		
5PMBillCodes	11PMBillCodes	MidNiteBillCodes	Actual-Time	Should return:
No	Yes	No	00:16	0.70
No	No	Yes	23:16	1
No	No	Yes	00:01	0.95
</table>

Would really be interested to see if someone can get a handle on this without a zillion lines of code.

Sorry about the formatting. Not a strong point of mine.

Best wishes,

Bob

 

[Jon K]

When I used Elana's syntax in a public function GetDeliveryGrade() in the attached DB, the expression:-
JSGrade SelectCase: GetDeliveryGrade([Bill_Code],[Target_Pickup_Time])
in the query Query1 ran fine without giving me any error message.

------------------------
Public Function GetDeliveryGrade(bill_code As String, target_pickup_time As String) As String

Select Case bill_code
Case "BR", "RB", "TB", "B2", "VB", "B3"

Select Case target_pickup_time
Case Is <= "17:00"
GetDeliveryGrade = "100%"
Case Is <= "17:15"
GetDeliveryGrade = "95%"
Case Is <= "17:30"
GetDeliveryGrade = "90%"
Case Is <= "17:45"
GetDeliveryGrade = "85%"
Case Is <= "18:00"
GetDeliveryGrade = "80%"
Case Is <= "18:15"
GetDeliveryGrade = "75%"
Case Is <= "18:30"
GetDeliveryGrade = "70%"
Case Is <= "18:45"
GetDeliveryGrade = "65%"
Case Is <= "19:00"
GetDeliveryGrade = "60%"
Case Else
GetDeliveryGrade = "0%"
End Select

Case "B6"

Select Case target_pickup_time
Case Is <= "23:00"
GetDeliveryGrade = "100%"
Case Is <= "23:15"
GetDeliveryGrade = "95%"
Case Is <= "23:30"
GetDeliveryGrade = "90%"
Case Else
GetDeliveryGrade = "0%"
End Select

Case "B5"

If target_pickup_time <= "12:00" Then
GetDeliveryGrade = "100%"
Else
GetDeliveryGrade = "0%"
End If

Case Else
GetDeliveryGrade = "0%"
End Select

End Function
------------------------

(Note. The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.)

 

[raskew]

Jon-

Agree that your code works very nicely. (For anyone trying to follow this, you need to download Jon's example -- otherwise it won't make a bit of sense.)

However, there's a definition problem here. The writer described "B5" as [12pmBillCodes] but then went on to offer examples which were based on minutes before 24:00 (00:00).

12pm is in fact one minute after 11:59 AM, (i.e. Noon)
from the debug window-
widget = date() + #11:59#
? widget
5/6/03 11:59:00 AM
widget = date() + #12:00#
? widget
5/5/03 12:00:00 PM

Your code picked up on the misinterpreation and treats B5 to be Noon, not midnight. To see this in action, in your table, change the target_pickup_time for ID 7 to 23:29 and for ID 8 to 00:01. Then change ID 6 to 00:01. Run the query and you'll see that the code doesn't cope.

That's what I'm trying (without success) to resolve.

Best wishes,

Bob

 

[Jon K]

Thanks Bob for pointing this out. I converted the IIF expression to Select Case statements without analysing the bill codes and the pick up time.

As the time is counted as 23:58, 23:59, 00:00, 00:01, etc, comparing the time around midnight can always be a headache if we do not consider the dates as well.

Jon

 

[dkuntz]

Raskew -

Thanks for the posts.

you wrote:
------------------------------------------------------------------------------------
However, there's a definition problem here. The writer described "B5" as [12pmBillCodes] but then went on to offer examples which were based on minutes before 24:00 (00:00).

-and-

We’ve got goal-times [XpmBillCodes] and actual-times (target_pickup_time). Goal-times being (in your example) 1700 (5:00 PM), 2300 (11:00 PM) or 2400 (mid-night).
-----------------------------------------------------------------------------------

I think you may be looking at Elana's example:

------------------------------------------------------------------------------------
Case "B5"
Select Case target_pickup_time
case 24:00
jsgrade = 100%
------------------------------------------------------------------------------------
Probably just a simple mistake.

[12pmBillCodes] should have been named [NoonNextDayBillCodes]. I failed to mention that any transactions with these billcodes (currently only "B5") need to be delivered by noon the next day.

Similar to [11pmBillCodes] we have a situation where deliveries are crossing over midnight that need to be graded even though the pickups were made the previous day.

Your time is appreciated, Thanks again -
Derek