Formula to Remove Empty Spaces

[Accesshelp]

Hello all,

Somehow, my brain is not working today. Can you help me with a formula to
remove spaces for my select query? For example, I have a text field with the
following text: My Name is John Doe.

I like to turn it into "MyNameisJohnDoe". Please note the text does not
always have 5 words. It could have as little as 2 words.

Thanks.

 

[vanderghast]

Replace(originalString, " ", "")


sould do, in recent versions.



Vanderghast, Access MVP

 

[Jeff Boyce]

Take a look at Access HELP on the "Replace" function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Accesshelp]

Vanderghast,

Thank you very much for the formula. It worked perfectly.

Could I ask you one more question? I have two tables (Table A and Table B)
with student information. Table B contains the most recent data (changes and
additions).

What I like to do is I like to check whether the names in A and the names in
B to see whether they are the same. If not, I like to know what they are.

So I join 2 tables with the student id in a select table. Then I create a
column to do the comparison using the StrComp formula. I would say at least
98% of the time the information is the same.

Doing that comparison, there are twists to the problem. The twists are
either the names spell wrong this year or the year's names have extra spaces
in between or have a puntuation. Because of those twists, the results come
out more than what they suppose to.

Is there another way of doing the comparison to show only the records with
changes?

Thanks.

 

[Accesshelp]

Jeff,

Thanks for your help. Replace function works perfectly.

Could I ask you one more question? I have two tables (Table A and Table B)
with student information. Table B contains the most recent data (changes and
additions).

What I like to do is I like to check whether the names in A and the names in
B to see whether they are the same. If not, I like to know what they are.

So I join 2 tables with the student id in a select table. Then I create a
column to do the comparison using the StrComp formula. I would say at least
98% of the time the information is the same.

Doing that comparison, there are twists to the problem. The twists are
either the names spell wrong this year or the year's names have extra spaces
in between or have a puntuation. Because of those twists, the results come
out more than what they suppose to.

Is there another way of doing the comparison to show only the records with
changes?

Thanks.

 

[vanderghast]

Basically, you run the query that the query wizard about finding record
without match in another table. You can edit the SQL statement and replace
the ON clause:

ON tableA.Name = tableB.Name


to

ON Replace(tableA.Name, " ", "") = Replace(tableB.Name, " ", "")


to make the test based on the name, but where the spaces have been removed,
or


ON Replace(Replace(tableA.Name, " ", ""), ",", "") =
Replace(Replace(tableB.Name, " ", ""), ",", "")


where you would also have removed the comas.


Vanderghast, Access MVP

 

[Accesshelp]

Vanderghast,

Is there a better way? Because sometimes, even I have what you said, the
last letter in one of the two tables can throw the results off. Also
sometimes, a letter transposes in one of the tables can throw the results
off.

I guess what you have is the best way because I know that there is no
solution is bullot proof.

Thanks.

 

[John W. Vinson]

I guess what you have is the best way because I know that there is no
solution is bullot proof.

Is "Thomas Jones" the same person as "Tommy Jones"? How about "Sally Lynn
Smith" and "Sally Smith-Fredricks"? Or for that matter "Sally Fredricks",
whose mother is still using her maiden name in some circumstances and the
hyphenated name in others?

Names are inherently *bad* linking fields, because they're just too variable.
A USB (Using Someone's Brain) interfase is usually the only solution.

 

[Accesshelp]

John,

Thanks for your input.

I was not planning on linking the name field.

I have a clean set of records, and I would like to compare those with the
most recent set of records. If the fields in the most recent set of records
are different by having extra spaces, transposing of letters or having a
punctuation, I would not consider them different. Otherwise, those fields
are considered updated fields.

Basically, I want to filter the fields that are updated, and if those fields
are updated, I want to reflect the changes to my clean set of records.

Thanks.

 

[John W. Vinson]

John,

Thanks for your input.

I was not planning on linking the name field.

I have a clean set of records, and I would like to compare those with the
most recent set of records. If the fields in the most recent set of records
are different by having extra spaces, transposing of letters or having a
punctuation, I would not consider them different. Otherwise, those fields
are considered updated fields.

Basically, I want to filter the fields that are updated, and if those fields
are updated, I want to reflect the changes to my clean set of records.

I still suspect that having a pair of human eyes on the task will make it
easier.

You can write a small function (I'm sure it's been posted here...) to strip
out all nonalphabetic characters from a field: heck, it's late but it's not
hard:

Public Function JustAlpha(strIn As String) As String
Dim iPos As Integer
Dim chrNext As String
JustAlpha = ""
strIn = UCase(strIn)
For iPos = 1 to Len(strIn)
chrNext = Mid(strIn, iPos, 1)
If chrNext >= "A" AND chrNext <= "Z" Then
JustAlpha = JustAlpha & chrNext
End If
End Sub

This will take "Tommy Jones" and "Tommy Jones" and "T*O*M*M*Y _ J%o3N.eS" and
convert them to TOMMYJONES.

Switched characters... well, that's tougher since it's a combinatorial open
ended problem; and it will lead to false matches (Jane and Jean say). There
are some alphanumeric distance algorithms that could be used but they'd
probably be overkill here.