Access VBA String to Number: Safe Conversion Guide

In Access VBA, convert a numeric string with the function that matches the number you need: use CLng for a whole-number Long, CDbl for a floating-point number, or CCur for currency. Check for Null, blank text, invalid characters, and possible overflow before converting data that comes from a form, table, or import.

Choose the conversion function for the result

VBA provides several conversion functions rather than one universal “string to number” operation. The correct choice depends on the range and precision your data requires. A conversion function also determines what happens when a value has a fractional part, so choosing only by the appearance of the input can produce surprising results.

CInt(text) converts to the 16-bit Integer type, whose range is -32,768 through 32,767. It rounds fractional values to the nearest integer rather than simply cutting off the decimal part.
CLng(text) converts to the 32-bit Long type, with a range of -2,147,483,648 through 2,147,483,647. It is a common choice for whole-number IDs that are genuinely numeric, counters, and Access Long Integer fields.
CSng(text) converts to Single-precision floating point. CDbl(text) converts to Double precision and is generally a sensible choice for measurements or calculations that need a wider range and more precision.
CCur(text) converts to Currency, a fixed-point type suited to values that need four decimal places. It is often preferable to floating point for amounts where binary rounding artifacts are undesirable.
CDec(text) converts to the Decimal subtype of Variant. VBA does not let you declare a variable with a standalone Decimal type, so the result is stored in a Variant.

For instance, if a form field contains the text 275 and the destination is a Long Integer field, use CLng. If the text is a measured value such as 2.75, use CDbl or another type appropriate to the field. Do not use a narrow type just because the current sample happens to fit; later records may exceed its range.

How VBA interprets numeric text

Conversion functions read text according to VBA’s type and regional conversion rules. In particular, the decimal separator and thousands separator can depend on the Windows regional settings. A string written as 1,25 may represent one and a quarter in a locale that uses a comma for decimals, while a computer configured for a period may interpret the same text differently or reject it.

This matters when data comes from another country, a CSV export, or an application that uses a fixed number format. Do not assume that a period or comma will mean the same thing on every user’s computer. If you control the data format, define it consistently and parse it deliberately; if the text is intended to follow the user’s locale, functions such as CDbl are designed to use that locale.

VBA’s Val function behaves differently. It reads from the start of a string and stops when it reaches a character it does not recognize as part of the number. It uses a period as its decimal separator, regardless of regional settings. For example, Val("12.5xyz") returns 12.5, while Val("12,5") returns 12 because the comma ends the numeric portion. That can silently turn malformed or locale-formatted input into a different value. Use Val only when its specific parsing behavior is intended, not as a general-purpose validation method.

Basic conversion examples in Access VBA

For a known, valid string, conversion is straightforward. This procedure shows a Double conversion and prints the result in the Immediate window. In the VBA editor, open the Immediate window with Ctrl+G on Windows. The code assumes the string uses a decimal format accepted by the current regional settings.

Public Sub ShowStringConversion()
    Dim rawText As String
    Dim amount As Double

    ' The text must be a valid number for this locale.
    rawText = "1234.56"
    amount = CDbl(rawText)

    Debug.Print amount
End Sub

For an integer conversion, replace CDbl with CLng if a Long Integer is the intended result. For example, CLng("275") returns 275. However, CLng does not mean “discard everything after the decimal.” It rounds to the nearest integer, and values exactly halfway between integers use banker’s rounding: CLng(2.5) gives 2, while CLng(3.5) gives 4. CInt follows the same rounding rule but has a smaller range.

Rounding, truncation, and number types

Choose deliberately between rounding and truncation. If you need to remove the fractional portion toward zero, use Fix; if you need to round downward toward negative infinity, use Int. These differ for negative values: Fix(-3.8) returns -3, while Int(-3.8) returns -4. Neither should be confused with CLng, which rounds to an integer and can therefore move a value up or down.

Type selection also affects range and precision. A Double can represent a much wider range than a Long, but floating-point values are approximate; some decimal fractions cannot be represented exactly in binary. Currency stores values with four decimal places and has a finite range. Decimal can be useful for decimal precision, but as a Variant subtype it has different handling from a separately declared numeric variable. Match the VBA type to the Access field and the meaning of the data, then consider how calculations and rounding should behave.

Do not convert values that are identifiers merely because they contain digits. Postal codes, account numbers, product codes, and telephone numbers are often better stored as text: leading zeros matter, arithmetic is not meaningful, and a numeric type may remove formatting. For example, converting the text 00427 to a number produces 427, losing the two leading zeros.

Handle Null, empty strings, and invalid input

A common cause of runtime errors is passing data directly to a conversion function without checking what it contains. A bound Access control may contain Null, an empty string, spaces, or nonnumeric text. Converting Null with a numeric conversion function raises an error; converting nonnumeric text generally raises a type mismatch. A string containing a valid number can still overflow the selected type.

IsNumeric can help determine whether an expression can be evaluated as a number, but it is not a complete guarantee that a particular conversion will succeed. It does not choose the right destination type, protect against range limits, or resolve an unintended regional format. The following function returns True only when it can assign the input to a Double. It returns False for Null, Empty, blank text, nonnumeric text, and conversion errors.

Public Function TryParseDouble(ByVal value As Variant, _
                               ByRef result As Double) As Boolean
    On Error GoTo ConversionError

    ' Start with a failure result; leave result unchanged on failure.
    TryParseDouble = False

    If IsNull(value) Or IsEmpty(value) Then Exit Function
    If VarType(value) = vbString Then
        If Len(Trim$(CStr(value))) = 0 Then Exit Function
    End If
    If Not IsNumeric(value) Then Exit Function

    result = CDbl(value)
    TryParseDouble = True
    Exit Function

ConversionError:
    ' Includes conversion and range errors.
    Err.Clear
End Function

Call the function and branch on its result before using the value. For example, a form can show a validation message when it returns False instead of allowing a conversion error to interrupt the user. This example demonstrates a VBA function, not a macro that changes records. Still, save a copy of the database before running any data-changing macro or bulk update: macro actions cannot be undone with Ctrl+Z.

Converting values in Access tables and queries

In VBA, a field value can be converted when it is read, but it is usually better to store data in the correct field type in the first place. If a table column contains numbers as Short Text, calculations, sorting, validation, and joins can behave differently from a proper Number or Currency field. When importing data, check the import specification and sample rows so that Access does not infer an unsuitable type or treat mixed values inconsistently.

Access queries also provide conversion expressions such as CLng, CDbl, and CCur. A query that converts every row will fail if one row contains blank or malformed text. Also beware of using IIf as if it were a short-circuit safety check. In VBA and Access expressions, IIf evaluates both its true and false arguments, so an expression such as IIf(IsNumeric([AmountText]), CDbl([AmountText]), Null) can still attempt the failing conversion. Clean the data first, use a suitable validation routine, or handle records individually in VBA.

For recurring imports, consider retaining the original text in a staging table, validating it, and then appending valid rows to a table with properly typed fields. That approach preserves the source for review and makes it easier to identify which values failed and why. It is safer than silently substituting zero, since zero may be a legitimate amount and can hide a data-quality problem.

Common conversion errors and how to avoid them

Type mismatch: The input is not accepted as a number, or it contains text such as a label or an unexpected separator. Inspect the original value and its regional format rather than changing conversion functions at random.
Overflow: The value is outside the selected type’s range. Choose a wider type if appropriate, but also confirm that the input is not corrupted or unexpectedly scaled.
Unexpected rounding: CInt and CLng round; they do not truncate. Use Fix or a separately specified rounding rule when that is the actual requirement.
Null conversion error: Check IsNull before converting a field or control value. Null means missing or unknown, not the numeric value zero.
Silent partial conversion with Val: Because Val stops at the first unrecognized character, text such as a number followed by stray letters may produce a plausible but incorrect result. Validate the whole input if every character must be meaningful.
Lost leading zeros: Numeric conversion changes identifiers such as 00081. Store such values as text when their formatting is significant.

During debugging, inspect the source value, its VarType, and the exact conversion line. A breakpoint or the Immediate window can reveal whether the input is Null, an empty string, or text formatted differently than expected. If the conversion occurs during a batch update, test on a copy and log rejected values before changing the production data.

Frequently Asked Questions

What is the best VBA function to convert a string to a number?
There is no single best function for every case. Use CLng for a whole-number Long, CDbl for a Double, CCur for Currency, or another conversion function that matches the destination type. Validate input before conversion when it is not guaranteed to be clean.

How do I convert a string to an integer in Access VBA?
Use CLng(text) for a 32-bit Long Integer, or CInt(text) for the smaller Integer type. Both round fractional input to the nearest integer, including banker’s rounding for exact halves. Check that the result is within the chosen type’s range.

Why does CDbl work on one computer but fail on another?
The computers may use different regional settings for decimal and thousands separators. A string such as 1,25 may be valid decimal text in one locale but not another. Standardize the input format or parse it according to the format that produced it.

Should I use Val or CDbl?
Use CDbl when you want a locale-aware conversion to Double and expect the whole input to be valid. Val uses a period for decimals and stops at the first unrecognized character, which can quietly accept only part of a bad string.

How can I avoid an error when the field is Null?
Test IsNull(value) before calling a conversion function, and decide what missing data should mean in your application. Do not automatically replace Null with zero unless zero is genuinely the correct business meaning.

A practical rule for reliable conversion

First decide what the value represents, then select a compatible VBA and Access field type. Next, establish whether the text follows the user’s regional settings or a fixed external format. Finally, validate missing values, blanks, invalid characters, and range limits before converting. These steps prevent the most common problems: type mismatch errors, unintended rounding, locale-dependent results, and loss of meaningful text such as leading zeros.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.