Append query help

[bravo6]

I have a field called [fname] that is the customer's first name. Sometimes it contains a middle ititial as well and I need to get rid of that initial. So sometimes the field contains "Mary" and sometimes it contains "Mary B" for example.

It seems like my solution:
Replace([fname],"* ?","* ")
should work but it doesn't. What am I missing?

 

[PHV]

What about this ?
If(fname LIKE "* ?", LEFT(fname,LEN(fname)-2), fname)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bravo6]

Thanks that was very helpful and got me moving in a better direction. I had to tweak it a bit to get the results I was looking for. I also had to take into account that some of the records had middle name rather than middle ititial (something I didn't realize in my first post) so the field could be: "Mary", "Mary B", or "Mary Beth". The query below converted them all to "Mary"

fname: IIf([TableName.fname] Like "* *",Left([Tablename.fname],InStr([Tablename.fname]," ")-1),[Tablename.fname])