Pulling data from a "nested" field

[JBruyet]

Hey all,

I have a table that I'm trying to pull some info out of (Tech support for the program that created the data hasn't been helpful at all) and I'm stuck as to how to do this.

I'm using Acess 2003 and I can do a search in the table and find the data, but the data is in what I'm calling a nested field. If I navigate to that field I can up-arrow and down-arrow within the field and see different data lines. If I press the Home or End key I move to the beginning or the end of the data line.

I'm trying to pull up one particular line in a query but the query only grabs the first line of data. Does anyone know how I can choose the line number of the data (it's the sixth line in the field) in a table from a query?

Thanks,

Joe Brouillette

 

[JBruyet]

Ok, I can pull a query now that does ALL info. BUT, for 63 devices I'm getting 1083 records. I only want 63 records with the data I'm looking for.

Anybody?

Joe Brouillette

 

[JBruyet]

How about any off-the-wall suggestions?

Joe

 

[AppStaff]

your post is a bit confusing. I'm not sure what you mean by nested field... i suspect you mean nested form (most call sub form).

From what I can make of your situation it seems like you just need to modify the query criteria to return only the record you want. We don't really have enough info to tell you specifically what that would be however.

Just ask yourself what rules determine the record I want and then design your query appropriately.

 

[TonyJollans]

Hi Joe,

I'm with AppStaff here. There really isn't enough information to go on. Tell us more!

I think you are saying that you have a field (a column in a table) that contains several lines of data and you want your query to return only the sixth line. I guess that can be done but it will not reduce the number of records that your query returns so there must be something else you're not telling us.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[JBruyet]

Ok, I'll try to clarify. Within my table there are some records composed of fields (please forgive me if I sound condescending. i'm just trying to explain more clearly what i'm trying to do). Within some of those fields there are several lines of data. If I click in the field and press the down-arrow, I can scroll through the data within that one field one line at a time. If I continue to press the down-arrow I will eventually get to the last line of data, then pressing the down-arrow will move me down each field within that column. I've tried to pull up just one line of data within that one field, but I can't seem to figure it out. I also thought that perhaps the data was just a very long text string, but making the field longer doesn't bring up any addtional information. I still need to click in the field and arrow -up or -down to see all of the data. My goal at this time is to be able to run queries that will pick out one single line of data within that field.

Is this any clearer? And thanks for trying to help!

Joe Brouillette

 

[fredericofonseca]

How are you looking at the fields?

Using plain SQL, or by opening the table on datasheet view?
If the latest then you have a lookup field.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[JBruyet]

I'm opening the table in Datasheet View. If I go to Design View, the data type for that field is "Memo", and there's no Display Control under the Lookup tab.

Joe Brouillette

 

[fredericofonseca]

Ok.

Another option is that people entered the data like abc+ctrl-enter+dfg+ctrl-enter, and this will look like you have several rows.

What happen on datasheetview if you increase the size of each row? (you can do that with the mouse)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[JBruyet]

Fredericofonseca, I am able to increase the row height, and I am able to see the entire contents of the field by doing that, but I have to increase the row height so much that I can only see two rows at a time. Is there a way to have a query pull data from just one line in a field composed of several lines????

Thanks,

Joe Brouillette

 

[fredericofonseca]

You will need to speak with whoever did the db layout and the input screens for that field and ask them why they have it like that.

If you do a "select my_long_filed from mytbl" you will retrieve the FULL field, displaying it is another thing.

Probably the best to display is a multiline edit field.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[TonyJollans]

Hi Joe,

I'm quite sure your field is one long text string with carriage returns. When you view the query there is only (by default) enough space in the grid to see one line at a time. Try resizing the rows so they are deeper - then you should see more than one line at a time.

Sample SQL to extract each line separately looks like this:

[blue]
SELECT InStr(            [[i]FullFieldName[/i]], Chr(13) ) AS L1End,
       InStr( [L1End]+2, [[i]FullFieldName[/i]], Chr(13) ) AS L2End,
       InStr( [L2End]+2, [[i]FullFieldName[/i]], Chr(13) ) AS L3End,
       Mid( [[i]FullFieldName[/i]], 1,         [L1End]-1 )       AS Line1,
       Mid( [[i]FullFieldName[/i]], [L1End]+2, [L2End]-[L1End] ) AS Line2,
       Mid( [[i]FullFieldName[/i]], [L2End]+2, [L3End]-[L2End] ) AS Line3
FROM   [i]TableName[/i];[/blue]

I hope you can see how to extend it for more than three lines. Note that it does give you some fields you don't really want to see but you can hide them in the query view if you wish.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[JBruyet]

Frederico & Tony,

Thanks for the help. I'll give it a shot and see what happens.

Joe Brouillette