CODEWITHSUNDEEP
ms-access
elithin
elithin

Reputation: 191

Access Check if a string field contains a char before doing something with it

my problem is rather simple (i think), but I just can't make it work. I have numbers in a field that look like this 1;2.3 but sometimes they look like this 1;2.3 - 4;5.6 What I have to do is: If there is the '-' char, get the first part (1;2.3). Otherwise get the whole thing. I did something to get the left part

Left([age_years];InStr(1;[age_years];" - ")-1))

but when there is just one part it gives me a #Func! result. So I tried 

mths: IIf(InStr([age_years]>0;"-");[age_years];Left([age_years];InStr(1;[age_years];" - ")-1))

but it didn't work at all. Gave me just a bunch of #Error!, and I cannt see what I'm doing wrong.

Help please!

Upvotes: 2

Views: 6843

Answers (1)

Fionnuala
Fionnuala

Reputation: 91326

How about:

IIf(InStr([age_years];"-")=0;[age_years];Left([age_years];InStr(1;[age_years];" - ")-1))

You need to decide between " - " and "-", I have left it, but you should pick one.

Upvotes: 1

Related Questions