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
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
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.
ASKER
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
Case "AD": GetLArBill = varTotSaving * 0.22: Exit Function
What error are you getting in other cases?
ASKER
#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
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
#error can be result of NULL value of variable varTotSaving.
Add one string in the beginning of function
varTotSaving = nz(varTotSaving,0)
Add one string in the beginning of function
varTotSaving = nz(varTotSaving,0)
ASKER
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
LARMSV-BE-V2.0--2-.zip
There are two files.. which one is relevant?
ASKER
The query is in the FE database, its the one called tblprovidersbill Query
The database keeps referring to a Network share..
ASKER
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.
I would suggest you to revisit the same for its correctness in terms of functionality desired.
ASKER
If I knew how to I would
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Option Compare Database
Open in new window
Hope this helps