Trim but using an expression / identifier ??

[secondangel]

Hi i want to righttrim from a field however each record is going to be of different lengths as its full of text.

I know the point i want to start as right at the end of the record is a -- character followed by some text.

I wish to trim everything after the -- to the end of the record.

I presume rtrim$ is useful but you have to put a number of how many characters to trim. I dont know how many characters.

As the text after the -- is also variable how do i assign it to look and trim after this --

SO basically serach the record, as soon as you reach the -- then delete all text after it.

Will i need to code a vb module.

Any help much appreciated

Ashley

 

[KenHigg]

InStr() should find the postion of the '--'. Then you simply do the math

 

[kenwarthen]

Ashley.

You could use a function like the following.

Public Function fTrimString(strInput As String) As String
Dim intStringLength As Integer
Dim intDoubleDashPosition As Integer
Dim strNewString As String

intStringLength = Len(strInput)
intDoubleDashPosition = InStr(strInput, "--")
strNewString = Left(strInput, intDoubleDashPosition + 1)
fTrimString = strNewString

End Function

Ken

 

[boblarson]

Just an FYI - RTrim trims SPACES from the right of any field - not other characters.

 

[secondangel]

Thanks so much for your help

Whats the best way to call the function bearing in mind im automating a lot of this through form buttons / macro and procedure calling.

I.e an external text file is imported into a table and then i run a find/replace and several sorting bits all through the button press code.

Should i drop the function in here or should i (probs best practice) set the filename as the variable and call teh function

i.e fTrimString(importfile)


edit: within the button press having imported the text file and then with the table open in datasheet view i do the following lines

Plot.SetFocus
fTrimString (Plot)

It deosnt work but doesnt give me an error either

Sorry for being so dim

Thanks so much for your help. Its saved me some hair on my head.

Ashley

 

[kenwarthen]

Ashley,

Put the fTrimString function into a code module.
Create a command button on the appropriate form and enter the following code into the On Click event of the command button. — Ken

Private Sub cmdProcessImportFile_Click()
Dim rs As DAO.Recordset
Dim strImportText As String
Dim strNewText As String
Dim intRecordCount As Integer

intRecordCount = 0
Set rs = CurrentDb.OpenRecordset("tblImportFile")
With rs
.MoveFirst
Do Until .EOF
intRecordCount = intRecordCount + 1 'Increment the record count
strImportText = .Fields("txtString") 'Read the imported text value
strNewText = fTrimString(strImportText) 'Process the text
.Edit
.Fields("txtString") = strNewText 'Update the record to the processed value
.Update
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing

MsgBox intRecordCount & " record(s) were processed.", vbOKOnly + vbInformation, "Processed Record Count"

End Sub

 

[secondangel]

Wow, thanks muchly. I did have the code already in a function module.

Clicking the button gives me

jet engine error saying it cant find the object


Set rs = CurrentDb.OpenRecordset("tblImportFile")


obvbiously i called it my actual file - which is called DVDIMPORTFROM

Set rs = CurrentDb.OpenRecordset("DVDIMPORTFROM")

 

[kenwarthen]

Ashley,

The line:

Set rs = CurrentDb.OpenRecordset("tblImportFile")

assumes the file has been imported into an Access table called tblImportFile. The rest of the code then processes each record's txtString field. In your application you'll need to import the text file first and then modify the code changing the name of the table from tblImportFile to whatever you name your table. You'll also change the field name txtString in the code to whatever field in your imported table has the string with the double hyphens your trying to process.

Ken

 

[secondangel]

Ashley,

The line:

Set rs = CurrentDb.OpenRecordset("tblImportFile")

assumes the file has been imported into an Access table called tblImportFile. The rest of the code then processes each record's txtString field. In your application you'll need to import the text file first and then modify the code changing the name of the table from tblImportFile to whatever you name your table. You'll also change the field name txtString in the code to whatever field in your imported table has the string with the double hyphens your trying to process.

Ken

Thanks Ken

my field is called Plot
and my table that the text is imported into is called DVDIMPORTFROM

it still gives the runtime error 3078




i tried having the table open and moving the focus to it but i thought that wasnt needed for recordset

Really sorry im being a pain - thanks so much for your help

 

[kenwarthen]

Ashley,

If the file has been imported into a table called "DVDIMPORTFROM" and the field you need to process is called "Plot". Make the following changes to the code in the On Click event of the command button on your form where the processing will occur. You will not have to do anything manually in the DVDIMPORTFROM table. — Ken

Private Sub cmdProcessImportFile_Click()
Dim rs As DAO.Recordset
Dim strImportText As String
Dim strNewText As String
Dim intRecordCount As Integer

intRecordCount = 0
Set rs = CurrentDb.OpenRecordset("DVDIMPORTFROM")
With rs
.MoveFirst
Do Until .EOF
intRecordCount = intRecordCount + 1 'Increment record count
strImportText = .Fields("Plot") 'Read text value
strNewText = fTrimString(strImportText) 'Process the text
.Edit
.Fields("Plot") = strNewText 'Update the record
.Update
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing

MsgBox intRecordCount & " record(s) were processed.", vbOKOnly + vbInformation, "Processed Record Count"

End Sub

 

[secondangel]

Hi Ken

That is exactly what i did but i still get the jet engine error.

which is strange - and the debug line is highlihted yellow on that line

Set rs = CurrentDb.OpenRecordset("DVDIMPORTFROM")


Is there something somweherelse i may have a problem with

Im using access 2003 but figured allwas ok.

Is there a way i can check the db at all ?

Many Thanks

 

[kenwarthen]

Ashley,

I think you're almost there. In the VB editor click on the menu item Tools, and select References. You'll see a list box of available references. Find the item called, Microsoft DAO 3.6 Object Library and select it. I'm running Access 2007 and can't remember the 2003 library objects, but if you see the Microsoft Access Object Library and Visual Basic for Application items, select them as well. Click the OK button. Your code should now work.

Ken

 

[secondangel]

Hi Ken

They are all ticked as shown in the picture

As i have the office disk with me do you reckon i should pop it in and see about maybe reinstalling the object libraries ?

Ashley

 

[boblarson]

Hi Ken

They are all ticked as shown in the picture

As i have the office disk with me do you reckon i should pop it in and see about maybe reinstalling the object libraries ?

Ashley

The other thing that could be wrong is - do you have a table or query actually named DVDIMPORTFROM?

 

[secondangel]

pic attached

 

[secondangel]

The other thing that could be wrong is - do you have a table or query actually named DVDIMPORTFROM?

I do yes. Its a table with about 23,00 entries in.

 

[kenwarthen]

Ashley,

Before you do that try changing the code to the following. I think some of the Access 2003 is coming back to me.

Remove the line:
Dim rs as DAO.Recordset

Replace with the following two lines.

Dim db as database
Dim rs as recordset

Remove the line:
Set rs = CurrentDb.OpenRecordset("DVDIMPORTFROM")


Replace with:
Set db = currentdb
set rs = db.openrecordset("DVDIMPORTFROM ")

Save the changes and try running the code again.

Ken

 

[boblarson]

If you have

Dim rs As DAO.Recordset

and you have a reference to Microsoft DAO 3.x (where .x is like 51 or 6 )

and you have a table named DVDIMPORTFROM

Then the next thing to try is to import all objects into a new, blank MDB file.

Or, if you wish, you can post your db here and we can take a look.

 

[secondangel]

It works past the line now but get an error on the following line ( maybe becuase i forgot to say that there are sometimes empty in the plot field)


strImportText = .Fields("Plot") 'Read the imported text value

Error was

invalid use of null

 

[boblarson]

Ashley,

Before you do that try changing the code to the following. I think some of the Access 2003 is coming back to me.

Sorry, I actually I don't think it is.

Remove the line:
Dim rs as DAO.Recordset

Replace with the following two lines.

Dim db as database
Dim rs as recordset

No, that is not correct. Access 2003 sets BOTH DAO and ADO as defaults checked and you DO need explicit referencing.

Remove the line:
Set rs = CurrentDb.OpenRecordset("DVDIMPORTFROM")


Replace with:
Set db = currentdb
set rs = db.openrecordset("DVDIMPORTFROM ")

Sorry, but again you don't need a space after the name regardless of the version of Access.