TEXT FILE TO ACCES AND FORMAT 1

[kaniz]

I have imported a textfile
this is is sample of the TEXT FILE

MAY, 5 ,2001,Rocky, ,5555, T 89

I have a form with the following event to remove blanks but it is not working properly

when I view the table there is still space on space in between
for example T 89

Can someone please help me figure out what I am doing wrong

Private Sub Export_File_Click()
Dim MyDb As Database, MySet As Recordset, stringin As String, pos As Integer
Dim charremove As Variant
Dim strin As String
charremove = left(charremove,1)

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDb.OpenRecordset("labor&quot

MySet.MoveFirst

Do While Not MySet.EOF

strin = MySet.Fields("FIELD7&quot
pos = InStr(1, strin, charremove & charremove)


Do While pos
strin = Left(strin, pos - 1) & charremove & Mid(strin, pos + 2)
pos = InStr(1, strin, charremove & charremove)

Loop



MySet.Edit
MySet.Fields("FIELD12&quot = strin
MySet.Update
MySet.MoveNext
Loop


End Sub

 

[MichaelRed]

well, I'm not going to reproduce all of it, but uyou should be able to "plug' this in to get the 'no spaces' version of your string. At least as far as I can tell from your post.

Public Function basStripSpace(strIn As String) As String

    '? basStripSpace("MAY,        5    ,2001,Rocky,     ,5555,   T        89")
    'MAY,5,2001,Rocky,,5555,T89

    Dim MyVar As Variant
    Dim tmpStr As String
    Dim tmpWord As String
    Dim Idx As Integer
    Dim Jdx As Integer

    MyVar = Split(strIn, ",")               'Place items in array by delimiter

    
    For Idx = 0 To UBound(MyVar)
        tmpWord = Trim(MyVar(Idx))          'Trim 'external' spaces
        For Jdx = 1 To Len(tmpWord)         'Walk throuhg the "word"
            tmpChr = Mid(tmpWord, Jdx, 1)   'Get chr
            If (tmpChr <> &quot; &quot;) Then         'Check for space
                tmpStr = tmpStr & tmpChr    'Not Space, add to temp
            End If
        Next Jdx                            'Loop
        MyVar(Idx) = tmpStr                'Replace &quot;squeezed word' in Array
        tmpStr = &quot;&quot;
    Next Idx

    tmpStr = Join(MyVar, &quot;,&quot;)

    basStripSpace = tmpStr

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

If you have Access 2000 you can use REPLACE.

MyString = Replace(MyString, " ", "", 1, -1)

See Access Help for more info. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[kaniz]

Thank you both of you for your reply. I don't have access 2000 unfortunately. Michael I am not sure how can I use the function above. I am not passing any parameters. My code is under the onclick event.
I have another variation of the code. This one seems to work for some of the records for some other it does not work.It seems to take the 2 number out. For example

D 01
S 45
Became D1
Became S5

Yet the other records seem to be fine. Any other suggestions
please


Dim MyDb As Database, MySet As Recordset, stringin As String, pos As Integer
Dim charremove As Variant
Dim strin As String
Dim frmfld As String
Dim tofld As String
charremove = " "
Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDb.OpenRecordset("labor&quot

MySet.MoveFirst


Do While Not MySet.EOF
MySet.Edit

strin = MySet.Fields("field7&quot
MsgBox pos

Do While InStr(1, strin, " &quot
pos = InStr(1, strin, " &quot
strin = Left(strin, pos - 1) & Mid(strin, pos + 2)
'left will return the first character
'mid will return
MsgBox strin
'pos = InStr(1, strin, charremove & charremove)

Loop
'MsgBox pos


'MySet.Edit
MySet.Fields("field7&quot = strin
MySet.Update
MySet.MoveNext
Loop


End Sub

 

[MichaelRed]

Look (Carefully!) at the below.

the first block of code is a "helper" function. It accepts any string and returns a string with NO spaces (although 'non-printing' characters may be returned which APPEAR to be spaces).

The SECOND block is a significant revision of your code which uses the FUNCTION to do the space removal. There were several additional variable which appeared to NOT be used in this code, so I removed them. One var StringIn appeared to be unused, and was retained for use but only for 'clarification'.

IF I understand your intent, the original function is not really necessary, as the Function basNoSpace could easily be used as the updateto clause of an update query.

Public Function basNoSpace(strIn As String) As String

    Dim Idx As Integer
    Dim strTmp As String
    Dim strChr As String

    For Idx = 1 To Len(strIn)
        strChr = Mid(strIn, Idx, 1)
        If (strChr <> &quot; &quot;) Then
            strTmp = strTmp & strChr
        End If
    Next Idx

    basNoSpace = strTmp

End Function

[/code]
Public Sub basFixField()

Dim MyDb As Database
Dim MySet As Recordset

Dim StringIn As String
Dim StrIn As String

Set MyDb = CurrentDb
Set MySet = MyDb.OpenRecordset("labor", dbOpenDynaset)

Do While Not MySet.EOF

StringIn = MySet.Fields("field7&quot
StrIn = basNoSpace(StringIn)

Loop

With MySet
.Edit
.Fields("field7&quot = StrIn
.Update
.MoveNext
End With

Loop

End Sub
[/code]
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over