How to Remove Decimal Points From Fixed-Width Access Text Exports
Question details
The user needs to prevent unwanted decimal points from appearing in phone numbers and age values when exporting data to a fixed-width text file in Access.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Exporting database tables or queries to fixed-width text formats for external use or system integration.
- Observed behavior
- Numeric fields such as phone numbers are exported with unwanted decimal points, which breaks the expected fixed-width layout or data format.
Before modifying table data types, ensure you create a backup of your Access database, as altering field properties can permanently change existing records.
Change the Field Data Type to Short Text
Convert identifier fields like phone numbers to Short Text, as they are not used for mathematical calculations and should not be treated as numeric values.
By default, numeric data types may append decimal points during a text export to preserve mathematical precision. Changing the field to Short Text ensures the data is exported exactly as it appears, without formatting alterations.
Right-click your target table in the Navigation Pane and select 'Design View'.
Locate the field causing the issue (e.g., Phone Number). Click the 'Data Type' column next to it and select 'Short Text' from the drop-down menu.
Save the changes to your table. Proceed to export the table as a fixed-width text file; the decimals will no longer appear.
Use an Export Query with Formatting Functions
If you must keep the numeric data type in your table, create a query to explicitly format the data as text before exporting.
Manage Your Data Exports Easily with WPS Office
While Microsoft Access handles complex relational databases, you can seamlessly process, format, and export tabular data—including text and CSV files—using WPS Spreadsheet. WPS Office is a lightweight, fully compatible alternative to Microsoft Office that makes data formatting effortless.
Frequently Asked Questions
Why do phone numbers get exported with decimal points in Access?
When a field is set to a Number data type, Access may append decimal points during a fixed-width text export to preserve the numeric precision of the data type. Storing these values as text prevents this behavior.
Can I remove decimal points from age values without changing the table's data type?
Yes. You can create a Select Query and use the Format() function, such as Format([Age], "00"), to convert the numeric age into a formatted text string without decimals specifically for the export.
Will changing a Number field to Short Text delete my existing data?
Generally, changing a field from Number to Short Text will preserve your existing digits. However, any leading zeros that were previously stripped out by the Number format will not automatically return unless you run an update query to reformat them.
