Removing two spaces from a string

[Talismanic]

I am having some problems figuring this out. I want to remove two spaces from a string if they are present. The string is in a table that is imported from another program which won't allow me to make the changes there.

The typical string I am dealing with looks like this K345-, but the accounting department wants to break down job costs even more so they are adding letters to the end of the string starting with A. The program that they use (Unix Based) exports the number like this K345-   A. I need to loop through the table and convert the job numbers with the double space and the letters down to this K345-A.

Any ideas? I can manage the loop but I am not sure how to test for the double space and then remove it.

 

[D-Fresh]

Use a string of characters that is allowed and compare each letter to that string... If it matches, build a new string.. Like so..


dim AllowedCharacters as string
dim TempString as string
dim ctr as integer

AllowedCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-"

'Open your table here and loop through
While not Table.EOF
TempString=0
for ctr = 1 to len([FieldName])
If InStr(1, AllowedCharacters, Mid([FieldName], ctr, 1)) <> 0 Then TempString = TempString & Mid([FieldName], ctr, 1)
next
'Now Edit the table and replace the value of [FieldName] with TempString
wend

This will spike out any characters not on your list... If there are more characters that are allowed, just add it to the list... Just make sure you keep the spaces out. Hope this helps.

Doug

 

[SimonP]

The LTrim$(str) will remove all spaces before the string. RTrim$(str) does the same (after the string). Set a variable as your field using the Left$(str, 6) and one as Right$(str, 1), add this code into the button thats performing the change and you should be able to do this.

HTH - Simon

 

[Talismanic]

I must be doing something wrong when I open my table because I am getting an error on the bold line below.

Dim objDB As Database
Dim objRS As Recordset
Dim AllowedCharacters As String
Dim TempString As String
Dim ctr As Integer

AllowedCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-"

Set objDB = CurrentDb()

'Open your table here and loop through

Set objRS = objDB.OpenRecordset("Jobs", dbOpenDynaset)

While Not objRS.EOF

TempString = 0
For ctr = 1 To Len([JobNumber])

The error say that Access can't find the field it may of been deleted or is spelled wrong. Neither is the case, where did I go wrong?

[This message has been edited by Talismanic (edited 07-02-2001).]

 

[D-Fresh]

You are referring to the table's Fieldname, so you have to use the correct syntax... Use this instead...


For ctr = 1 To Len(objRS![JobNumber])

Hope that fixes it for you.

Doug

 

[Talismanic]

That did, D-Fresh but I have one more question. How should the update look? I have this:

objRS![JobNumber] = TempString

Access is looking for a Addnew or Edit near that update.

 

[D-Fresh]

You should use the following...

with objRS
.edit
![JobNumber] = TempString
.update
.movenext
end with

This will update your current record and move on to the next record.

Doug

 

[Talismanic]

That worked, thanks again.

There is a 0 being added to the beginning of each number like this 0K333-A. What is causing that?

[This message has been edited by Talismanic (edited 07-02-2001).]

 

[D-Fresh]

Oh, my fault... When you initalize the variable TempString, you have to set it to an empty string, "", not 0. Use this line...

TempString = ""

You know, I thought I changed it before.. Sorry for the confusion.

Doug

 

[Talismanic]

I thought that was the problem and had even tried changing it to a empty string but the zeros were still there. Then when you replied to my question I realized I never reset my table to its original state so I was getting the same results.

Anyway, a big duh to me and a final thanks to you.

 

[DALeffler]

If the only thing you have to worry about is a double space in the string, try this:

'may not work if " " is first or last...
TempStr = mid([fieldname],1,Instr(1, [FieldName]," ")-1) & mid([fieldname], instr(1,[fieldname]," ") + 2)

Avoid 'dem loops at all costs - may not be possible here...

Doug (love's 'dem strings...)