Query to extract part of one field to populate another

[kbarrett]

I am a beginning Access user and have an Access db with a field that is populated with a sample id plus sample depths in parenthesis. An example value looks like "HB13-02 (6-32". I need to extract the sample depths ( 6 and 32 ) to populate two other fields, UDEPTH and LDEPTH respectively. I can't seem to find the right function to do it.

Thank you for any help.

[zmbd]

We're going to need a tad more information than that.
Only one example makes it very difficult to provide anything except a guess.

You could help us out if you tell us how the table is designed. If there is already a field with the sample ID we can use that information to help locate your data.

You can help us by telling us how this information came to be... did you import this from a text file, an excel file, or something else?

I would suspect that you will need to write a custom function in VBA to extract the values you need.
Based on the one example.
I'd len to return the string length
I'd instr to find the first "("
I'd instr to find the "-"

Then using this information you can midstr to get the start and length of the first numeric and midstr to get the second numeric.

If you will provide the above information
and if you write any code (check out > Before Posting (VBA or SQL) Code for some basic trouble shooting steps ) please post it back (formated with the [CODE/] button please) and we'll help tweek things.


As you've indicated that you are very new to Access I'll PM you a copy of a list some basic tutorial sites and other links that you might find usefull as you develop your application.

[Seth Schrock]

Another suggestion, again based on the one example, would be to use the Split() function.
Split on the (
Then Split the upper bound of the first array based on the -

This second split would then hold the values you need without the confusing nested instr functions.

[kbarrett]

Sure - First let me correct something. The field that contains the necessary information is called FIELD-ID, not sample-id.

The database contains information on sediment samples collected last fall. The data were provided from the laboratory that ran the analysis on the samples and has 2 tables: 1) EDD (electronic data deliverable) with all the information on the samples. The primary key is FIELD-ID; 2) samplemaster, that lists the unique sample-ids, collection date and time, and also FIELD-ID.

The FIELD-ID contains the sample-id plus the upper and lower depths of the sample in parenthesis. So in the example I gave above, HB13-02 is the sample-id and (6-32) is the upper and lower depths of that sample. The fields for upper and lower depths in the EDD table were left blank by the lab (don't ask me why) so I need to extract the values in parenthesis.

Does that help?

[zmbd]

@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).

[zmbd]

I also suspect that your database isn't properly normalized:
> Database Normalization and Table Structures.

[kbarrett]

I appreciate the help especially considering you did this through your lunch hour. And no offense taken. I've done some programming in the past but am very rusty.

You correctly described what I need to do.

I'll need to digest this for a bit, see if I can get it to work, and if successful, will post my code.

Thank you!