Extracting the first numbers before a space

[torquay]

Hi I am trying to extract all numbers before a space, no problem with
LN: Left([Custom label],InStr([Custom label]," ")-1)
Except this will return #Func! where there are no spaces after the number
Example
1234 yth
will return 1234
but if the number has nothing after it i.e 2456 I get #Func! error
Can anyone piont me in the correct direction,
thanks Kim

 

[plog]

The problem is that InStr (https://www.techonthenet.com/access/functions/string/instr.php) will return 0 if no space is in your original string. And Left([Custom Label], -1) doesn't make sense to the computer.

So what you need to do is build a conditional statement (using Iif). Your so close, I won't write the code for you, but I will do it in english:

if there's a space in your original string, use your current code, but if there's not, just return the string.

 

[MarkK]

You might also look into the Split() and Val() functions...

? val("1243 asdf")
 1243 

? Split("1243 asdf")(0)
 1243

? val("1243")
 1243 

? Split("1243")(0)
 1243