Still Struggling with If, Then, Else statement

  • Thread starter Thread starter kbelo
  • Start date Start date
K

kbelo

I am a newbie trying hard to create a function...so far
this is working for me:
Function BillingCode(totalvisits)
If totalvisits = 4 Then
BillingCode = "G0317"
ElseIf totalvisits = 3 Then
BillingCode = "G0318"
ElseIf totalvisits = 2 Then
BillingCode = "G0318"
ElseIf totalvisits = 1 Then
BillingCode = "G0319"
ElseIf totalvisits = 0 Then
BillingCode = 0
End If
End Function

However I would like to add another line(?variable) so
If totalvists = 1 And Day (FieldName) = "PD" Then
BillingCode = "G0323"
Everything I have tried, just has not worked. Thanks for
your help!
Kathy
 
Kathy,

What you have seems fine to me. All you need to add is an addition (nested)
If...Then statement, plus you need to add 'fieldname' to the function
declaration. Declaring datatypes will help too.

Public Function BillingCode(totalvisits As Integer, _
fieldname As String) As String '+++++++++
If totalvisits = 4 Then
BillingCode = "G0317"
ElseIf totalvisits = 3 Then
BillingCode = "G0318"
ElseIf totalvisits = 2 Then
BillingCode = "G0318"
ElseIf totalvisits = 1 Then
'++++++++++++++++++++
If fieldname = "PD" Then
BillingCode = "G0323"
Else
BillingCode = "G0319"
End If
'++++++++++++++++++++
ElseIf totalvisits = 0 Then
BillingCode = 0
End If
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
[email protected] said:
I am a newbie trying hard to create a function...so far
this is working for me:
Function BillingCode(totalvisits)
If totalvisits = 4 Then
BillingCode = "G0317"
ElseIf totalvisits = 3 Then
BillingCode = "G0318"
ElseIf totalvisits = 2 Then
BillingCode = "G0318"
ElseIf totalvisits = 1 Then
BillingCode = "G0319"
ElseIf totalvisits = 0 Then
BillingCode = 0
End If
End Function

However I would like to add another line(?variable) so
If totalvists = 1 And Day (FieldName) = "PD" Then
BillingCode = "G0323"
Everything I have tried, just has not worked. Thanks for
your help!
Kathy
Click to expand...

This itemized "If" structure is tailor-made for implementation as a
Select Case structure instead. Try this:

'-----------
Function BillingCode(totalvisits, dayvalue)

Select Case totalvisits
Case 4
BillingCode = "G0317"
Case 3
BillingCode = "G0318"
Case 2
BillingCode = "G0318"
Case 1
If dayvalue = "PD" Then
BillingCode = "G0323"
Else
BillingCode = "G0319"
End If
Case 0
BillingCode = "0"
Case Else
BillingCode = Null
End Select

End Function

'-----------

Note that I wasn't sure what you meant by "Day (FieldName)", so I
redefined your function to accept a second argument, "dayvalue", which
you would have to pass to the function along with "totalvisits". Also,
you didn't include any catch-all "else" clause, to handle a situation in
which totalvisits is not in the range 0-4, so I took the liberty of
adding one. However, I don't actually know what should be returned in
such a case. By its definition, your function returns a Variant type,
so I thought it would be okay to return Null. However, that may
actually be invalid in your situation.
 
Kathy,

Just another thought... Perhaps a simpler way of selecting return values
that depend on a single input, is to use the Choose function:
Choose(myValue, "one", "two", "three", "four", "five")
'If myValue is none of the available options, the function returns Null.

So in your case, you could use this:
Public Function BillingCode(totalvisits As Integer, _
fieldname As String) As String

BillingCode = Nz(Choose(totalvisits, _
IIf(fieldname = "PD", "G0323", "G0319"), _
"G0318", "G0318", "G0317"), _
"0")
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Further to the other suggestions:

Down the track, it might be worth storing your numbers and codes in a table,
so you can change them easily. For example:

tblBillingData
total_visits billing_code
(integer) (text)
4 G0317
3 G0318
etc.

Then your function could get the code from the table at runtime:

dim vCode as variant
vCode = dlookup ("billing_code", "tblBillingData", "total_visits=" &
totalvisits)
if isnull (vCode) then
msgbox "no record found!"
else
msgbox "code = " & vCode
endif

The advantage of that approach, is that it would be easier to add new codes
and change existing ones.

HTH,
TC
 
Back
Top