I need a smiple Module to do an advanced update to an existing data file

[thealy]

It has been many years since I have done any Access programming. I typically use SQL but I have some rather complex string manipulation that I need to do to one column in a file and the file is larger than Excel can handle. The problem is that I don't remember the first thing to creating a Module in Access. If someone could provide a simple but complete module sample that would first open a file and loop through the file reading in every row and performing some simple modifcation to a string column, that would make my day. Thanks in advance!!!

 

[Sergeant]

I don't know if I can help, but you can help get your answer by clarifying what kind of 'data file' you are talking about.

 

[thealy]

Thanks,

My file is already in MS Access. I already imported my text file into MS Access and now I just want to run a routine that updates a field in the current file. For example purposes, what I need to do is to change the following Text field from "abc#def#ghi#" to = "3". "3" indicates how many "#" are contained in the field.

 

[KeithG]

You need to create a custom funtion with VBA to count the # signs. Something like below should work.

Public Function CountOf(Text As String) As Long
    
    Dim strText As String
    Dim lngCnt, lngSize As Long
    
    strText = Text
    lngSize = Len(strText)
    
    For X = 1 To lngSize
        If Mid(strText, X, 1) = "#" Then
            lngCnt = lngCnt + 1
        End If
    Next X
    
    CountOf = lngCnt
    
    
End Function

 

[thealy]

Thanks for your response. The problem that I am struggling with is how to perform that function on each row in my MS Accesss Data file. I assume I somehow need to read in each row from my data file, lets call it (myfile), and update the field called (myfield). In SQL I would simple perform and UPDATE statement but becuase the logic that I need to do on the Text value (myfield) is more complex than SQL can handle I need to be able to do this via a function. I don't so much need assistance with the code to determine the new text string as I do with reading the file in and performing an update on each row. Hope this helps to clear it up some. Thanks again.

 

[KeithG]

So basically you want to import the file into access do your string manipulation than export the file out of Access? If so to what format? A text file? I would paste the funtion I wrote into a module and then use the funtion in a query. Then you can export your query to the external format. Is there a reason why you must change the actual field?

 

[thealy]

Thanks Keith. The part I was missing was that I could use that Function in a Query (yeah, I am pretty Access green). This is perfect. Thanks for your help!!!

 

[KeithG]

Glad I could help