3/21/2013        2:00 PM Reported by Phyllis Eppig @ West Bond 714-349-9012

Problem Statement: Error being generated by application.

Database:        INVC database (Access 2010)

Steps to reproduce problem (CONFIRMED)

Inventory Control Activities → Allocate and Draw Parts → Allocate Parts This order →

0.13134 - valid order number → Click OK →

Error: Order Product Items (Made Table).Order ID.

Initial Assessment

Underlying query not receiving required parameter.  Need to review program flow and ensure that user inputted parameter is being properly provided to the query.  This is an Access 2003 to Access 2010 migration, so check for compatibility issues, since it worked fine prior to migration.  

Next steps

  1. Rhyss:  Review tonight 03/21/2013 @ 11PM and report findings or solution by 03/22/2013 @1PM.  Completed.
  2. This is a conversion related technical issue.  Further analysis is required to determine the specific cause of error.
  3. Error was being caused by the query [Order Product Items Query (Make Table), because field [Order ID] was being renamed to [Expr1].  This can be caused by programmer error or possibly by conversion.  Resolution was to properly name the field.  
  4. A subsequent error was revealed when NULL values were encountered.  After narrowing down the source of the errors, the old NZZ() function (a custom function developed in-house to deal with NULL values) was replaced with the standard NZ() function and set to default to 0 when NULL value is encountered.
  5. Sandy is now reporting that the results are not matching the version that is on the XP machine.  She did not specify what query she is using, though Frank provided a table from that query.  We must now try to identify the query in INVC database before we can do any further analysis.


Detailed Analysis

  1. [Form_ALLOCATE AND DRAW PARTS FORM]!cmdAllocate_Click()
  1. Query: Order Query (Make Table) - OK
  1. Creates table [This order (Made Table)]
  1. Query: Order Serial Numbers Query (Make Table) - OK
  1. Creates table [Order Serial Numbers (Made Table)]
  1. Query: Order Product Items Query (Make Table)
  1. Creates table [Order Product Items (Made Table)]
  1. This was renaming [Order ID] to [Expr1].  Fixed
  2. New error:  Invalid use of Null - Not previously detected, because the copy Rhyss had contained no data.
  1. Error localized to query [Parts to Allocate This Order (Make Table)] (see 1.g.i.2)
  1. Query: Order Part Items Query (Make Table) - OK
  1. Creates table [Order Part Items (Made Table)] from:
  1. Table - [This Order (Made Table)]
  2. Table - [Order Party Items]
  3. Table - [ALL PARTS]
  4. Table - [Inventory]
  1. Query: Order Special Items Query (Make Table) - OK
  2. Query: Allocate Spare Parts This Order (Update) - OK
  3. Query: Parts To Allocate This Order (Make Table) - ERROR.  Also found a copy of this query dated 10/24/2000.
  1. Creates table [Parts to Allocate This Order (Made Table)] from
  1. QUERY - [Order Product Data This Order]
  1. Table - [Order Product Items (Made Table)]
  1. The query is trying to group by a field called [Order ID] which is not present in this table.
  1. Changed field [Expr1] to [Order ID].  Access changed it back to Expr1!!!
  1. [Order ID] should be a Number (Single) data type based on table [ORDERS].
  2. Problem located in previous queries (see 1.c)
  1. Table - [Orders] JOIN to [Order Product Items (Made Table)] ON [Customer ID]
  2. Table - [Customers] JOIN to [Orders] ON [Order ID]
  1. Expressions: QtyReqd, QtyAvailable, QtyShort had NZ() function added to deal with null values.  Default was set to 0 when NULL encountered.   Invalid use of Null error was caused by field QtyShort.
  2. QUERY - [Bill of Matl, Multiple]  JOIN [Order Product Data This Order] ON [OPDTO.Product No] = [BOM.Model No] - POSSIBLE PROBLEM - Unable to find Model No: 0.
  1. Table - [ALL PARTS]
  2. Table - [PART USAGE]
  3. Table - [MODEL ASSY]
  1. TABLE - [All Parts]
  2. TABLE - [Inventory]
  1. Query: Allocate Parts This Order (Update)
  1. Table - [INVENTORY]
  2. Table - [Parts to Allocate This Order (Made Table)]
  3. Table - [Orders]


Query (Original): Parts to Allocate This order (Make Table)

SELECT DISTINCT [order product data this order].[order id],

                [order product data this order].[product no],

                [order product data this order].quantity,

                [order product data this order].[company name],

                [all parts].[part no],

                [all parts].rev,

                [all parts].description,

                [all parts] ! [category]

                AS Category,

                [all parts].[manuf name],

                [all parts].[manuf p/n],

                [bill of matl, multiple].[extd qty],

                [extd qty] * [quantity]

                AS QtyReqd,

                inventory.qtystock,

                inventory.qtyallocated,

                Iif(Nzz([qtystock]) >= Nzz([qtyallocated]),                 ← Custom method NZZ() fails to handle NULL values

                Nzz([qtystock]) - Nzz([qtyallocated]

                                  ), 0)  AS

                QtyAvailable,

                Iif([category] = "assy", 0, Iif(

                Cint([qtyavailable]) >= Cint([qtyreqd]), Cint(0)

                                            ,

                                            Cint([qtyavailable]) - Cint(

                                            [qtyreqd]))) AS QtyShort,

                [qtyreqd]

                AS QtyToAllocate,

                DATE()

                AS Today

INTO   [parts to allocate this order (made table)]

FROM   ([order product data this order]

        INNER JOIN ([all parts]

                    INNER JOIN [bill of matl, multiple]

                            ON [all parts].[part no] =

                               [bill of matl, multiple].[part no])

                ON [order product data this order].[product no] =

                   [bill of matl, multiple].[model no])

       INNER JOIN inventory

               ON [all parts].[part no] = inventory.[part no]

GROUP  BY [order product data this order].[order id],

          [order product data this order].[product no],

          [order product data this order].quantity,

          [order product data this order].[company name],

          [all parts].[part no],

          [all parts].rev,

          [all parts].description,

          [all parts] ! [category],

          [all parts].[manuf name],

          [all parts].[manuf p/n],

          [bill of matl, multiple].[extd qty],

          inventory.qtystock,

          inventory.qtyallocated

HAVING ( ( ( [all parts].rev ) <> "obs"

            OR ( [all parts].rev ) IS NULL )

         AND ( ( [all parts] ! [category] ) <> "assy"

               AND ( [all parts] ! [category] ) <> "xparts" ) )

ORDER  BY [all parts].[part no]

WITH OWNERACCESS OPTION;


Query (Modified): Parts to Allocate This order (Make Table)

SELECT DISTINCT [order product data this order].[order id],

                [order product data this order].[product no],

                [order product data this order].quantity,

                [order product data this order].[company name],

                [all parts].[part no],

                [all parts].rev,

                [all parts].description,

                [all parts] ! [category]                      AS Category,

                [all parts].[manuf name],

                [all parts].[manuf p/n],

                [bill of matl, multiple].[extd qty],

                Nz([extd qty], 0) * Nz([quantity], 0)         AS QtyReqd,

                inventory.qtystock,

                inventory.qtyallocated,

                Iif(Nz([qtystock], 0) >= Nz([qtyallocated], 0),                         ← Standard method Nz() handles NULL values.

                Nz([qtystock], 0) - Nz([qtyallocated], 0), 0) AS QtyAvailable,

                Iif([category] = "assy", 0, Iif(

                Nz(Cint([qtyavailable])) >= Nz(Cint([qtyreqd])),

                                            Cint(0),

                Cint(Nz(

                     [qtyavailable])) - Cint(

                                            Nz([qtyreqd]))))  AS QtyShort,

                [qtyreqd]                                     AS QtyToAllocate,

                DATE()                                        AS Today

INTO   [parts to allocate this order (made table)]

FROM   ([order product data this order]

        INNER JOIN ([all parts]

                    INNER JOIN [bill of matl, multiple]

                            ON [all parts].[part no] =

                               [bill of matl, multiple].[part no])

                ON [order product data this order].[product no] =

                   [bill of matl, multiple].[model no])

       INNER JOIN inventory

               ON [all parts].[part no] = inventory.[part no]

GROUP  BY [order product data this order].[order id],

          [order product data this order].[product no],

          [order product data this order].quantity,

          [order product data this order].[company name],

          [all parts].[part no],

          [all parts].rev,

          [all parts].description,

          [all parts] ! [category],

          [all parts].[manuf name],

          [all parts].[manuf p/n],

          [bill of matl, multiple].[extd qty],

          inventory.qtystock,

          inventory.qtyallocated

HAVING ( ( ( [all parts].rev ) <> "obs"

            OR ( [all parts].rev ) IS NULL )

         AND ( ( [all parts] ! [category] ) <> "assy"

               AND ( [all parts] ! [category] ) <> "xparts" ) )

ORDER  BY [all parts].[part no]

WITH OWNERACCESS OPTION;

Show Parts Data and Status Query

Possible source queries

INVC2000

SELECT [Parts To Allocate This Order (Made Table)].[Order ID], [ALL PARTS].[Part No], [ALL PARTS].Description, INVENTORY.SellPrice, [Parts To Allocate This Order (Made Table)].QtyStock

FROM ([ALL PARTS] INNER JOIN INVENTORY ON [ALL PARTS].[Part No] = INVENTORY.[Part No]) INNER JOIN [Parts To Allocate This Order (Made Table)] ON INVENTORY.[Part No] = [Parts To Allocate This Order (Made Table)].[Part No]

WHERE ((([Parts To Allocate This Order (Made Table)].[Order ID])=0.13134));


Sub Assemblies of This Order Report (Report)

        Show Sub Assemblies of This Order (Query)