Need assistance on Parsing Fields in a table

[Paul7905]

Background:&nbsp;&nbsp;I have an accounting system that supports inventory and sales-order(not an Access application)that was designed around an inventory record key that has to be user friendly, i.e. the &quot;key&quot; to the records has to consist of characters that a human can understand (i.e. a human has to be able to look a the key and understand what it is).&nbsp;&nbsp;This system uses a 30 character max key (which, of course, must be unique).&nbsp;&nbsp;I hate this &quot;key&quot; as I have a unique number for each item (a UPC code)but there is no way around it.<br>&nbsp;<br>Our business sells plants and trees and the sales force thinks of these items in terms of the &quot;botanical name&quot; of the plant and the &quot;size&quot; of the plant in terms of containter size.<br><br>I have built an Access 97 database table that contains all of the items we sell; there are two columns in this table (among others), one is the &quot;botanical name&quot; and the other is the &quot;container size&quot;.<br><br>I want to &quot;generate&quot;, with some code, a unique record key (30 characters maximum)for each record in the table that incorporates portions of the &quot;botanical name&quot; and the entire &quot;size&quot;.<br><br>The following is a representation of the data:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Botanical name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;size<br><br>Cercis Canadensis mexicana&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5 Gal.<br>Cercis Canadensis mexicana&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5 Gal. MRV<br>Caesalpina pulcherrima 'phoenix'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 Gal.<br>Caesalpina pulcherrima 'Sunbird'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 Gal.<br>Caesalpina pulcherrima 'Sunbird'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15 Gal.<br>Caladium bicolor&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 Gal. STK<br>caladium bicolor&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2 Gal. <br>Asparagus retrofractus&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5 Gal. <br>Cupressus arizonica galbra&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;24&quot; BOX<br>Cupressus Arizonica galbra&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;36&quot; BOX<br>Datura versicolor&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3 Gal. PRM&nbsp;&nbsp;&nbsp;<br>Ficus microcarpa nitida&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;24&quot; BOX STD<br>Ficus microcarpa nitida&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;24&quot; BOX MLT<br>Hibiscus rosa-sinensis 'brilliant'(san diego)&nbsp;&nbsp;5 Gal.<br>Hibiscus rosa-sinensis 'brilliant' (san diego) 15 Gal. MPT <br>Hibiscus rosa-sinensis 'butterball'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5 Gal. PT<br><br>I have over three thousand items and so don't wish to undertake this manually unless it is impossible otherwise.&nbsp;&nbsp;&nbsp;The &quot;key&quot; has to contain all of the &quot;size&quot; characters(without spaces) and whatever number of characters that are left over from the &quot;botanical&quot; name field, for example, taking the last item, the constructed key might appear as :&nbsp;&nbsp;&nbsp;<br><br>Hibiscusrosa-s'butterb5Gal.PT <br><br>the Ficus might appear as:<br>&nbsp;<br>Ficusmicrocarpanitida24&quot;BOXMLT<br><br>I would like this constructed key to end up in another column in the table if possible.&nbsp;&nbsp;Does anyone have any ideas ?&nbsp;&nbsp;

 

[SBerman]

It would be pretty easy to generate a little application to make an ID field from just the N letters of the size field, preceded by the 30-N remaining letters ofr the botanical field.<br><br>1) Run your size field through a &quot;space removing&quot; function to eliminate the space characters.<br>2) Determine how many characters your space-less size field is.<br>3) Do 30 - your result in #2 to tell you how many characters to take from the botanical name.<br>4) Add 3 and 1 together.<br><br>The problem is getting unique names for the very long botanical names, but using a combination of the first letters in the botanical string and the last letters in the botanical, they would be ok.&nbsp;&nbsp;&nbsp;It seems like in most botanical names, it's the species and variegate name that provide the uniqueness, not the genus.&nbsp;&nbsp;So if the botanical string emphasized the species?&nbsp;&nbsp;e.g. you used the first 3 letters, but then all the remaining letters would be the last ones in the botanical string. eg.<br>&nbsp;&nbsp;&nbsp;Would Hibsinensis'butterball'5Gal.PT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;be ok? <br>&nbsp;&nbsp;or&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Hibis'brilliant'(sandiego)5Gal.<br><br><br>Suzanne

 

[Paul7905]

Suzanne, Thanks ! <br><br>Yes, your recommendation is precisely what i need to do, unfortanately I am new to access and don't know VBA at all~&nbsp;&nbsp;i have had some success creating a form and importing the data (which I had in Excel) but now am somewhat stumped.&nbsp;&nbsp;Just how do you get rid of spaces in a field?&nbsp;&nbsp;and, then, once done, how do I &quot;pluck&quot; out the appropriate number of characters from the botanical name and then reassemble everything back into a single field (to use as the &quot;key&quot;?)&nbsp;&nbsp;You can tell where i am going with this, once i can accomplish this, i can &quot;export&quot; the table and then load it into our accounting package to prime it with inventory (which we are not using presently).&nbsp;&nbsp;I don't expect you to write the code for me but to point me in the right direction, (maybe i can use the access help topics to assist me once i know what help topics to look at)<br><br>thanks again<br>Paul

 

[Chopper]

First, add a new field to your table, call it fldKey, I am assuming you have named this table: tblProducts<br><br>Then type in a module:<br><br>-------------------------------------<br>Public Sub UpdateKey()<br><br>dim db as dao.database<br>dim rst as dao.recordset<br>dim strSize as string<br>dim strBoName1 as string<br>dim strBoName2 as string<br>dim bytCount as byte<br>dim bytPos as byte<br>dim strKey as string<br><br>set db = currentdb<br>set rst = db.openRecordset(&quot;SELECT * FROM tblProducts&quot<br><br>do while not rst.eof<br>'Create Size string without spaces<br>&nbsp;&nbsp;&nbsp;&nbsp;for bytcount = 1 to len(rst!Size)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if mid(rst!Size, bytcount) &lt;&gt; &quot; &quot; then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strSize = strSize & mid(rst!Size, bytCount)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;endif<br>&nbsp;&nbsp;&nbsp;&nbsp;next bytCount<br>'Find where a &quot;'&quot; is and grab the first 8 characters of that<br>&nbsp;&nbsp;&nbsp;&nbsp;bytPos = instr(rst![Botanical name], &quot;'&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;if bytPos &lt;&gt; 0 then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strBoName2 = Mid(rst![Botanical name], bytPos, 8)<br>&nbsp;&nbsp;&nbsp;&nbsp;else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strBoName2 = &quot;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;endif<br>'Create Botanical Name string without spaces<br>&nbsp;&nbsp;&nbsp;&nbsp;for bytcount = 1 to len(rst![Botanical name])<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if mid(rst![Botanical name], bytcount) &lt;&gt; &quot; &quot; then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strBoName1 = strBoName1 & mid(rst![Botanical name], bytCount)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;endif<br>&nbsp;&nbsp;&nbsp;&nbsp;next bytCount<br>'Grab what is left over of the botanical name<br>&nbsp;&nbsp;&nbsp;&nbsp;strBoName1 = left(strBoName1, (30-len(strSize)-len(strBoName2))) <br>'Assign the key string<br>&nbsp;&nbsp;&nbsp;&nbsp;strKey = strBoName1 & strBoName2 & strSize<br>'Edit the record<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.edit<br>&nbsp;&nbsp;&nbsp;&nbsp;rst!fldKey = strKey<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.update<br>'move to the next record if there is one<br>&nbsp;&nbsp;&nbsp;&nbsp;if not rst.eof then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.movenext<br>&nbsp;&nbsp;&nbsp;&nbsp;endif<br>loop<br><br>rst.close<br>set rst = nothing<br>set db = nothing<br><br>end sub<br><br>-------------------<br>Now, save and debug the module<br>open the Debug Window, and type the name of the sub procedure: Update Key<br>Hit enter, and the table will be updated with the key <p>-Chopper<br><a href=mailto: > </a><br><a href= > </a><br>

 

[Paul7905]

Chopper, thanks so mucn for the code.<br><br>I put the code in and ran it, it seems to run and process several records and then it hangs on the instruction :<br><br>'Grab what is left over of the botanical name<br>&nbsp;&nbsp;&nbsp;&nbsp;strBoName1 = Left(strBoName1, (30 - Len(strSize) - Len(strBoName2)))<br><br>looking at the first few records in the table (up to the record where the instruction hangs, it seems to be propagating a portion of the Botanical name in the first record through the other records, i.e. part of the botanical name in the first record appears in subsequent records in the (key) that is being constructed.&nbsp;&nbsp;I don't understand why it does not continue processing the entire table but hangs on the fourth record?&nbsp;&nbsp;&nbsp;&nbsp;The error says (Runtime Error '5' invalid procedure call or argument)<br>when i click on &quot;debug&quot; it highlights the aforementioned code in yellow.&nbsp;&nbsp;any ideas on what i am doing wrong? <br>Thanks <br>Paul