CODEWITHSUNDEEP
ms-accesscobol
Buggabill
Buggabill

Reputation: 13901

Calculated columns in Access 2003 have null value when inserting into new table

I have a make table query where some of the columns are calculated. An example of how one of those columns looks is as follows:

SQFTCost: (([SUPPLY_MASTER]![LAST_COST]+[SUPPLY_MASTER]![FREIGHT_COST])/[SUPPLY_MASTER]![SQFT_PER_CTN])

In this case, LAST_COST is a decimal with a precision of 9 and a scale of 3. FREIGHT_COST is is a decimal with a precision of 8 and a scale of 3, and SQFT_PER_CTN is a decimal with a precision of 7 and a scale of 3.

Whenever I run the make table query, that column and all the others like it are filled with nulls. I know that they are actually null, because I tested that in a routine that I wrote.

table results

However, if I change the query to a SELECT query, all is well. The values are correct.

table results

Does anyone have any idea what can be done to fix this? I am using Access 2003.

Upvotes: 0

Views: 296

Answers (2)

Buggabill
Buggabill

Reputation: 13901

I have come across the correct answer to this issue. It is the result of a sometimes flaky ODBC driver that we are using here. Our data rests in COBOL files that are in the Vision file format. The trouble is coming from using fields that are defined in the xfds as REDEFINES of other fields in the file. Using the original field fixes the issue.

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

Just a few suggestions:

  1. Try adding a cLng() or something equivalent in front of your expressions, to force a well defined data type
  2. I avoid Make Table queries, preferring Append queries. Just make an "template" table properly set up, and use a copy with Append queries. It's the only way to have a clean design.

Upvotes: 3

Related Questions