• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

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
0
seamus99
Asked:
seamus99
  • 7
  • 7
1 Solution
 
pritamduttCommented:
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
0
 
seamus99Author Commented:
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.  
0
 
seamus99Author Commented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
pritamduttCommented:
What error are you getting in other cases?
0
 
seamus99Author Commented:
#error
0
 
pritamduttCommented:
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

0
 
als315Commented:
#error can be result of NULL value of variable varTotSaving.
Add one string in the beginning of function
varTotSaving = nz(varTotSaving,0)
0
 
seamus99Author Commented:
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
0
 
pritamduttCommented:
There are two files.. which one is relevant?
0
 
seamus99Author Commented:
The query is in the FE database, its the one called tblprovidersbill Query
0
 
pritamduttCommented:
The database keeps referring to a Network share..
0
 
seamus99Author Commented:
Ahh that cause the FE is linked to the BE. do u need me to relink or will u do it
0
 
pritamduttCommented:
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.


0
 
seamus99Author Commented:
If I knew how to I would
0
 
pritamduttCommented:
Hi,

Please find new code to be used for modGetLarBill
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 = 7.5: Exit Function
                Case Else: GetLArBill = varTotSaving * 0.25: 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



and updated  tblprovidersbill Query

SELECT tblRevcodeBill.ClaimID, Sum(tblRevcodeBill.OriginalBill) AS SumOfOriginalBill, Sum(tblRevcodeBill.FeeReduction) AS SumOfFeeReduction, Sum(tblRevcodeBill.AdditionalReduction) AS SumOfAdditionalReduction, Sum(tblRevcodeBill.Settlementoffer) AS SumOfSettlementoffer, Sum(Nz([tblrevcodebill.feereduction])+Nz([tblrevcodebill.additionalreduction])+Nz([tblrevcodebill.Settlementoffer])) AS TotalSavings, Customers.CustomerID, GetLarBill([CustomerID],nz([billtype]),[sumoforiginalbill],[TotalSavings]) AS LarBill, Sum(Nz([tblrevcodebill.originalbill])-Nz([tblrevcodebill.feereduction])-Nz([tblrevcodebill.additionalreduction])-Nz([tblrevcodebill.settlementoffer])) AS RevisedBill, tblClaim1.BILLTYPE
FROM tblSettlem, Customers INNER JOIN (tblRevcodeBill INNER JOIN tblClaim1 ON tblRevcodeBill.ClaimID = tblClaim1.ClaimID) ON Customers.ID = tblClaim1.ID_Cust
GROUP BY tblRevcodeBill.ClaimID, Customers.CustomerID, tblClaim1.BILLTYPE
ORDER BY tblRevcodeBill.ClaimID;

Open in new window


No more errors now!

Hope this helps!
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now