Please Help With This Code

[chandpuri]

Hi,
I have an access table with records and each record has an entry like 001#AKA. I want to remove all the characters on the left of #(inclusive #). I am using this code as an Module in Access to find the # and then doing the replacement. But it seems to be going in a loop and not doing anything. Please can you let me know what could be the problem.
Thanks

Here is my code

Sub main()


Dim ws As Workspace
Dim db As Database
Dim rsMyRS As Recordset
Dim str_rep As String
Dim txt As String
Dim fieldvalue As String
Dim x As String
Dim replacementstring As String


'Set ws = DBEngine.Workspaces(0)
Set db = OpenDatabase("C:\db1.mdb")



Set rsMyRS = db.OpenRecordset("Test", dbOpenDynaset)


fieldvalue = "001#"

If Not rsMyRS.EOF Then rsMyRS.MoveFirst
Do While Not rsMyRS.EOF

x = InStr(1, fieldvalue, "#")
replacementstring = Left(fieldvalue, x)
fieldvalue = Replace(fieldvalue, replacementstring, "")



Loop

End Sub

 

[RuralGuy]

Try this:

Sub main()

Dim db As DAO.Database
Dim rsMyRS As DAO.Recordset
Set db = OpenDatabase("C:\db1.mdb")
Set rsMyRS = db.OpenRecordset("Test", dbOpenDynaset)

With rsMyRS
   .Edit
   Do While Not .EOF
      ![YourField] = Mid(![YourField],InStr(![YourField],"#")+1)
      .Update
      .MoveNext
   Loop
   .Close
End With
Set rsMyRS = Nothing
Set db = Nothing
End Sub

...replacing YourField with the name of YourField.

 

[gemma-the-husky]

you can do this with an update query

given a field name "myfield"

update the field value to

mid(instr([myfield],"#")+1,99)

the instr finds the first occureence of #
the mid (instr + 1) returns all the characters from the next character onwards.

much quicker than recordset processing

 

[chandpuri]

Hi Thanks very much for the code. Everything runs fine but the table is not getting the new values? The table still has old values. But when I debug and see each line everything looks fine. What could be the problem?

 

[RuralGuy]

I don't think it will help but try this:

With rsMyRS
   .MoveFirst
   Do While Not .EOF
      .Edit
      ![YourField] = Mid(![YourField],InStr(![YourField],"#")+1)
      .Update
      .MoveNext
   Loop
   .Close
End With

Is the 1st record getting changed? Please post your actual code with the real names.

 

[chandpuri]

Hi,
Thanks very much it worked.

I have another problem.can you please help.

I am opening a text file and finding a string say 001# till EOF and then replacing it. Once the replacement is done I want to find another string say 003# in the complete file and replace it with another string. This way I need to find around 15 strings and replace them. And in the end close the file.

But i do not know what the problem is with the code.

My code is like this

Private Sub Main()
Dim intext As String
Dim outtext As String

Open "C:\test.txt" For Input As #1
Open "C:\certi.txt" For Output As #2

Do While Not EOF(1)
Line Input #1, intext

If InStr(1, intext, "001#") > 0 Then
outtext = Replace(intext, "001#", "|")
Print #2, outtext
End If

If InStr(1, intext, "002#") > 0 Then
outtext = Replace(intext, "002#", "")
Print #2, outtext
End If



Loop

Close


End Sub

 

[RuralGuy]

Your code will *only* output a record if the incoming record contains either "001#" or "002#". Is that what you want? Don't you have this problem posted in another thread somewhere?

 

[Guus2005]

Your code will *only* output a record if the incoming record contains either "001#" or "002#". Is that what you want? Don't you have this problem posted in another thread somewhere?

Hi RG,
Funny meeting you here.

 

[RuralGuy]

Hi RG,
Funny meeting you here.

Howdy! I believe we are both on several forums.

 

[Tanner65]

Your code will *only* output a record if the incoming record contains either "001#" or "002#". Is that what you want? Don't you have this problem posted in another thread somewhere?

Yes. I thought I had solved how to find it in the other thread, using instr and replace if I remember correctly.

 

[chandpuri]

Hi
I am trying to change the values in the txt file now but having this problem. Can you see what could be the problem.
I read a txt file which looks like this
|001#AKA|002#STO|
|001#WAR|002#STO|

After running the macro it should look like
|AKA|STO|
|WAR|STO|

Instead it looks like this:-
||AKA|002#STO|
|001#AKA|STO|
||WAR|002#STO|
|001#WAR|STO|


Here is the code

Private Sub Main()
Dim intext As String
Dim outtext As String

Open "C:\test.txt" For Input As #1
Open "C:\tested.txt" For Output As #2


Do While Not EOF(1)
Line Input #1, intext

outtext = Replace(intext, "001#", "|")
Print #2, outtext

outtext = Replace(intext, "002#", "")
Print #2, outtext

Loop

Close

End Sub



Please can you see where I am going wrong.
Thanks

 

[RuralGuy]

Try this for the main loop:

   Do While Not EOF(1)
      Line Input #1, intext
      outtext = Replace(intext, "001#", "")
      outtext = Replace(outtext, "002#", "")
      Print #2, outtext
   Loop

 

[chandpuri]

Hi,
No it did not work as now only the data after which the Print statement comes is replaced but the first record stays the same. Now the output is
|001#AKA|STO|
|001#WAR|STO|

 

[RuralGuy]

You *did* see that the source for the 2nd line is the destination of the 1st line, right?