parsing a single field

[niki]

Hello,
I have a database with a field containing mutliple words separated by comas .
ID Keywords
1 steack, meat, tomato
2 car, porsche, ford
3 food, egg, cow


I want to parse this keywords field and to transform this database into another database with a single entry for each keyword. like this:
ID Keywords
1 steack
1 meat
1 tomato
2 car
2 porsche
2 ford
3 food
3 egg
3 cow

I know how to parse the keywords field and input each keyword in a separate field in a single entry by using the code at this adress : http://www.mvps.org/access/strings/str0003.htm
But its not of much use because, afterwards, I want to compare the keywords field with another keywords field to find matches....

Can someone help me?

 

[PookaTech]

I skimmed the code at the access web, and don't understand why you can't use it. Could you explain what you need to do that it does not do.

Thank you,
Pookatech

 

[niki]

well pookatech,
this code is only gonna parse one word and put it in the field the function is used in. For example I can't use this function on a single field, and put as an argument multiple rows.... It only works for one row doesn't it?

 

[PookaTech]

Well niki,

Here is some code for you. This is a funtion that returns no value. It simply takes each record in the old table and parses the words into a new table. To look like the columns you showed in your example. I have named my table that has the old Keywords "tblOldKeywords". The columns in the table that are important are "OldKeywordID" this is the field you named ID, and "OldKeyword" this is the column containing the field that needs to be parsed. I have named my table that will contain the new keywords "tblKeywords" it has two fields "keywordId" which the old keyword ID will be put in and "Keywords" which each parsed keyword will be put in.

The code simply creates a new record in tblKeywords for each word it parses from the old records.

Paste the funtion in a module. Set the table and field names to the ones you would like to use and let it rip.

If you are having any problems let me know.

Pookatech



Function ParseField()

On Error GoTo ParseField_error

Dim bytWC As Byte
Dim bytStringStart As Byte
Dim bytStringEnd As Byte
Dim dbCurrent As Database
Dim rstNewKeywordTable As Recordset
Dim rstOldKeywordTable As Recordset
Dim Keyword As String
Dim KeywordId As Long
Dim Index As Long


'Set a reference to the old and new keyword tables
Set dbCurrent = CurrentDb
Set rstNewKeywordTable = dbCurrent.OpenRecordset("tblKeywords")
Set rstOldKeywordTable = dbCurrent.OpenRecordset("tblOldKeywords")

'start with the first record for the old keywords
rstOldKeywordTable.MoveFirst

'set the function to run for each record in the old table
For Index = 1 To rstOldKeywordTable.RecordCount

'set variables to old keyword Id and old Keyword
KeywordId = rstOldKeywordTable![OldKeywordID]
Keyword = rstOldKeywordTable![OldKeyword]

'If the string is not zero length start parsing it
If Not (Len(Keyword) = 0) Then

'Set initial variable values for each record to be parsed
bytStringStart = 1
bytStringEnd = 1

'parse the field until there are no more words to be parsed
Do While bytStringEnd > 0

'Add a new record to teh new keyword table, then set the Id Number for the record
rstNewKeywordTable.AddNew
rstNewKeywordTable![KeywordId] = KeywordId

'Find the end of the word you are parsing out
bytStringEnd = InStr(bytStringStart, Keyword, ",")

'parse the string and set the keyword in the table equal to the parsed string
If bytStringEnd = 0 Then
rstNewKeywordTable![Keywords] = Trim(Mid(Keyword, bytStringStart))
Else
rstNewKeywordTable![Keywords] = Trim(Mid(Keyword, bytStringStart, bytStringEnd - bytStringStart))
End If

'Update the New Table
rstNewKeywordTable.Update

'set the new start of the string right passed the last comma
bytStringStart = bytStringEnd + 1

Loop

End If

'move to the next record in the old table
rstOldKeywordTable.MoveNext

Next Index

Exit Function

ParseField_error:
MsgBox "Something went wrong"


End Function

 

[niki]

hey pooka,
thx for your answer!!
but when I debug the code I have small declaration problem though:
-Dim dbCurrent As Database: Database is not recognised as a valid type

Do I have to create the tblKeywords before running the query based on the parsefield function?
Its quite late here so I'll drop it for today but I'll be back tommorrow!
thx again!!
nico

 

[PookaTech]

Hey niki,

The problem is that your computer isn't recognizing the datbase object type because it doesn't have the right references. Sort of like trying to look up the word "Automobile" when all you have is a chinese dictionary.

Here's how you give your computer the right dictionary.

open any module. Select tools from the menu bar and then select references. (I am using A97 bu this should work with any version) you will need to find the references "Microsoft Access 8.0 Object Library" and "Microsoft DAO 3.51 Object Library" and check the boxes next to them. Then hit ok and the references box should close.

IF you have a different version of Access you might already have a version of "Microsoft Access *** Object Library" or Microsoft DAO **** Object Library". I think any later version of these libraries would be fine 9.0 or 3.52 etc. I also don't know which one is causing the problem, although I would guess it is the Access one.

As for your other question yes you needed to already have the table created. I will send you a database with the code. Open the module and read the directions. You still need to do the directions above first. And you must import the module into your database to run it.

Good Luck

 

[PookaTech]

Looks like you don't accept emails.

 

[niki]

hey pooka!!
thx for all of this, that's more than enough, you are the king. I think I'll get through this problem with everything you explain here. That will make a classic case for people who encounter the same problem!!
thx again!

 

[niki]

well I have a problem there the function's error routine shows up when I try to run it... I named my two tables same way as you except for the s at the end and my table fields are named correctly.... I dont get it...
I can't attach the mdb cuz its too big, PM me your email so that I can send it, its only 700 kO and there are only two entries... dunno why it doesn't work though...
I am not sure whether I am a complete idiot, but I am not 100% sure on the way to run the ParseField function, should I run it with a macro? Because, regarding what you tell me in the comments before the function itself, I push the "run" button and I don't have any "go" button to push after that. What I have is a dialog box which asks me which macro I want to use for this function. So when I go all the way through the debugging, that's when I get the error code...
Therefore, I tell access to run it with a macro. This adds a sub line after the function which calls the macro. Then I create the macro which I parameter to "run the code" and to call the function "Parsefield()". This returns me that the function is not known by acces, even if I paramater the macro to use this function with the wizard...
Could'nt all my problems be due to the fact that I import your mdb file to access 2000 and that the function becomes unusable???
this leaves me speechless... what do you think?
thx 4 everything
nico

 

[PookaTech]

Send it to Pookatown@aol.com.

I will not be able to look at it until I get home tonight though, as I can't use my work email for this site and I can't access my aol from work. The web security here doesn't allow it.

I will get back to you sometime this weekend. Zip it if you can.

Hope to hear from you soon,
Pookatech

 

[PookaTech]

I accidentally deleted your email. So I am trying to post the database back to you.

The problem was I was expecting you to be parsing text fields with a max of 255 cahracters, but you wre parsing a memo field. There were were also problems because the fields started with ";" as well as the fact the code I had set up only worked for ",". Now it works for "," and ";" and ":". It leaves a couple blanks because it expects a word before the ";" in the field and only finds a zero length string, but a quick delete query should be able to purge the blank records.

Let me know how it goes.

Pookatech

 

[niki]

hey pooka!! you are the man!!!!!
The function works perfectly!!!
thx again for the great work!! I f you are ever to be in paris email me, I'll buy you dinner!!
nico