Trim Name?

  • Thread starter Thread starter Andy
  • Start date Start date
A

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
 
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)
 
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))

RonaldoOneNil said:
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 said:
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
Click to expand...
Click to expand...
 
Fname: Left([Forename],IIf(InStr(1,[Forename],"
",1)>0,Len([Forename])-2,Len([Forename])))

Andy said:
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))

RonaldoOneNil said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
'====================================================
 
Back
Top