Extracting part of a field in a query

[Guest]

Can someone please tell me how I would extract the first name from a text box
in an access query? i.e. Burt Reynolds I want to extract Burt.

I tried this in a query and was successful extracting the last name..but it
has been challenging to extract just the first name.

The expression that I used was LEFT, MID, and InStr

Thanks A Bunch

 

[Brendan Reynolds]

There is no really reliable way to do this. You can get everything to the
left of the first space easily enough ...

SELECT tblTest.FullName, Left$([FullName],InStr(1,[FullName]," ")-1) AS
GivenName
FROM tblTest;

However, not all full names contain only one space. An expression like the
one above will fail on names such as José Pedro Freyre, Isabel de Castro, or
Miguel Angel Paolino. (The examples are from Northwind).

An expression like the one above may do the bulk of the work for you, but
any substantial volume of data is likely to require careful checking and
manual correction.