Append Query Syntax error

[RandomNeeds]

This was really helpful. This is what I ended up with. I will do more detailed testing tomorrow, but I believe that all the scenarios I just ran come out with the intended result. Thanks for sticking with me!

Public Function GetSwKey(varKeyOrderType As Variant, _
                         varNewKeyNumber As Variant, _
                         varPartNumber As Variant, _
                         varAlohaKey As Variant) As Variant
    If varKeyOrderType = "NEWKEYSHIP" Then
        GetSwKey = varNewKeyNumber
    ElseIf varKeyOrderType = "REPLACESHIP" Then
        GetSwKey = varNewKeyNumber
    ElseIf varPartNumber Like "H40*" Then
        If varAlohaKey > 0 And IsNull(varNewKeyNumber) = True Then
            GetSwKey = varAlohaKey
        ElseIf varkeynumber > 0 Then
            GetSwKey = varNewKeyNumber
        End If
    End If
End Function

 

[vbaInet]

Pleased to hear!