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

Puzzling.   Perhaps "product" should be defined in your table as a variant, not a string.   Just a thought.

S

On Saturday, January 14, 2023 at 10:37:02 PM EST, Sean Cooper via groups.io <smcjb=yahoo.com@groups.io> wrote:

I 'hope' I never make that mistake again.

 

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

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.

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

I 'hope' I never make that mistake again.

 

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

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.

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.

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.

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

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.

[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.

Re: [MSAccessProfessionals] Identify non-unique fields in a column

Hi Sean,

Does the expression work? Is it really slow? Is InvoiceNum indexed?

Duane

On Jan 14, 2023, at 5:25 PM, Sean Cooper via groups.io <smcjb=yahoo.com@groups.io> wrote:



Thanks Duane. I created a simple example. The problem is in reality "InvoiceNum" is a large (10 or 12 digit I believe) exchange generated deal number, and the table has thousands of records in it - and I don't know what the deal numbers.

On Friday, January 13, 2023 at 03:11:14 PM CST, Duane Hookom <duanehookom@hotmail.com> wrote:

I expect a DCount() would work. 

Select *, IIf(DCount("*","Your Table Name","InvoiceNum='" & [InvoiceNum] & "'")>1, "Yes","No") As MultipleOrder

FROM …

Duane

On Jan 13, 2023, at 12:30 PM, Sean Cooper via groups.io <smcjb=yahoo.com@groups.io> wrote:



Greetings All.

I know that you can easily use Access to identify "Unique Records" but this is slightly different.

Imagine I have a simple table with CustomerID, ProductID & InvoiceNum but I want create a query with an additional column "MultipleOrder" which identifies if an Invoice has more than one product on it. ie if the InvoiceNum is not unique then it is a multiple order.

Hence the resulting query would like this

Obviously I can do this by creating two queries, the first to count how many records there are for each InvoiceNum and then a second query referencing both the table and the query to identify InvoiceNum with a count greater than one.

Is there a way to do this all in one query though?

Thanks, Sean

Re: [MSAccessProfessionals] Identify non-unique fields in a column

Thanks Paul. Since this is a large dataset I think I will just bite the bullet and use the joined two query method.

On Friday, January 13, 2023 at 12:35:32 PM CST, Paul Baldy <pbaldy@gmail.com> wrote:

You could use a DCount() or subquery in the MultipleOrder field, but on larger datasets the two-query method would likely be much more efficient.  That assumes you were going to use a join on the queries.

Paul

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

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

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

Date 1/13/2023 10:29:56 AM

Subject [MSAccessProfessionals] Identify non-unique fields in a column

Greetings All.

I know that you can easily use Access to identify "Unique Records" but this is slightly different.

Imagine I have a simple table with CustomerID, ProductID & InvoiceNum but I want create a query with an additional column "MultipleOrder" which identifies if an Invoice has more than one product on it. ie if the InvoiceNum is not unique then it is a multiple order.

Hence the resulting query would like this

">

Obviously I can do this by creating two queries, the first to count how many records there are for each InvoiceNum and then a second query referencing both the table and the query to identify InvoiceNum with a count greater than one.

Is there a way to do this all in one query though?

Thanks, Sean

Re: [MSAccessProfessionals] Identify non-unique fields in a column

Thanks Duane. I created a simple example. The problem is in reality "InvoiceNum" is a large (10 or 12 digit I believe) exchange generated deal number, and the table has thousands of records in it - and I don't know what the deal numbers.

On Friday, January 13, 2023 at 03:11:14 PM CST, Duane Hookom <duanehookom@hotmail.com> wrote:

I expect a DCount() would work. 

Select *, IIf(DCount("*","Your Table Name","InvoiceNum='" & [InvoiceNum] & "'")>1, "Yes","No") As MultipleOrder

FROM …

Duane

On Jan 13, 2023, at 12:30 PM, Sean Cooper via groups.io <smcjb=yahoo.com@groups.io> wrote:



Greetings All.

I know that you can easily use Access to identify "Unique Records" but this is slightly different.

Imagine I have a simple table with CustomerID, ProductID & InvoiceNum but I want create a query with an additional column "MultipleOrder" which identifies if an Invoice has more than one product on it. ie if the InvoiceNum is not unique then it is a multiple order.

Hence the resulting query would like this

Obviously I can do this by creating two queries, the first to count how many records there are for each InvoiceNum and then a second query referencing both the table and the query to identify InvoiceNum with a count greater than one.

Is there a way to do this all in one query though?

Thanks, Sean