Finding a space....

[New2SQL]

..i am new to SQL/Access databases so am looking to create a query that finds the first space in a text box. For example, if i have "New2SQL 100", how would i write a query to find the space and shortern the text to just "New2SQL"?

Any help would be much appreciated. Thanx.

 

[Mile-O]

First space or all spaces?

 

[New2SQL]

still unsure...

i am looking to basically chop of the info after the space in the text. so that way the text goes from "New2SQL 100" to "New2SQL".

 

[Mile-O]

have a look at the "Trim" function in help

The Trim function doesn't trim internal spaces - just trailing and leading spaces.

For the first space, it could be removed using the Trim function in combination with the InStr(), Left$(), and Mid$() functions.

i.e.

Trim(Left$([TextBox], InStr(1, [TextBox], " "))) & Mid$([TextBox], InStr(1, [TextBox], " ")+1)

 

[Mile-O]

Just seen your new post, you don't need the Replace(). You only need the Left$() and InStr() functions.

Left$([TextBox], InStr(1, [TextBox], " ")-1)

 

[New2SQL]

Excellent.

Thanx for the help.

got the results i need. no doubt i will be back!