MS Access Val() Function

The Val() function in MS Access extracts numeric values from a string. This is useful for cleaning up data that might have mixed text and numbers, or for extracting numerical components from text strings.

Val(): Definition and Usage

Val() attempts to convert a string into a number. It reads the string character by character until it encounters a non-numeric character. At that point, it stops reading and returns the number it has parsed so far. If the string starts with non-numeric characters, it returns 0. If the string is `NULL`, it returns `NULL`.

Syntax

Syntax

Val(string)

Parameter Values

Parameter Description
string The string expression containing the number to extract. This is required.

Example

Extracting Numbers from a String

This example extracts the numeric part from the 'PostalCode' column in the 'Customers' table (assuming a 'Customers' table exists with a 'PostalCode' column containing strings that might include non-numeric characters).

Syntax

SELECT Val(PostalCode) FROM Customers;
Output

(The numeric portion of each PostalCode from the Customers table will be displayed here.  If a PostalCode starts with non-numeric characters, 0 will be returned. If a PostalCode is NULL, NULL will be returned.)
**Note:** The output will vary depending on the contents of your `Customers` table's `PostalCode` column. The function will extract numbers from the beginning of the string until it encounters a non-numeric character. For example, if `PostalCode` is "123AB4", `Val()` will return 123. If `PostalCode` is "AB123", `Val()` will return 0. If `PostalCode` is `NULL`, then the output will be `NULL`.