Convert Text to a number and 0 instead of #error

[wyptaj]

Hello Everyone, 

I'm sure this is an easy thing for all you experts, but I'm having a bit of trouble with this.

I have a linked sql table in access, whereby numbers have been stored as text for the field bk_profit

I am in query design view on Access, but I can't for the life of me get the required outcome.

Profit: Val([bk_profit]) converts the text to a number, but where there isn't a number I just want zero, but it keeps coming back with #error.

Please can you advise what the formula I can put in the field heading to make this right ?

Many thanks in advance.
Paul

[#2]

use:

Profit: Val([bk_profit] & "")

instead,

[#3]

Hi,

How about this?

IIf(IsNumeric(bk_profit),Val(bk_profit),0)

Servus
Karl
************
Access News
Access DevCon

[#4]

Thank you both for your help, I really do appreciate it and they both worked. Thank you so much

[#5]

review again the requirement.
you don't need 3 functions just to return a number or 0 (if not number).

[#6]

Hi Arnel,

I reviewed the requirement and keep interpreting "where there isn't a number" as where there is an empty field or potentially alphanumeric text like "123xyz" where your short version would not return 0.

Of course, this is my interpretation, but it is allowed as long as the requirements are not specified in more detail.

Servus
Karl
************
Access News
Access DevCon