Re: [MSAccessProfessionals] Problem with Null Values in a Function

No problem Sean.  We've all banged our heads against that one at some point.  :)

Paul

------ Original Message ------

From "Sean Cooper via groups.io" <smcjb=yahoo.com@groups.io>

To "msaccessprofessionals@groups.io" <msaccessprofessionals@groups.io>

Date 1/14/2023 4:01:47 PM

Subject Re: [MSAccessProfessionals] Problem with Null Values in a Function

Your right Paul. Since its a string I had it declared as a String, of course the null values violate this declaration. Changing this to Variant fixed it. Thank you very much.

What a waste of several of hours!

Sean

On Saturday, January 14, 2023 at 05:52:05 PM CST, Paul Baldy <pbaldy@gmail.com> wrote:

You'd have to declare product as a Variant:

Function myfunction(product As Variant, price)

I assume you've declared it as something else, because without declaration Variant is the default.

Paul

------ Original Message ------

From "Sean Cooper via groups.io" <smcjb=yahoo.com@groups.io>

To "msaccessprofessionals@groups.io" <msaccessprofessionals@groups.io>

Date 1/14/2023 3:49:19 PM

Subject [MSAccessProfessionals] Problem with Null Values in a Function

I have a table that contains stock and bond prices AS A STRING. The stock prices should be in decimals but the bond prices are in 32nds. So a stock price of "100.01" means 100.01 but a bond price of "100'01" means 100 & 1/32nd. In reality its more complicated than that as some bonds are priced in 32nds, some 64ths, some 128ths etc etc. So I need to not only identify whether each record is for a bond versus a stock but also what the bond is. To do this I have a column entitled product. 

So I have a custom function of form (pseudo code)

Function myfunction(product, price)

    If product = stock then myfunction = val(price)

    If product = bond then myfunction = a formula

End Function

This function works well, as long as the field [Product] is not null. When it is Null I want it to use the price as if it was a decimal. Unfortunately when it is Null it always returns #Error. Even if I check for a Null and try to handle Null cases differently (or check for Len = 0).

eg the following does not work

Function myfunction(product, price)

    If isnull(product) then myfunction = val(price)

End Function

To simplify the problem if I just create the simple function (which doesn't really perform much)

Function myfunction(product, price)

    myfunction = val(price)

End Function

This function does not actually reference the [Product] in anyway, but I get #Errors for the records with the Null [Product]

But if delete the reference to [Product] entirely, so the function is

Function myfunction(price)

    myfunction = val(price)

End Function

It works perfectly. 

What am I missing here? How can I reference [Product] and not get #Error for records where it is Null.