HowTo Insert a Space into text string

[apug]

I have a table with a User field. It is LastName,FirstName. I have another table LastName, FirstName with a space after the comma. I need to run a join query on the User fields. How do I insert a space after the comma on my first table.

 

[lespaul]

It will be easier to remove the space from the second table.

I think the syntax is:

Replace(UserName, " ", "")
Replace(FieldName, WhatToReplace, PutInstead)


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

Replace(yourFieldName, ",", ", ")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[apug]

I get an "undefined function 'replace' in expression" error message. Am I missing a plugin. I am in access '97

 

[PHV]

Have a look here: faq705-4342

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[apug]

Thank you for your help. Not exactly what I was looking for. I will keep searching.

 

[ChanceD2]

I am at a loss...

1. Why exactly do you have 2 different table with user names?
2. By having a field with full user name, you are breaking the very first rule of data normalization. "All Fields Must Be Atomic".

Maybe, if you're willing, we could help you get this sorted out.

Also, I'll bet this is why you are having a difficult time getting an answer.... because it is being done incorrectly.

Pre-Post-Script ----- I only know this because I did this too!

If you want help to fix this, just ask. If not, give more detail on how the data is being copied from one table to the other.

HTH

C-D2

 

[apug]

I agree but this is not my table, it is what I have to work with. Here is what I did; I separated out the LastName, and FirstName into separate fields and then I concatenated these 2 fields into a 3rd field in a table with the space between the First and Last names. A little confusing but here is the code:

SELECT MyTableScoreAvgNameTrim.UserName, MyTableScoreAvgNameTrim.[%], Left([UserName],InStr(1,[UserName],",")) AS last, Mid([UserName],InStr(1,[UserName],",")+1) AS First, [last] & " " & [first] AS Expr1 INTO MyTableAvgWithNameSpace
FROM MyTableScoreAvgNameTrim;

Again, I had to work with an existing table structure.