Access query help (1 Viewer)

[Jovial]

I have data as
WILSON, DAVE D
GROVER, LAURA A

I want as
WILSON,DAVE
GROVER,LAURA

How do i achieve this in access query?

 

[Alc]

Assuming the data will always be in that format, try

 = Replace(Left({name}, Len({name} - 2), " ")

This would strip off the last two characters in each case and then remove the space between the comma and the first name.

 

[Ranman256]

select LAST, IIF(instr([FIRST]," ")>0,left([FIRST],instr([FIRST]," ")-1),[FIRST]) as FirstNam
from table

 

[Jovial]

Assuming the data will always be in that format, try

 = Replace(Left({name}, Len({name} - 2), " ")

This would strip off the last two characters in each case and then remove the space between the comma and the first name.

I changed the name to my column name 'CustomerName' and it gives me error as:
the exprerssion you entered has a function containing the wrong number of arguments

 

[Jovial]

select LAST, IIF(instr([FIRST]," ")>0,left([FIRST],instr([FIRST]," ")-1),[FIRST]) as FirstNam
from table

What is LAST and FIRST here? Data are present in column CustomerName as
CustomerName
WILSON, DAVE D
GROVER, LAURA A

 

[Alc]

I changed the name to my column name 'CustomerName' and it gives me error as:
the exprerssion you entered has a function containing the wrong number of arguments

Did you leave the {} brackets in? They were just to indicate a general name and should be removed from the actual query.
What's the full query into which you placed that line?

 

[Jovial]

Did you leave the {} brackets in? They were just to indicate a general name and should be removed from the actual query.
What's the full query into which you placed that line?

Replace(Left([CustomerName], Len([CustomerName] - 2, " ")

 

[Alc]

Replace(Left([CustomerName], Len([CustomerName] - 2, " ")

Sorry, I misread the question and assumed you had written a query to start with and just wanted help getting it working. That's just the part to convert the name into the format you want. In order to create the query itself you need to format it as Ranman256 did. e.g.

 SELECT Replace(Left([CustomerName], Len([CustomerName] - 2, " ") FROM {table name};

 

[Jovial]

Sorry, I misread the question and assumed you had written a query to start with and just wanted help getting it working. That's just the part to convert the name into the format you want. In order to create the query itself you need to format it as Ranman256 did. e.g.

 SELECT Replace(Left([CustomerName], Len([CustomerName] - 2, " ") FROM {table name};

My table name is CommentItem
Select Replace(Left([CustomerName], Len([CustomerName] - 2, " ") from CommentItem
error is still in the function. It is showing error in " " part

 

[Alc]

I thin there's a bracket missing after the 2. Try

 Select Replace(Left([CustomerName], Len([CustomerName] - 2), " ") from CommentItem

 

[Jovial]

I thin there's a bracket missing after the 2. Try

 Select Replace(Left([CustomerName], Len([CustomerName] - 2), " ") from CommentItem

nope -Same error

 

[Alc]

Post a small version of the database, zipped ,and I'll take a proper look at it.

 

[plog]

How many records you talking about? If its more than 100 I am fairly certain you have formats other than what you initially posted and will fail :

1. De La Cruz, John R --> DeLaCruz,John
2. Smith Jr., John R--> SmithJr,John
3. Smith, John --> Smith,Jo
4. Smith, Mary Ann J -->Smith,MaryAnn



The real problem is your data is stored improperly--discrete pieces of data should be stored discretely. That means a field for each part of the name. You get your data like that and putting it together in any manner you want is simple.