How can you put a space in an expression. I have a table that I want to combine the FNAME and LNAME Fields, and I can do it, but it puts it together as one word. I just wrote an expression that is:

FLNAME: COLLECTOR!FNAME+COLLECTOR!LNAME

Thanks in advance for any help,

Kevin

Kevin, note concatenation of strings is done via the ampersand (&). To include the space, just attach it in the middle, i.e.

FLNAME: COLLECTOR!FNAME & " " & COLLECTOR!LNAME


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

It worked!!! Thanks so much!:D:D

Greg has your answer for you

Kenny Alligood

OK...How about going the opposite direction. I have a name field and I want to break it into to fields FName and LName. I was trying to find a way using access help for substrings, but couldn't find anything. Any suggestions?

-Tim

What is the format of your Name field? Is the data entry of varied types or is it consistent?
Doe, John
Doe, John R
Doe Jr, John R
John Doe
John R Doe

Some basic info would assist us in helping you.

Beth M

It is consistent: John Doe

If you are using Access 2000 or 2002 you can use the InStrRev function like the following to retrieve the left half of the string:

Left([FieldName],InStrRev([FieldName]," ")-1)

Below is used for the right:

Right([FieldName],Len([FieldName])-InStr(1,[FieldName]," "))

HTH,

Beth M

Hi Tim,

Another approach to take if you want to merge the parsed names into a table of there very own which has a strcuture identical to the table with the unparsed full-name field is to:

1. Copy your original table's structure only.
2. Add columns to the new table for the parsed firstname and the
   parsed lastname fields.

Your original table might be: tblFullName

PersonID
FullName

Your new empty table might be: tblParsedNames

PersonID
FirstName
LastName

Run the following append query. Make sure your table names and field names match the names in the query and that you are using the same data types in the two tables:

INSERT INTO tblParsedNames ( PersonID, FirstName, LastName )
SELECT
    tblFullName.PersonID,
    Mid([FullName],1,InStr([FullName],Chr(32))-1) AS FirstName,
    Mid([FullName],InStr([FullName],Chr(32))+1) AS LastName
FROM tblFullname;

As long as your new table's field list and the fields in the INSERT statement are the same, you can add as many fields to the new table from your original table as you like. Just another way to go.

HTH

Bob

COOL!!! Thanks alot to both of your ideas Beth and Bob. I tried them out and they work great!

Thanks
-Tim