@Seth: I used the string functions because array functions end to be harder for new programmers to grasp. Personally, I would have used the split() (^_^)
@kbarrett:
Your explanation certainly helps and so long as the data you want will always follow "a(#-#)" format then the function will be very easy to construct.
Your field names contain a dash "-" this is a reserved charactor potentially leading to issues down the line. It would be best practice to change those to an underscore.
(
Access 2007 reserved words and symbols AllenBrowne- Problem names and reserved words in Access )
I also want to be sure of what it is that you want, so if you will allow me to repeat back to you what I think I understand (^-^)
table one: EDD
[FIELD-ID] text
[UpperDepth]
[LowerDepth]
[other fields]
table two: samplemaster
[FIELD-ID]
[other fields]
Example record:
Table: samplemaster
[FIELD-ID.......][....other data fields....]
[HB13-02 (6-32)][...]
You want to take this record to:
Table: EDD
[FIELD-ID].......[UpperDepth]...[LowerDepth][....other data fields....]
["HB13-02 (6-32)]...[6]..............[32].......[....]
You do not want to alter the origninal [FIELD-ID] value in either table
Please don't take this wrong; however, we prefer to teach how to fish first rather that just tossing a fish out.
I'm not sure of your skill level, so I'm going to
start at the top outline level and if you need more help then we'll go from there. Part of this is because my lunch hour is up ( :'( sigh ) and partly because if you learn how to do this you'll have a cool set of tools for the next situation!
The basic outline will be
Open a
DAO recordset on Table: samplemaster, as we appear to need only the [FIELD-ID] we need only include that in recordset.
Open a DAO recordset on Table: EDD, as we appear to need only the [FIELD-ID], [UpperDepth], [LowerDepth] we need only include those in recordset.
Move to the first record in each sample set.
findfirst [samplemaster]![FIELD-ID] on the [EDD]![FIELD-ID]
If the record is found then the extracted results will be updated to that record, else append a new record to [EDD]
I am going to use the split() and arrays here as they are (as seth noted) a bit more effecent:
Using the
split function on "(" to return an array with two elements ( "HB13-02" , "6-32)")
Take the len of the second element, return
left(len(first_array(1)-1)
to strip the ")" ==> 6-32 you can store it back into the second element of the first array.
Feed this "6-32" into split again returning a new array with the two elements ("6","32") now notice the quotes, these are currently being treated as string datatypes.
You can now take these two elements, recast as long (clng()) or to the datatype you have set for [EDD]![UpperDepth] and [EDD]![LowerDepth]
Either Store these values in the currently selected record in [EDD] or append as new record.
Move to next record in recordset samplemaster, repeat.
NOTE: When opening the recordset, I highly advise that you either use a stored query, or build the string first and use the built string. Most errors when opening a recordset occur due to an error in the string. Building the string first makes it easier to troubleshoot. (^_^)
Another thing, note the first element of the first array, if you wanted to change the [FIELD-ID] to just "HB13-02" in both tables, now would be a good time - keeping in mind that if you have relationships between tables, then altering the primary key values can cause issues.
Try to code this yourself... if you run into a wall, please read thru
> Before Posting (VBA or SQL) Code for some suggestions. If those don't help then post your code back here (click on the [CODE/] button in the post toolbar first and then paste the script between the [code] tags).