Custom function currency getting # Error

[maacky99]

I have a custom function for a sliding scale markup on parts.

If I make it As Currency I get an #error if PARTS is null or 0. If I make it As Variant, I can't get it to show as currency. Here's the function:

Public Function partsmarkup(parts As Variant) As Variant
If IsNull(parts) Then
partsmarkup = Null
If parts > 0 And parts <= 200 Then
partsmarkup = parts * 2
Else
If parts >= 201 And parts <= 300 Then
partsmarkup = parts * 1.8
Else
If parts >= 301 And parts <= 400 Then
partsmarkup = parts * 1.7
Else
If parts >= 401 And parts <= 500 Then
partsmarkup = parts * 1.6
Else
If parts >= 501 And parts <= 750 Then
partsmarkup = parts * 1.5
Else
partsmarkup = parts * 1.4
End If
End If
End If
End If
End If
End If
End Function

Thanks!

 

[izyrider]

null is going to be an issue!
check for it before calling the function

if isnull(parts) then
'do something
else
myVar = partsmarkup(parts)
endif


0 should be no problem.

faster (and tidier) code would be:

Public Function partsmarkup(parts As Currency) As Currency

    Select Case parts

        Case Is <= 200
            partsmarkup = parts * 2
            
        Case Is <= 300
            partsmarkup = parts * 1.8
            
        Case Is <= 400
            partsmarkup = parts * 1.7
            
        Case Is <= 500
            partsmarkup = parts * 1.6
            
        Case Is <= 750
            partsmarkup = parts * 1.5
            
        Case Else
            partsmarkup = parts * 1.4
            
    End Select
    
End Function

izy

 

[maacky99]

Thanks for your help izy!

The function itself worked great and is much easier and smaller the the one I came up with. However I am a bit confused on the part about addressing nulls. When you say to check for it before calling the function - do I do that in the module or the query that I am using the function in? Also what would you or others suggest I put where you said 'do something', I don't know the proper code/syntax for ignore or don't do anything.

Thanks again.

 

[ghudson]

if isnull(parts) or parts = "" then
     msgbox "You must have a valid parts number!"
     exit sub
else
     myVar = partsmarkup(parts)
end if

 

[maacky99]

Thanks for the quick reply - but let me clairfy the issue a bit.

Parts is the cost of the parts to do a job (from the estimates table). The main table we use is the repairs table - they are joined by a repair number.

Nulls exist because we can have an entry in the repairs table (created when the job was opened) but no entries in the estimates table yet. I was thrown into this db project, I know it's not set up properly but it's what I need to work with.

So it's not a matter of an invalid part number or someone entering something improperly.

maacky

 

[izyrider]

if i understand, you are trying to use this function in a query.

how about something in your query like:

iif(isnull(parts), null, partsmarkup(parts))

take care: this is seriously untested...

izy

 

[maacky99]

That worked beautifully!

Thanks for all your help.

maacky