convert text to number in VBA

[gizzy]

what function in VBA should I use to convert string field to a currency
or any numeric format?
I have a database that gets data from SQL server and all the values
there are stored in a character format, but i need to do some
mathematic functions on Access so i need numeric format

thanks in advance,
Gizzy

 

[Guest]

try

Int([FieldName])

Some of the functions you can check
Clng = Convert to Long
CDbl = Convert to Double
CCur = Convert to Currency

 

[gizzy]

Thank you!

But what if i leave one of the fields blank?
if i use CCur(field2) then i get message "invalid use of null"

 

[MH]

If you want to ignore nulls and return zeros where the text value is null,
use:

CCur(Nz([FieldName]))

MH

 

[John Spencer]

One method.

IIf(IsNumeric([FieldName]),Val([FieldName]),Null)

If you want to ignore nulls and return zeros where the text value is null,
use:

CCur(Nz([FieldName]))

MH

Thank you!

But what if i leave one of the fields blank?
if i use CCur(field2) then i get message "invalid use of null"

 

[Guest]

Use the Nz function with it to replace null with 0

CCur(Nz(field2,0))

 

[gizzy]

CCuz(nz) helped me. Thanks a lot!