CODEWITHSUNDEEP
functionms-accessvba
user3073009
user3073009

Reputation: 35

Custom function in Access is returning type conversion error

I am creating a custom function in Access which check 2 fields to see if there is a value in them and returns an answer depending on which field has values.

Half of it works, however I am getting a type conversion error after the query when trying to use the custom function.

There can be 4 results, all of which returns a different answer.

1) Field 1 is Empty & Field 2 is empty = Function returns nothing.

2) Field 1 is Empty & Field 2 has a value = function returns text string "Delete"

3) Field 1 has a Value & Field 2 is empty = function returns text string "New"

4) Field 1 has a value & Field 2 has a value = Check if Values are the same

4a) If value is the same = function returns text string "Match"

4b) If value is different = function returns text string "Update"

I tried to use a case select to determine which field is empty and which field isn't, however I resorted to using If statements as I wasn't getting the results I was after.

Currently the half that does work is when it compares the 2 values and returns "Match" and "Update". The half the doesn't work is when it needs to return "Delete" or "New". It's like it's not able to determine if the fields are empty.

I tried using IsEmpty, IsNull and Field = ""

Here is my code:

Public Function BarcodeCheck(DBTPART As String, SUPPBC As String)

If IsEmpty(DBTPART) And IsEmpty(SUPPBC) Then
    BarcodeCheck = ""
End If

If IsEmpty(DBTPART) And Not IsEmpty(SUPPBC) Then
    BarcodeCheck = "NEW BC"
End If

If Not IsEmpty(DBTPART) And IsEmpty(SUPPBC) Then
    BarcodeCheck = "DELETE BC"
End If

If Not IsEmpty(DBTPART) And Not IsEmpty(SUPPBC) Then
    If DBTPART = SUPPBC Then
        BarcodeCheck = "MATCH"
    Else
      BarcodeCheck = "UPDATE BC"
    End If
End If

End Function

Upvotes: 2

Views: 350

Answers (1)

gembird
gembird

Reputation: 14053

The problem are the parameters of your function which are declared as String

Public Function BarcodeCheck(DBTPART As String, SUPPBC As String)

If the fields DBTPART and SUPPBC can contain Null then you have to declare the parameters in your UDF as Variant. In Visual Basic, the only data type that can contain Null is the Variant. Then in your UDF use IsNull() function to compare field value to Null.

So the function should work when parameters are of type Variant. (And maybe you can use If-Then-ElseIf?)

Public Function BarcodeCheck(DBTPART As Variant, SUPPBC As Variant)

    If IsNull(DBTPART) And IsNull(SUPPBC) Then
        BarcodeCheck = "EMPTY123"

    ElseIf IsNull(DBTPART) And Not IsNull(SUPPBC) Then
        BarcodeCheck = "NEW BC"

    ElseIf Not IsNull(DBTPART) And IsNull(SUPPBC) Then
        BarcodeCheck = "DELETE BC"

    ElseIf Not IsNull(DBTPART) And Not IsNull(SUPPBC) Then
        If DBTPART = SUPPBC Then
            BarcodeCheck = "MATCH"
        Else
          BarcodeCheck = "UPDATE BC"
        End If
    End If

End Function

Upvotes: 3

Related Questions