Help fixing a Calculated Data Type Error (1 Viewer)

marseneault · Feb 4, 2019

Hello,
So I started creating a data base that has a number of calculated values to get Profit, Margins and Overhead.
The Excel Spreadsheet that I get the data from includes decimal points, however, when I imported the data, it created it to be a long interger.

Since Integers do not have decimal points, many of my calculated values are not accurate and I have Tables, Querries and Reports that All use this data.

I tried changing the data type in my tables, querries and reports but now I my report shows an error and will not run.

I would have thought that by following the trail and updating all the data types to be the same would have fixed the issue, but it has not. Any ideas on how to repair this?

theDBguy · Feb 4, 2019

Hi. You are correct to change the data types but which one did you change them to?

marseneault · Feb 4, 2019

I changed them from Long Integer to Double, with 2 decimal points in the currency Format.
When I do this I also change the corrisponding data types in all my tables, queries and reports. That is when I get the error.

Micron · Feb 4, 2019

A common approach to the issue of Excel data types getting mucked up is to link the sheets as tables, then run update/append queries using the linked tables on Access tables. The Access tables should handle the conversion as long as the field data types are correct. This assumes the data on the Access side needs to be updated or appended to later, as the linked sheets can't be modified in Access.
You probably broke something doing it manually, but you created a db backup before doing major modifications, right?

marseneault · Feb 4, 2019

I am actually using this method to get the data into my report. I have dynamically linked several Excel Spreadsheets. I append the records using an append query and this is is were the decimal places disappear.

Micron · Feb 4, 2019

Well, I don't understand the part about 'changing the data types' in queries or reports. You can change the formats in those places and that can alter the way data is displayed, but you can't set data types in form/report controls or query fields.

If a number field in your table is double with 2 decimal places, you should only see 2 - unless you activate that field. If there are more than 2 places in the data, you'll see them. I've found that unless you set the format to Fixed & 2 places, the data can be something other than what's displayed. However, that doesn't explain why you don't see any decimal places in the report, so the report control format must be wrong, assuming the table field data type is correct.

marseneault · Feb 4, 2019

Thanks for your advice, Since the issue seamed to be stemming from an import query and calculated values in the append table, I decided to skip that data calculation all together and create a new table with the correct data type selected. Now my calculations all work and i have no rounding errors which is the issue that I was dealing with.

The New issue is that I have my report looking for a parameter that is no longer there

Enter Parameter Value
(Access_JobsiteTable.%Profit)

Is there a way to get rid of these errors when the structure of a table or query changes, other than deleting the report/Form and starting over?

Micron · Feb 4, 2019

1) you should NOT have spaces or special characters in any object name such as % (field, report, form, control, etc).

See http://allenbrowne.com/AppIssueBadWord.html
http://access.mvps.org/access/general/gen0012.htm
https://access-programmers.co.uk/forums/showthread.php?t=225837

2) any changes you made to a data source reference (e.g. table or field) will cause the current issue if you don't make the corresponding change in the form/report controls
3) First time you mentioned 'calculated fields'. Up to now I think only calculations were mentioned. As a general rule you shouldn't be storing calculations in tables to begin with if they're derived from Access data. Doing the calculations in the table itself is probably worse.

Help fixing a Calculated Data Type Error (1 Viewer)

marseneault

Registered User.

theDBguy

I’m here to help

marseneault

Registered User.

Micron

AWF VIP

marseneault

Registered User.

Micron

AWF VIP

marseneault

Registered User.

Micron

AWF VIP

Similar threads

Users who are viewing this thread