Delete all the characters on the left of # in a recordset

[chandpuri]

Hello,
We have an access database where we import daily data via txt file. The data is seperated with pipes. Each record has a value and before that value we have a number like 001#. This is in all the records. We have about 250,000 records in each file. I tried to replace the characters in the txt file before importing into access but I get import export errors while importing. So now I need to know how can we write a program which opens the access table and finds # and replaces all the characters to its left.
Ur help will be greatly appreciated.
Thanks

Best Regards,
Chandpuri

 

[Tanner65]

Well, I'm not quite as familiar with the Access side of it, but I have done something similar in Excel.

It's going to be a combination of the instr, left and replace functions. From what I can tell so far, the 001# is before the value you need. So, we know what to look for, #, and use the instr function to return the location of the # in the string.

x= instr(1,fieldvalue,"#")

Since instr returns the location of the # in that string we can use the left function to get the information to the left of the #:

 replacementstring = left(fieldvalue,x)

Then since we now have the 1 piece of information you need to rid yourself of the numbers, you can use replace:

 fieldvalue = replace(fieldvalue,replacementstring,"")

So essentially you could combine those into one line fluid line in:

 fieldvalue = replace(fieldvalue,left(fieldvalue,instr(1,fieldvalue,#),"")

Now, if you need the # to stay, just have the left function be:

 left(fieldvalue,instr(1,fieldvalue,#)-1)

Granted this is all in theory. I'm not quit sure how're you going to loop through the fields but someone else may be able to help you with that.


Also, if you are having too much difficulty trying this way, it should be possible to do a Visual Basic Script using nearly identical functions (same names, different requirements, I think) to edit the text file before importing.

 

[chandpuri]

Hi
Thanks for your reply. 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:\Gagan\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

 

[Tanner65]

Hi
Thanks for your reply. 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:\Gagan\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

Fieldvalue needs to be the current fields value, not the string value.

I'll need to check when I get to work in a few hours, but it seems as though you might need a for next, or a do loop to go through the fields. And for each field the value in the current field will be fieldvalue in the strings I had shown you.

 

[chandpuri]

Thanks Mate
With field value you mean the column name of the field or the values in the field like 001#AKA.

 

[chandpuri]

I put the name of the field in the field value eg field1.
The program runs but nothing changes in the table

 

[Tanner65]

Try something like this:

Do While Not RS.EOF
If RS.EOF then Exit Do 'May not be necessary.
fieldvalue = RS!FieldName.Value
RS.Move (0) 'To requery the field for editing
RS!FieldName.Value = Replace(fieldvalue, Left(fieldvalue, InStr(1, fieldvalue, "#")), "")
RS.MoveNext 'To move to the next field
Loop

For integerity reasons, you're not going to want to run this without making sure it's doing it's job correctly. So, be sure to go line by line through it a few times before letting it go to make sure it's not going to be stuck in an infinite loop.

 

[Tanner65]

Thanks Mate
With field value you mean the column name of the field or the values in the field like 001#AKA.

The value of the field. In my code, the RS!FieldName is the column name.

 

[chandpuri]

Hi Thanks very much
The code looks great but I am getting an run time error 3265 that Item not found in the collection. The error is on line
fieldvalue = rsMyRS!FieldName.value

Here is the 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 Integer
Dim replacementstring As String
Dim value As String
Dim FieldName as String

Set db = OpenDatabase("C:\Gagan\db1.mdb")
Set rsMyRS = db.OpenRecordset("Test", dbOpenDynaset)

Do While Not rsMyRS.EOF

If rsMyRS.EOF Then Exit Do

fieldvalue = rsMyRS!FieldName.value

rsMyRS.Move (0)

rsMyRS!FieldName.value = Replace(fieldvalue, Left(fieldvalue, InStr(1, fieldvalue, "#")), "")

rsMyRS.MoveNext

Loop

End Sub

 

[chandpuri]

Sorry Mate. Please ignore my previous posting. My fault I made the changes and put the correct field name. The program runs fine but the values in the table is not changing. I have put Msg Box to see all the process and it looks fine. but the values in the table is not getting changed. any particular reason??