Feb 02 2022 04:24 AM
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
Feb 02 2022 04:41 AM
Feb 02 2022 04:44 AM
Hi,
How about this?
IIf(IsNumeric(bk_profit),Val(bk_profit),0)
Servus
Karl
************
Access News
Access DevCon
Feb 02 2022 04:47 AM
Feb 02 2022 04:57 AM
Feb 02 2022 05:12 AM
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