473,322 Members | 1,538 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Query to extract part of one field to populate another

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.
Mar 18 '14 #1
6 1499
zmbd
5,501 Expert Mod 4TB
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.
Mar 18 '14 #2
Seth Schrock
2,965 Expert 2GB
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.
Mar 18 '14 #3
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?
Mar 18 '14 #4
zmbd
5,501 Expert Mod 4TB
@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).
Mar 18 '14 #5
zmbd
5,501 Expert Mod 4TB
I also suspect that your database isn't properly normalized:
> Database Normalization and Table Structures.
Mar 18 '14 #6
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!
Mar 18 '14 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: rdraider | last post by:
Is there a function that will extract part of a string when the data you want does not occur in a specific position? Field "REF" is varchar(80) and contains an email subject line and the email...
1
by: Tony Johnson | last post by:
I want to update a particular field in a form for only a certain set of records. The certain set of records is named Financed. I need to update those Financed records with a financed number that...
1
by: John Phelan-Cummings | last post by:
I'm not certain if this made the post. Sorry if it's a repeat: Using a Button to take an autonumber from one form to populate another autonumber field on another form. I have a Mainform "A"...
3
by: Chris Kettenbach | last post by:
Hello all, Quick question. I have a listbox that is populated by items from a database. When the user select something from the list, I want to populate another listbox based on the selection in...
1
by: WebArtist8248 | last post by:
Hi all, Trying to figure out how to use a checkbox in a form and have JavaScript populate another field. For instance, I have 2 fields: DaytimePhone and EveningPhone and I have a checkbox that...
1
by: Nelly | last post by:
Please help! I am trying to sort a query by a field calculated as e.g. / I keep getting an error about subqueries not allowed for this type of expression, but only when I set to sort. I...
6
by: Spoogledrummer | last post by:
Hi, I'm a total novice to ASP (yesterday was literally the first time I looked at it) but am currently working on an application that will send an e-mail to selected users, the text that goes in the...
2
by: jmartmem | last post by:
Is there a function, or a combination of functions, in MS Access 2007 that will allow me to extract part of a string? I have a database text field "Email" that contains a person's name and email...
1
by: sourcemb | last post by:
I have an Access database that has been developed using only the native Access tools and a few macros. I have found the need to use one of the controls on a form (contains a date field from one of...
2
by: stormtrupr | last post by:
I have a Table(PA Phrases) consisting of phrases to be used in a purchase agreement, but not all phrases apply for every purchase agreement. I want to allow the sales rep to select the phrases...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.