Access: Val Function

In Access, the Val function returns a variant indicating the sign of a number.

The syntax for the Val function is:

Val( string )

The required string argument is any valid string expression. It is a sequence of characters that can be interpreted as a numeric value. Val stops reading the string at the first character that it cannot recognize as part of a number. Val also strips blanks, tabs, and linefeeds from the argument string. Symbols and characters often considered part of a numeric value, such as the dollar sign and commas, are not recognized by Val as numeric. The Val function always returns a Double. If the result of the Val function is assigned to a variable, the Double returned by Val is forced into the datatype of the variable. 


Val ( 237 and 54)        returns 237 
Val ( 2 4 54 7)            returns 24547
Val (17 Main Road )     returns 17

VBA Code

Dim MyNumber 
MyNumber = Val(2 4 57 Main Road)

This example uses the Val function to return the numbers contained in a string. Now the MyNumber variable would contain the value 2457.

SQL query

You can also use the Val function in a query.

SELECT Val([Address]) AS Expr1 
FROM EmployeeAddressTable


Leave a Reply

Your email address will not be published.