Convert "A Large House" to "ALargeHouse"

  • Thread starter Thread starter Barry McConomy
  • Start date Start date
B

Barry McConomy

Hi

I want to remove the spaces from text within a field, can someboby help
please.

i.e. convert "A Large House" to "ALargeHouse"

Regards
Barry
 
Hi

I want to remove the spaces from text within a field, can someboby help
please.

i.e. convert "A Large House" to "ALargeHouse"

Regards
Barry

There are three different ways to do this, depending on which version
of Access you're running. If you have A2002 or later use an Update
Query updating the field to

Replace([fieldname], " ", "")

If you have an older version post back.
 
What version of Access?

For Access 2003, 2002 and some versions of 2000, you can use
Replace([MyField], " ", "").

Certain versions of Access 2000 don't allow you to use the Replace function
in queries, but there's an easy solution: create your own function that uses
Replace, and use your function instead:

Function MyReplace(InputString As Variant, ChangeFrom As String, ChangeTo As
String) As String

MyReplace = Replace(InputString & vbNullString, ChangeFrom, ChangeTo)

End Function

For any earlier versions of Access, you'll have to write your own function.
 
Hi John

Many thanks for the help.

Access 2002

Can I ask another:-

I want to convert "Word1, & Word2" to "Word1Word2", removing the ", &"

Thanks

Regards
Barry



John Vinson said:
Hi

I want to remove the spaces from text within a field, can someboby help
please.

i.e. convert "A Large House" to "ALargeHouse"

Regards
Barry

There are three different ways to do this, depending on which version
of Access you're running. If you have A2002 or later use an Update
Query updating the field to

Replace([fieldname], " ", "")

If you have an older version post back.
 
Hi Douglas

Many thanks for the support.

Regards
Barry

Douglas J. Steele said:
What version of Access?

For Access 2003, 2002 and some versions of 2000, you can use
Replace([MyField], " ", "").

Certain versions of Access 2000 don't allow you to use the Replace function
in queries, but there's an easy solution: create your own function that uses
Replace, and use your function instead:

Function MyReplace(InputString As Variant, ChangeFrom As String, ChangeTo As
String) As String

MyReplace = Replace(InputString & vbNullString, ChangeFrom, ChangeTo)

End Function

For any earlier versions of Access, you'll have to write your own function.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Barry McConomy said:
Hi

I want to remove the spaces from text within a field, can someboby help
please.

i.e. convert "A Large House" to "ALargeHouse"

Regards
Barry
 
Hi John

Many thanks for the help.

Access 2002

Can I ask another:-

I want to convert "Word1, & Word2" to "Word1Word2", removing the ", &"

Well... think about it.

Replace(String1, String2, String3)

searches String1 for any instance of String2, and if it finds it,
replaces it with String3. To remove a string, replace it with an empty
string by using "" as String3.
 
Hi John

I have tried with many combination of expressions ans still can't get it to
work.

I am strugling with string2, I want to remove 3 instants of , & and space, I
can remove any one but not all 3 at the same time.

The expression I am using Expr1: Replace([catn]," ","")

Many thanks
Barry
 
All Replace([catn]," ","") is going to do is remove the spaces. If you want
to remove all 3, you'll need to nest the calls:

Replace(Replace(Replace([catn]," ",""), ",", ""), "&", "")



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Barry McConomy said:
Hi John

I have tried with many combination of expressions ans still can't get it to
work.

I am strugling with string2, I want to remove 3 instants of , & and space, I
can remove any one but not all 3 at the same time.

The expression I am using Expr1: Replace([catn]," ","")

Many thanks
Barry



John Vinson said:
Well... think about it.

Replace(String1, String2, String3)

searches String1 for any instance of String2, and if it finds it,
replaces it with String3. To remove a string, replace it with an empty
string by using "" as String3.
 
Hi John

I have tried with many combination of expressions ans still can't get it to
work.

I am strugling with string2, I want to remove 3 instants of , & and space, I
can remove any one but not all 3 at the same time.

The expression I am using Expr1: Replace([catn]," ","")

If you want to remove the text string ", &" then replace THAT TEXT
STRING:

Replace([catn], ", &", "")

It's really pretty simple: you put the text string that you want to
replace (no matter what or how long it might be) in the second
argument, and the string with which you want to replace it in the
third argument.
 
Hi Doug

Thanks for being patient with me and many thanks for the knowledge.

Barry


Douglas J. Steele said:
All Replace([catn]," ","") is going to do is remove the spaces. If you want
to remove all 3, you'll need to nest the calls:

Replace(Replace(Replace([catn]," ",""), ",", ""), "&", "")



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Barry McConomy said:
Hi John

I have tried with many combination of expressions ans still can't get it to
work.

I am strugling with string2, I want to remove 3 instants of , & and
space,
I
can remove any one but not all 3 at the same time.

The expression I am using Expr1: Replace([catn]," ","")

Many thanks
Barry



John Vinson said:
Hi John

Many thanks for the help.

Access 2002

Can I ask another:-

I want to convert "Word1, & Word2" to "Word1Word2", removing the ", &"

Well... think about it.

Replace(String1, String2, String3)

searches String1 for any instance of String2, and if it finds it,
replaces it with String3. To remove a string, replace it with an empty
string by using "" as String3.
 
Back
Top