VBA & Macro Problems

How to Preserve Decimal Places When Inserting VBA Recordsets into a Table

Maira Mehtab Oct 10, 2026 868 views

Question details

The user is experiencing a loss of decimal precision when inserting data from a VBA recordset into a table, despite setting the destination field's display format to show decimal places.

How to Preserve Decimal Places When Inserting VBA Recordsets into a Table

Product: VBA Spreadsheets
Device & OS: not provided
Scenario: Running a VBA script using an INSERT INTO statement to transfer values from a recordset into a destination table.
Observed behavior: Decimal values are truncated or discarded upon insertion because the destination field is constrained by an integer data type, turning fractional numbers into whole numbers.

Before you start

Before modifying table structures or data types, create a backup copy of your database or spreadsheet to prevent accidental data loss during schema modifications.

Solution 1Recommended

Modify the Destination Field Data Type

The most common reason for lost decimals is an incorrect underlying field data type, such as Integer or Long, which cannot store fractional values regardless of display settings.

Setting the decimal places property to '2' on an Integer field only changes how the data is visually presented, not how it is stored. To retain fractional values from your VBA recordset, the destination field must support decimals.

Open table in design mode

Open your destination table or spreadsheet in Design View so you can modify the underlying schema.

Locate the target field

Select the specific field or column where the VBA recordset is inserting the numeric values.

Change data type to a decimal-compatible format

Look at the 'Data Type' or 'Field Size' property. If it is set to Integer or Long Integer, change it to Single, Double, or Decimal.

Set scale and precision

If you selected the Decimal data type, ensure you define the Scale (the number of digits to the right of the decimal point) and Precision accurately for your needs.

Save and re-run

Save the table design changes and execute your VBA INSERT INTO statement again to verify the decimal places are now preserved.

Display Format vs Storage: Remember that a Number field configured as a whole-number type will permanently discard decimal values upon insertion. Always modify the Field Size or Data Type rather than just adjusting the cell formatting.

Solution 2

Ensure Proper Variable Formatting in the VBA SQL String

Sometimes, constructing the SQL INSERT string in VBA can implicitly cast numbers or use local decimal separators that cause truncation.

Advanced Macro Support in WPS Office

Handle VBA Recordsets Effectively with WPS Spreadsheets

WPS Spreadsheets offers robust VBA (Visual Basic for Applications) support, allowing you to manage recordsets, execute SQL commands, and format data types accurately without losing decimal precision.

1. Open the VBA Editor: Launch WPS Spreadsheets, navigate to the 'Developer' tab, and click 'Visual Basic' to open the built-in VBA Editor.
2. Define Precise Variables: Declare your variables as 'Double' or 'Variant' to ensure decimal precision is maintained in memory during recordset operations.
3. Execute Data Insertion: Run your recordset insertion scripts to write precise data directly to your formatted sheets without truncation.

Fully compatible with Microsoft Excel macro files (.xlsm) and VBA syntax.Accurate data type management for precise numeric and decimal storage.Lightweight and fast execution for heavy data processing tasks.Free to use with an intuitive, familiar interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my table show two decimal places but all inserted values end in .00?

Your destination table field is likely set to an Integer data type, which discards decimals, while the display format is set to force two decimal places. Change the data type to Double or Decimal to store the actual fractional values.

Can I use VBA to change a table field's data type automatically?

Yes, you can execute a Data Definition Language (DDL) SQL statement via VBA (for example, executing 'ALTER TABLE TableName ALTER COLUMN FieldName DOUBLE') to modify the field type programmatically before inserting records.

Does the Single data type preserve exact decimal values perfectly?

The Single data type uses floating-point arithmetic and may introduce minor rounding discrepancies due to how computers store fractions. For exact precision, especially with financial data, it is highly recommended to use Currency or Decimal data types instead.

Excel & Data Problems