How to Extract Decimal Values from an Access Long Text Field
Question details
The user needs to locate specific numeric values with comma decimal separators (e.g., 0,32) associated with a parameter in a Long Text field, extract them, and convert them to proper numeric formats.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Cleaning and extracting parameter-based numerical data embedded inside Long Text fields in an Access database.
- Observed behavior
- The decimals are stored as raw text with comma separators, preventing them from being calculated or evaluated mathematically without extraction and conversion.
Before creating your extraction function, identify the exact parameter names that precede your numeric values and verify if the decimals consistently use a comma or have inconsistent spacing.
Create a Custom VBA Function to Extract and Convert Decimals
The most robust method is to use a custom VBA function that isolates the number, replaces the comma with a decimal point, and converts it to a Double.
A dedicated VBA function offers the flexibility to handle inconsistent spacing, variable decimal digit lengths, and parameter extraction dynamically.
In Microsoft Access, navigate to the Database Tools tab and click on Visual Basic, or simply press Alt + F11 on your keyboard.
In the VBA Editor, click Insert in the top menu and select Module to create a blank workspace for your custom function.
Write a function using the Split method to isolate the parameter, such as finding 'MROA ', and extract the numeric string that follows it.
Within the function, apply the Replace function to swap the comma for a period (e.g., Replace(extractedValue, ",", ".")) and wrap it in the CDbl or Val function to cast it as a numeric double.
Close the VBA editor, open your query in Design View, and create a calculated field by typing B: ExtractNumber([YourFieldName]). Run the query to see the parsed values.
Use Built-in String Functions in an Access Query Expression
For simple text fields with consistent formatting, you can use built-in string functions directly within a query without writing a full VBA module.
Manage and Clean Data Easily with WPS Office
If managing complex data extraction in Microsoft Access feels overly complicated, consider migrating your data workflows to WPS Spreadsheet. As a free, lightweight alternative, WPS Office provides intuitive text-to-column tools, seamless format compatibility, and a highly familiar user interface without the need for advanced VBA coding.
Frequently Asked Questions
Why does the Val function return incorrect results for comma decimals in Access?
In VBA, the Val function strictly recognizes the period (.) as a valid decimal separator. You must replace the comma with a period using the Replace function before passing the string to the Val function.
How can I remove duplicate spaces before extracting the text?
You can use the Replace function to substitute double spaces with a single space. Applying an expression like Replace([FieldName], " ", " ") helps standardize the text structure prior to splitting the string.
Can this method handle dynamically changing parameter names?
Yes, if the parameter name varies but the decimal format is consistent, you can split the text on the decimal indicator (such as " 0,") and extract the preceding string to determine the parameter name dynamically.
