Trim Name?

[Andy]

I have a field which holds the forename and the first initial with a space
inbetween but I just want a query to return the forename.

i.e. 'John K', I want the query to just return 'John'

Thanks

 

[RonaldoOneNil]

If there is always a space and 1 initial in your field then put a column in
your query like this
FName: Left([Forename],Len([Forename])-2)

 

[Andy]

No, some have initials and some don't. I was able to use this formula in
Excel but cannot replicate it in Access.

=LEFT(AH3,FIND(" ",AH3))

If there is always a space and 1 initial in your field then put a column in
your query like this
FName: Left([Forename],Len([Forename])-2)

I have a field which holds the forename and the first initial with a space
inbetween but I just want a query to return the forename.

i.e. 'John K', I want the query to just return 'John'

Thanks

 

[RonaldoOneNil]

Fname: Left([Forename],IIf(InStr(1,[Forename],"
",1)>0,Len([Forename])-2,Len([Forename])))

No, some have initials and some don't. I was able to use this formula in
Excel but cannot replicate it in Access.

=LEFT(AH3,FIND(" ",AH3))

If there is always a space and 1 initial in your field then put a column in
your query like this
FName: Left([Forename],Len([Forename])-2)

I have a field which holds the forename and the first initial with a space
inbetween but I just want a query to return the forename.

i.e. 'John K', I want the query to just return 'John'

Thanks

 

[John Spencer]

If you want to chop at the first space you can use the following expression.

Left([NameField],Instr(1,[NameField] & " "," ")-1)

Adding the space to the NameField in the Instr function ensures that a
space will always be found.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================