Query return characters upto first space

[vaughanmcd]

Hi I need to return charters upto the first space. Some of the lines have more than one space.

eg: LG-LIP-9024WMB 0021812 I want LG-LIP-9024WMB
or 9414894016190 FSHVE1260GL-FR 0021032 I want 9414894016190
or MS14 0029180 I want MS14

I tried this but not working New Supplier Part: Left([Search Terms],InStr([Search Terms],"/")-1)

Is the a formula?

 

[theDBguy]

Have you tried?

Left([Search Terms], InStr([Search Terms], " ") - 1)

 

[vaughanmcd]

Yes that work perfect, Thanks

 

[vaughanmcd]

Have you tried?

Left([Search Terms], InStr([Search Terms], " ") - 1)

It works when there is two or more string with spaces, But errors when there is a single string with no spaces.
Is there a way to just copy it over if its a single string aswell in the same fuction?

eg: BLS208 = BLS208
00110 0022018 = 00110

At the moment the query says conversion error when it come across something like BLS208 and will leave it blank.

 

[arnelgp]

IIF(InStr([Search Terms] & "", " ")<>0, Left([Search Terms], InStr([Search Terms], " ") - 1), [Search Terms])

 

[vaughanmcd]

IIF(InStr([Search Terms] & "", " ")<>0, Left([Search Terms], InStr([Search Terms], " ") - 1), [Search Terms])

Genius , Thank you