Query to return text to the left of a space in string

[peffw]

Hi,

Has anyone got a suggestion as to how I can run an update query which populates a new field with the the text to the left of the first space in a string? I'm trying to create a first name field from data where the first and middle name are in the same field.

Thanks

GEOFF

 

[pbaldy]

Try the Left() function using the InStr() function to find the position of the space.

 

[peffw]

Thanks Paul. I looked up InStr on Access help (I'm not a programmer) and tried to use an example they had in a query by substituting field names as follows

Expr1:Left([forenames],(InStr(1,[forenames],"."))-1)

The query returned an error. Any thoughts on what I'm doing wrong?

 

[pbaldy]

I thought you were looking for a space? That's looking for a period, and will probably error if there is none.

 

[peffw]

Sorry I was. The joys of cut and paste! I've substituted the full stop with a space and it works! Thanks for your help.

 

[pbaldy]

No problem, and welcome to the site. As illustrated by the problem you had, this will likely error if there's no space in the field. If that's a possibility, we'll have to tweak it.

 

[Armedinstructor]

I am experiencing the problem you have described above about there not containing a space in some cases. Can you help?

 

[CJ_London]

you have hijacked an 11 year old thread with effectively a different question.

Please start a new thread with your question and provide some example data to illustrate what you have and what outcome you require

 

[Pat Hartman]

Otherwise, we're happy you found us and we're looking forward to helping But we will need more information when you post your new question. Post some sample data and the expression you are using so we know what problem you are trying to solve.

 

[pbaldy]

I am experiencing the problem you have described above about there not containing a space in some cases. Can you help?

Test for it with an IIf, along the lines of

IIf(InStr(1,[forenames], " ") = 0,"", Left([forenames],(InStr(1,[forenames]," "))-1))