Link to home
Start Free TrialLog in
Avatar of seamus99
seamus99

asked on

Module Syntax in Access

I want to modify the following module to include an additional condition.  

So if CASE = "AL" and Billtype = "FL" GetLArBill = $9.50   else Case = "AL" and billtype is not "FL" GetLArBill = varTotSaving * 0.22.

Im sure my syntax is wrong.  



Function GetLArBill(strCustomerID As String, Billtype As String, varBill As Double, varTotSaving As Double) As Double

Select Case strCustomerID
          Case "AD": GetLArBill = varTotSaving * 0.22: Exit Function

          Case "AH"
                  Select Case varTotSaving
                       Case Is < 10001: GetLArBill = 1500: Exit Function
                       Case Is < 20001: GetLArBill = 3000: Exit Function
                       Case Is < 30001: GetLArBill = 4500: Exit Function
                       Case Is < 40001: GetLArBill = 6000: Exit Function
                       Case Is < 50001: GetLArBill = 7500: Exit Function
                       Case Is < 60001: GetLArBill = 9000: Exit Function
                       Case Is < 70001: GetLArBill = 10500: Exit Function
                       Case Is < 80001: GetLArBill = 12000: Exit Function
                       Case Is < 90001: GetLArBill = 13375: Exit Function
                       Case Is < 100001: GetLArBill = 13750: Exit Function
                       Case Is < 125001: GetLArBill = 15625: Exit Function
                       Case Is < 150001: GetLArBill = 18750: Exit Function
                       Case Is < 175001: GetLArBill = 19650: Exit Function
                       Case Is < 200001: GetLArBill = 22500: Exit Function
                       Case Is < 250001: GetLArBill = 28125: Exit Function
                       Case Is < 300001: GetLArBill = 33750: Exit Function
                       Case Is < 400001: GetLArBill = 45000: Exit Function
                       Case Is < 500001: GetLArBill = 56250: Exit Function
                       Case Is < 750001: GetLArBill = 84375: Exit Function
                       Case Is > 750000: GetLArBill = 123000: Exit Function
                  End Select

         Case Else
                 GetLArBill = varTotSaving * 0.25: Exit Function
End Select

End Function
Avatar of pritamdutt
pritamdutt
Flag of India image

Check this out


Option Compare Database

Function GetLArBill(strCustomerID As String, Billtype As String, varBill As Double, varTotSaving As Double) As Double

Select Case strCustomerID
Case "AL":
    Select Case Billtype
        Case "FL": GetLArBill = 9.5: Exit Function
        Case Else: GetLArBill = varTotSaving * 0.22: Exit Function
    End Select
        
        
          Case "AD": GetLArBill = varTotSaving * 0.22: Exit Function

          Case "AH"
                  Select Case varTotSaving
                       Case Is < 10001: GetLArBill = 1500: Exit Function
                       Case Is < 20001: GetLArBill = 3000: Exit Function
                       Case Is < 30001: GetLArBill = 4500: Exit Function
                       Case Is < 40001: GetLArBill = 6000: Exit Function
                       Case Is < 50001: GetLArBill = 7500: Exit Function
                       Case Is < 60001: GetLArBill = 9000: Exit Function
                       Case Is < 70001: GetLArBill = 10500: Exit Function
                       Case Is < 80001: GetLArBill = 12000: Exit Function
                       Case Is < 90001: GetLArBill = 13375: Exit Function
                       Case Is < 100001: GetLArBill = 13750: Exit Function
                       Case Is < 125001: GetLArBill = 15625: Exit Function
                       Case Is < 150001: GetLArBill = 18750: Exit Function
                       Case Is < 175001: GetLArBill = 19650: Exit Function
                       Case Is < 200001: GetLArBill = 22500: Exit Function
                       Case Is < 250001: GetLArBill = 28125: Exit Function
                       Case Is < 300001: GetLArBill = 33750: Exit Function
                       Case Is < 400001: GetLArBill = 45000: Exit Function
                       Case Is < 500001: GetLArBill = 56250: Exit Function
                       Case Is < 750001: GetLArBill = 84375: Exit Function
                       Case Is > 750000: GetLArBill = 123000: Exit Function
                  End Select

         Case Else
                 GetLArBill = varTotSaving * 0.25: Exit Function
End Select

End Function

Open in new window


Hope this helps
Avatar of seamus99
seamus99

ASKER

While that works when  a Record has a Bill Type of "FL" all other records are now showing an error.  SO the calculation is not working.  
So in other words if the BillType is blank I want it to calculate the other Cases like:

Case "AD": GetLArBill = varTotSaving * 0.22: Exit Function
What error are you getting in other cases?
#error
I have executed the Select case with a dummy function and I am not receiving any error. Please check what else could be causing the error.

Please upload mdb file for review if required

Function CallGetLAr()
    MsgBox GetLArBill("AL", "FL", 1#, 1#) ' Returns 9.5
    MsgBox GetLArBill("AL", "", 1#, 1#)   ' Returns 0.22
    MsgBox GetLArBill("AD", "FL", 1#, 1#) ' Returns 0.22
    MsgBox GetLArBill("AH", "FL", 1#, 150000) ' Returns 18750
    MsgBox GetLArBill("H", "FL", 1#, 1#) 'Returns 0.25
    
End Function
Function GetLArBill(strCustomerID As String, Billtype As String, varBill As Double, varTotSaving As Double) As Double

Select Case strCustomerID
    Case "AL":
        Select Case Billtype
            Case "FL": GetLArBill = 9.5: Exit Function
            Case Else: GetLArBill = varTotSaving * 0.22: Exit Function
        End Select
    Case "AD": GetLArBill = varTotSaving * 0.22: Exit Function
    
    Case "AH":
        Select Case varTotSaving
            Case Is < 10001: GetLArBill = 1500: Exit Function
            Case Is < 20001: GetLArBill = 3000: Exit Function
            Case Is < 30001: GetLArBill = 4500: Exit Function
            Case Is < 40001: GetLArBill = 6000: Exit Function
            Case Is < 50001: GetLArBill = 7500: Exit Function
            Case Is < 60001: GetLArBill = 9000: Exit Function
            Case Is < 70001: GetLArBill = 10500: Exit Function
            Case Is < 80001: GetLArBill = 12000: Exit Function
            Case Is < 90001: GetLArBill = 13375: Exit Function
            Case Is < 100001: GetLArBill = 13750: Exit Function
            Case Is < 125001: GetLArBill = 15625: Exit Function
            Case Is < 150001: GetLArBill = 18750: Exit Function
            Case Is < 175001: GetLArBill = 19650: Exit Function
            Case Is < 200001: GetLArBill = 22500: Exit Function
            Case Is < 250001: GetLArBill = 28125: Exit Function
            Case Is < 300001: GetLArBill = 33750: Exit Function
            Case Is < 400001: GetLArBill = 45000: Exit Function
            Case Is < 500001: GetLArBill = 56250: Exit Function
            Case Is < 750001: GetLArBill = 84375: Exit Function
            Case Is > 750000: GetLArBill = 123000: Exit Function
        End Select
    Case Else
        GetLArBill = varTotSaving * 0.25: Exit Function
End Select

End Function

Open in new window

#error can be result of NULL value of variable varTotSaving.
Add one string in the beginning of function
varTotSaving = nz(varTotSaving,0)
IF you run Query tblprovidersbill Query you will see that what is being returned from the Modile is #error unless there is a Billtype value.


LARMSV-BE-V2.0--2-.zip
There are two files.. which one is relevant?
The query is in the FE database, its the one called tblprovidersbill Query
The database keeps referring to a Network share..
Ahh that cause the FE is linked to the BE. do u need me to relink or will u do it
I was going through your GetLArBill Function and there seems to be some issue with the Select Case Logic.

I would suggest you to revisit the same for its correctness in terms of functionality desired.


If I knew how to I would
ASKER CERTIFIED SOLUTION
Avatar of pritamdutt
pritamdutt
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial