Searching for Space in a field

[Jerry8989]

Hello,
I'm trying to find the first space in every row in a specific column

Col1
------
BOB JOE FRANK
DAVE
WILLIAM

I need my result set to be
BOB
DAVE
WILLIAM

I've tried
Select instrrev(COL1, " ") From Table1
I keep getting zero back. Once I get the position of the first space then I can do a left(COL1, Position of the first space) .

Thank You

 

[Mike375]

InStr([FieldName]," ")

That gives the character count where the first space is. Use that in conjunction with Left()

I juts noticed you have someentries with only one word so some IIF might help as well.

 

[Jerry8989]

Mike,
Thank you for your help that was it. I've used Instr a million times but for some reason it didn't pop up in my mind.

Thanks again
Jerry

 

[Atomic Shrimp]

I juts noticed you have someentries with only one word so some IIF might help as well.

Alternatively, you could just concatenate a space onto the field inside the expression - in cases where there is already a space in the middle, it won't matter, and in cases where the field only contains one word, the space on the end will define the break.

 

[Mike375]

Alternatively, you could just concatenate a space onto the field inside the expression - in cases where there is already a space in the middle, it won't matter, and in cases where the field only contains one word, the space on the end will define the break.

Thanks for that idea. Another one for my tool box

 

[Jerry8989]

Mike Gurman,

If I use the following code:

IIF(InStr([FieldName]," ") > 0, left(InStr([FieldName]," "), InStr([FieldName]," "), [FieldName]) as Col1

that should cover all the scenarios, correct?

Thanks again to both Mikes for your help

 

[Brianwarnock]

The idea is correct but the formula isn't despite the fact that you have used Instr a million times , try

IIF(InStr([FieldName]," ") > 0, left([FieldName], InStr([FieldName]," ")-1), [FieldName]) as Col1

Brian

 

[Jerry8989]

Brian,
Thanks I forgot the -1.
It's a Million and one times now. LOL

Thanks
Jerry