Quick update names question??

[WB786]

I am getting data with a field name called [Name]. I need to be able to seperate the values from this field into FirstName and LastName. This is what I am currently using:

Left$([Name],InStr(1,[Name]," ")-1)
Right$([Name],Len([Name])-InStr(1,[Name]," ")+0)

But the problem is that sometimes in the data given to me has only the first name listed and when the above update query runs it is putting the first name in the LastName column instead of the FirstName column.

Can anyone help me get this fixed??

Thanks.

WB

 

[luceze]

You could check to see if the space exists.

iif(InStr(1,[Name]," ")=0,"",Right$([Name],Len([Name])-InStr(1,[Name]," ")+0))

HTH,
Eric

 

[WB786]

That didn't work. It will update the record if there is FirstName and LastName. But if only firstname then it is updating the FirstName field with nothing in it.

Also I forgot to mention is that in the update query I am trying to use it needs to be able to update both FirstName and LastName at the same time. So LastName shouldn't get updated if there is only a FirstName in the Name field.

Hope this makes sense,

Thanks..

WB

 

[AndreQ1]

This doesn't specifically address your problem but I STRONGLY suggest not using reserved words as column names. Too easy for the system to get confused.

Stay away from column names like User, Name, Table, Field, ID and the like.

 

[WB786]

AndreQ1,

Yes, I do know that. The file I am getting has NAME as one of the column names. I am just dumping the file into a temp table and then cleaning it up and then upload the data to the SQL server.

And you are right. This doesn't solve my problem. Thanks for the tip though.



WB

 

[BobJacksonNCI]

NAE (Not Access Expert),

To get by, you could do this in three steps:

1. Update the temp table - when there's only a first name add a last name of X

2. Use your working update query.

3. Update again replacing last name = X with null or blank, your choice.


My philosophy is that ongoing tasks warrant elegance and efficiency, one-timers warrant just getting it done.


HTH,
Bob

 

[WB786]

Bob looks like this actually maybe the only good solution so far. I don't mind setting up two extra update queries.

Thanks for the help and the simple soultion.



WB