Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 8th, 2003, 10:59 AM
Authorized User
 
Join Date: Jun 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kev_79
Default Space in Expression

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
 
Old July 8th, 2003, 11:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Originally posted by kev_79
 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
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
 
Old July 8th, 2003, 12:16 PM
Authorized User
 
Join Date: Jun 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kev_79
Default

It worked!!! Thanks so much!:D:D
 
Old July 9th, 2003, 08:01 AM
Authorized User
 
Join Date: Jun 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Greg has your answer for you

Kenny Alligood
 
Old August 14th, 2003, 12:48 PM
Authorized User
 
Join Date: Aug 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old August 14th, 2003, 12:57 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 14th, 2003, 01:53 PM
Authorized User
 
Join Date: Aug 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It is consistent: John Doe

 
Old August 14th, 2003, 02:24 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 14th, 2003, 03:14 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old August 20th, 2003, 09:38 AM
Authorized User
 
Join Date: Aug 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Thanks
-Tim






Similar Threads
Thread Thread Starter Forum Replies Last Post
Message> in query expression <expression>. (Error ybg1 Access 5 July 15th, 2007 05:42 AM
Looks like a space but it isn't. rstelma SQL Server 2000 3 September 7th, 2005 07:17 PM
space crmpicco Javascript How-To 1 February 7th, 2005 01:26 PM
unusual space yami56 Dreamweaver (all versions) 7 August 15th, 2004 06:39 AM
Space in URL lian_a Classic ASP Basics 5 June 23rd, 2004 11:29 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.