on 05-21-2010 1:43 PM
i used this to retrieve values form a string field where the string has multiple words
1234568 john smith
it retreives the numbers prior to the space
LEFT ({TableName.Field}, InStr({@field}, " "))
if there is only SNAP it returns a blank value
how do i get it to return the vale if there are multiple words or just one?
if instr(x,' ') = 1 then
split(x)[2]
else if instr(x,' ') > 0 then
split(x)[1]
else
x;
// where X is the string field
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try this...
IF IsNumeric(LEFT ({TableName.Field}, InStr({TableName.Field}, " ")))
THEN LEFT ({TableName.Field}, InStr({TableName.Field}, " "))
ELSE ""
This will return an empty string if the results of the original formula aren't numeric.
HTH,
Jason
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
i dont think i explained it properly- i am trying to create an id field from a string field that isnt always consitant.
my data looks like this
name field
123456 john smith
jack snap
joe
i need to take the first part of the string if there are spaces between the words or there is only one word.
my field should end up looking like
123456
jack
joe
if my field looks like this i dont get the first value it is blank, i need to retrieve the first word here.
/AKERS NATIONAL ROLL COMPANY
if my field is just one value 7041001
i get nothing.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.