Convert "A Large House" to "ALargeHouse"

[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

 

[John Vinson]

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.

 

[Douglas J. Steele]

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.

 

[Barry McConomy]

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

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.

 

[Barry McConomy]

Hi Douglas

Many thanks for the support.

Regards
Barry

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)

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

 

[John Vinson]

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.

 

[Barry McConomy]

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

 

[Douglas J. Steele]

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)

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

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.

 

[John Vinson]

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.

 

[Barry McConomy]

Hi Doug

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

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)

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

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.