Microsoft Access VBA Macro Programming

CDbl

CDblconverts a value or a string to a double-precision floating point number (eight bytes)

where decimal places are allowed:

CDbl("123.567 89 ")

This will return the value 123.56789.

If the expression has any nonnumeric characters, you will get a Type Mismatch error.

Val

Va lconverts a string to a value. It is more forgiving thanCIntorCLngbecause it will accept

nonnumeric characters:

Val(" 123 ")

This will give the value 123. The following will give the value 123.45:

Val("123.45")

The next example will give the value 12:

Val(" 12 richard")

The following will give the value 0, meaning there are no numeric characters to evaluate:

Val("richard")

Format Function

TheFormatfunction is one of the most useful and complex functions within VBA. It allows

you to format numbers to a chosen output format, similar to the way Access formats a cell,

where you can select from a number of options designating how a number will appear in a

cell.

TheFormatfunction does exactly the same thing as formatting a number or a date within

a cell in a spreadsheet, except it does so from within the code itself. If you wish to display a

number in a message box or on a user form, this function is very useful for making it

readable, particularly if it is a large number:

MsgBox Format(1234567. 8 9, "#,###.#")

This will give the displayed result 1,234,567.9.

In the format string, each # represents a digit placeholder. The comma indicates that

commas are used every three numeric placeholders. Only one numeric placeholder is shown

after the decimal point, which means that the number is shown rounded to one decimal place.

52 Microsoft Access 2010 VBA Macro Programming