Removing double quotes and extra spacing from a field

[ekta]

Hi All:

I have a table "tblComments". This table has a field called "Comments". I want to remove quotes and any extra spacing in this field. For example data in this field might look like this.
"ALSF/SSALR CERTIFIED"
I want to remove the quotes and the extra spacing so that it looks like this
ALSF/SSALR CERTIFIED


I would appreciate any help.

Thanx
Ekta

 

[fuzzygeek]

A good beginning

Go to the following previous discussion on a similiar issue and see the answer by Raskew. His function is a good starting point for you.

http://www.access-programmers.co.uk/forums/showthread.php?threadid=55057&perpage=15&pagenumber=1

 

[llkhoutx]

You need a program called "SpeedFerret." It will catch all the occurrences of your variable.

 

[ekta]

Thanx for replying fuzzygeek and llkhoutx

I looked at Raskew's function but I am not sure how I would use it to remove double quote and extra spacing. I am not very good with coding. I would really appreciate if you can guide me a little more.

llkhoutx, where can I get this program called "SpeedFerret"

Thanks n regards,

Ekta

 

[dcx693]

Another simple alternative is to use the Replace function. But you need Access 2000 or higher for it.

Where are the extra spaces you are looking to get rid of? In the string or at then ends?

FYI: " is Chr(34), a space character is Chr(32).

 

[ekta]

double quotes and extra spacing can be anywhere in the string.
I am using Access 2000.

Comments field might look like this


1. "Arrived at Site", "Departed from Site"

2. "CONTACTED ATCT TZ AND TOOK LOCAL CONTROL OF THE SYSTEM, Performed Weekly PM, on SYSTEM/FA999 per CONTACTED ATCT TZ AND RETURNED CONTROL OF THE SYSTEM

3. "TOOK LOCAL CONTROL OF "ALSF" FROM ATCTIC, COMPLETED MONTHLY,QUARTERLY MAINT. PER ORDER"

4. "ALSF/SSALR CERTIFIED"

I want to remove double quotes and extra spacing from these 4 records. It is not showing the extra spacing here but some words have lots os spacing. I want there to be just one space and if it is more than one space then remove it. I have thousands of records in the table. I want to check every one of them.

Thanks

Ekta

 

[llkhoutx]

SpeedFerret is for programs, not data. You can find it with google.com.

I'm great at misunderstanding questions.

 

[ekta]

Hi I have had some success in this. But I need more help.
Right now I have 2 seperate functions. One removes the extra spacing and the other removes double quotes. Then I have update queries for both functions. It does what I want seperately but I want to combine these 2 functions into one. I don't know how to do that.
Here are the functions that I have

Remove Extra Space:

Public Function SingleSpace(InText As String) As String
Dim S As String
S = InText
Do
S = Replace(S, " ", " ")
Loop Until InStr(1, S, " ") = 0
SingleSpace = S
End Function

SQL: UPDATE tblComments SET Comments = SingleSpace([Comments]);

Remove double quotes:

Public Function DoubleQuote(InText As String) As String
Dim D As String
D = InText
Do
D = Replace(D, Chr(34), "")
Loop Until InStr(1, D, "Chr(34)") = 0
DoubleQuote = D
End Function

SQL: UPDATE tblComments SET Comments = DoubleQuote([Comments]);

Thanx

Ekta