Switch Statement in a query not working, 1

[iuianj07]

Hello guys,

I am trying to create this query that has a switch statement, this is for our invoicing reporting. the code is below:

Amount: Switch([OSAR_ImagetoVendor] Is Not Null,"$12.00",[24HourRush]=-1,"$75.00",[1-3DayRush]=-1,"$62.50",[ReportingPeriod] Like "*Q*","$50.00",[ReportingPeriod] Like "*YE*","$50.00")

Okay, so this is the current Switch statement when Reporting Period is either Q or YE (which is $50), and if the 24HourRush checkbox is checked (-1), the price will go up 50% ($75.00) or if it’s a 1-3DayRush (-1) it will go up to 25% ($62.50)

Now they changed the price for Q statements to $45.00 per statement, so with this change, the Switch statement should be when it’s a Q statement and the 24HourRush is checked, the price will be $67.50 or if 1-3DayRush is checked the price will be $56.25. And if it’s a YE statement, then it should just be the same as above ($75.00 and $62.50).

Can you help me re-write this? Thank you very much

 

[dhookom]

A complex Switch() statement in a query is not where you should be creating your business rules. I would encapsulate this calculation in a user-defined function that would have the fields as arguments and return the appropriate value. Save your function in a module named "modBusinessCalcs".

I'm not sure about your logic. The Switch conditions might be true for multiple expressions. For instance what if OSAR_ImageToVendor is not null ANDD [1-3DayRush] is true?

Also, is there a reason why you are returning strings/text like "$12.00" and "$75.00"? Why don't you return 12 and 75?

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

hello dhookom,

with regards to the OSAR_ImageToVendor is not null and [1-3DayRush] is true, the price would still be $12.00

the reason why we are returning strings/text of $12.00 and $75.00 because this is the price we bill our client for every statement we create for them.

It was easy to use the Switch statement when we bill Q or YE statements the same price, but since Q statements have changed the price, I should come up with a more complex switch statement.


Thank you

 

[iuianj07]

This Switch statement is only one column from our invoicing query, and if I created a module/function or even a VBA to replace this Switch statement, how will I incorporate this into the invoicing query (Amount Column)?

Thanks

 

[dhookom]

Create a new module and paste this function into it:

Public Function GetAmount(O_ITV As Variant, Rush24 As Boolean, Rush36 As Boolean, RptPrd As String) As Currency
[green]' Type some comments here that explain the logic and calculations
' include who wrote the function and how it might change in the future[/green]
    Dim curO_ITV As Currency
    Dim curRush24 As Currency
    Dim curRush36 As Currency
    Dim curRptPrdQYE As Currency
    Dim curDefault As Currency

[green]'define return amounts near top of code
'   so they can be easily changed in the
'   future. It would be much better to
'   pull these values from a table rather
'   than hard-coding them
'======================================[/green]
    curO_ITV = 12
    curRush24 = 75
    curRush36 = 62.5
    curRptPrdQYE = 50
    curDefault = 0

[green]'No Numbers below here
'=========================[/green]
    Dim curAmt As Currency
    Select Case True
        Case IsNull(O_ITV) = False
            curAmt = curO_ITV
        Case Rush24
            curAmt = curRush24
        Case Rush36
            curAmt = curRush36
        Case RptPrd Like "*Q*"
            curAmt = curRptPrdQYE
        Case RptPrd Like "*YE*"
            curAmt = curRptPrdQYE
        Case Else
            curAmt = curDefault
    End Select
    GetAmount = curAmt
End Function

Save the module with the name "modBusinessCalcs".
Then, in your query replace the ugly Switch with:

Amount: GetAmount([OSAR_ImagetoVendor],[24HourRush],[1-3DayRush],[ReportingPeriod])

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

Hey dhookom,

I made little changes into the select case statement but it doesn't pull out the right numbers, below is the code:

curO_ITV = 12
curRush24 = 75
curRush36 = 62.5
curRush24Q = 67.5
CurRush36Q = 56.25
curRptPrdQYE = 50
curDefault = 0

'No Numbers below here
'=========================
Dim curAmt As Currency
Select Case True
Case IsNull(O_ITV) = False
curAmt = curO_ITV
Case Rush24
curAmt = curRush24
Case Rush36
curAmt = curRush36
Case RptPrd Like "*Q*" And Rush24 = True
curAmt = curRush24Q
Case RptPrd Like "*Q*" And Rush36 = True
curAmt = CurRush36Q
Case RptPrd Like "*YE*"
curAmt = curRptPrdQYE
Case Else
curAmt = curDefault
End Select
GetAmount = curAmt
End Function


What should happen is if the RptPrd is Q and either 24Rush or 1-3Rush are NOT checked (true), the price should be $45. IF the RptPrd is Q and 24Rush is checked (true) then the price should be $67.50, and if the RptPrd is Q and 1-3Rush is checked (true) then the price should be $56.25.

Thanks for helping me out

 

[iuianj07]

Hello dhookom,

nevermind my question above, I figured out how to re-write the query, if you care here is what I wrote:

curO_ITV = 12
curRush24 = 75
curRush36 = 62.5
curRush24Q = 67.5
CurRush36Q = 56.25
curRptPrdQYE = 50
curRptPrdQ = 45
curDefault = 0

'No Numbers below here
'=========================
Dim curAmt As Currency
Select Case True
Case IsNull(O_ITV) = False
curAmt = curO_ITV
Case RptPrd Like "*YE*" And Rush24 = True
curAmt = curRush24
Case RprPrd Like "*YE*" And Rush36 = True
curAmt = curRush36
Case RptPrd Like "*Q*" And Rush24 = True
curAmt = curRush24Q
Case RptPrd Like "*Q*" And Rush36 = True
curAmt = CurRush36Q
Case RptPrd Like "*YE*"
curAmt = curRptPrdQYE
Case RptPrd Like "*Q*"
curAmt = curRptPrdQ
Case Else
curAmt = curDefault
End Select
GetAmount = curAmt
End Function

thanks again, and I apologize for the Ugly Switch statement. I always try to be a better access developer.

Thanks again