How can i go to the end of a record?

[ruthcali]

I am using Access97.

How can i go to the end of a record? what i really need is to go to the end of a record and then go backwards 3 columns and capture that value into another field. That other field is called Loc2 and i programmically added it to the table.
Set fld = tdf.CreateField("Loc2", dbText, 10)
tdf.Fields.Append fld

for example:
F1 F2 F3 F4 ... F43 F44 F45 F46
abc xd dd 1.22 d4r 4r ty 1.88
i want to capture the value in F44 (which is 3rd from the last).

but another record might only have 8 records:
F1 F2 F3 F4 F5 F6 F7 F8
abc xd dd 1.22 d4r 4r ty 1.88
and i want to capture the value in F6 (which is 3rd from the last).

But, this table is being updated weekly with data from Excel so the total number of columns can change weekly. The table currently has a total of 43 columns (F1...F43). Next week, it might have 30 columns.

Thanks!

 

[JamesMeadlock]

If you use ado, add a reference to adox, and read up on the catalog object. You could use the catalog to navigate the table definitions, get the name of the column you need, then build a sql statement to pick the data from that column for you.

hth
James

 

[MichaelRed]

If this is in persuit of the 'other' thread item, please just do the other thinggy first. Follow what I have already provided / outlined. It would then be easy / obvious as it is just the UBound of the array - 2.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ruthcali]

James,
i don't know about ADO. Maybe that will be the next thing to learn when i get a breather (in other words, never!)

Michael,
Your response to my previous post is awesome. It's a bit overwhelming and i am still reading thru it trying to understand it! Compared to you, i feel like a beginner in Access! i'm always impressed with the advanced code you come up with.

But, for my post above about going to the end of a recordset and then going backwards 3 columns, i came up with the following code that seems to work:

Dim db As Database
Dim rec As Recordset
Dim x As Integer

Set db = CurrentDb
Set rec = db.OpenRecordset("select * from tblCLR&quot

Do Until rec.EOF
For x = 0 To rec.Fields.Count
If Not IsNull(rec.Fields("F" & Format(rec.Fields.Count - x))) Then
rec.Edit
rec!Loc2 = Nz(rec.Fields("F" & Format((rec.Fields.Count) - 3 - x)))
rec.Update
GoTo EndIfStatement
End If
Next x
EndIfStatement:
rec.MoveNext
Loop

 

[MichaelRed]

hmmmmmmmmm,

but the third field from the end of a record(set) is just fields.count - 1
???????????

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ruthcali]

Michael:
Were you asking me a question or making a statement above?

First i use rec.fields.count to find the total number of fields. In my table, i currently have 43 columns so rec.fields.count = 43.

That means rec.Fields("F" & Format(rec.Fields.Count equals rec!F43.

Then, starting from rec!F43, i look for the first field that has a value.

I use: For x = 0 To rec.Fields.Count
to look at each field in the row.

Ex: first look if rec!F43 has a value, if not, look if rec!F42 has a value, etc.

When a value is found:
If Not IsNull(rec.Fields("F" & Format(rec.Fields.Count - x))) Then

put that value minus 3 into the field called Loc2

rec.Edit
rec!Loc2 = Nz(rec.Fields("F" & Format((rec.Fields.Count) - 3 - x)))
rec.Update

then exit the If statement and move to the next record.

Do you find a problem with the logic?
thanks

 

[mulligh]

OK, there's a newbie clunky way to do this too What I did is to use a macro (GoToControl) to go to a specific field and then the SendKeys command to highlight and copy information and then use the GoToControl again to paste the information somewhere. If you want me to give more details on the clunky version, let me know.

 

[MichaelRed]

It was intended to be a 'rhetorical' question. You originally asked for " ... end of a recordset and then going backwards 3 columns ... ".

BUT, you start at the beginning of the recordset " ... For x = 0 To rec.Fields.Count ... ". If you want the third from the "end", it is simply the last (Fields.count - 1) - 2 (more). So, starting from the 'Beginning' and searching for some non-null doesn't get the thire from the 'end' (assuming -of course- that 'Beginning' nad 'End' are not the same).

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ruthcali]

Mulligh,
thanks for writing. that is a clever way, but i don't believe it will meet my situation because i need code to test the fields in order to find the specific field that i need.

For some records, the 3rd field from the end might be in field F44 and for another shorter record, the 3rd from the end might be in field F6. So i need code to test for null values and use a For Next loop and keep cycling until it finds it.


Hi Michael,
Thanks again for writing.
But I do start from the end of each record:
rec.Fields("F" & Format(rec.Fields.Count - x).

x is initialized as zero so, for example, that value above starts at F43 since i have 43 fields in the table at the moment.

Then as x increments, that value decreases: F42, F41, etc.

So i start from the end and go backwards checking for not null values.

Did i miss something?
thanks,
ruth

 

[MichaelRed]

No, but I did. I still think this is the hard way, but it is your app.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ruthcali]

Hi Michael,

What would you do? How would you go to the last value in a recordset and then go back 3 and put that value in another field?

I'm always ready to learn (especially from you)

Thanks,
Ruth

 

[MichaelRed]

Ruth,

Let me take a moment to summarize what I think the overall process is:

You receieve delimited text files which you need to 'parse' and append to an existing (Ms. A '97) db table.

The text files have two types of records. The first simply represents a new record to add to the table, while the second is simply additional fields which should be added to the preceeding record. These "tag" records exist due to a limitation of the originating system (the text file 'builder').

The tag records are recognized by the first dield being empty. When this occurs, the first (empty) field should be ignored, but the remainder of the fields should be added to the previous record. Further, the Third from the last field in the TAG record caries information which you use in some different process (not addressed / explined).

This thread is specifically concerned with this "third from last" field, while the other recent thread discusses the (possible) parseing mechanisim to 'groom' the delimited text file for import to your table.

'_______________________________________________________
'_______________________________________________________
'_______________________________________________________

Please re-state the situation as noted above, correcting the errors / mis-understandings.

'_______________________________________________________
'_______________________________________________________
'_______________________________________________________


Several steps in the text-to excel-to access appear to be un-necessary, and to interfeer with your goal in THIS thread. My thought is that recordsets are generally intended to be more-or-less regular. Attempting to process 'records' with different numbers of ("VALID"?) fields in a recordset is -at best- cumbersome. Since you need to groom (e.g. parse) the records during the import process, you may as well extract the weird field (" ... third from the last ... " during this process.

To start w/, the easiest way to get the records and fields from the delimited text file has been presented. You appear to reject this approach because you don't understand the process. If you're stuck in that position, I can not really help enough to continue this. On the other hand, the process in quite simplistic -and VERY efficient.

The get the file process is just creating a string variable which is the size of the delimited file, and then Get"ting" the contents of the file in the string variable. This is not conceptually different than the process of reading any test file on a line by line basis (e.g. Line Input ... ) - it just does all of hte lines at once.

Seperating the individual lines is nothing more than searching the BIG string for the 'new line' indicator, and placing each bunch of 'stuff' (characters) in a different array element when the delimiter is found.

Seperating the individual fields of each line is just the same process as seperating the lines - using a different criteria for the seperation.

Following this approach, you get each field from the original delimited text file in three steps. Of course, the fields are in array(s) - but that just simplifies the search for the third from the last, which is -at this point- just ubound or the array - 2. Since in the use of the split procedure, no additional "fields " or aray elements are created. So, at this point of the process, you would extract the "field" and save it seperatly.

Somewhere in the process, you need to deal with the 'tag' records mentioned above.

If I have read the threads correctly, these are simply the field arrays where the first field is "empty". Since, in my conceptual approach, the whole delimited text file is just one big array of arrays, searching for the 'tag' records is just a loop through the secondary array, finding those where the first array element ie empty.

Also, there is a mention of variable number of fields even between the records. This is one of the elements of the larger issue, where you need to know how many (if any) fields to add to the table. Since you are looping through the fields array, just accumulate the largest value of UBound in the secondary array - which will be the mininum field count you will require.

But, back at 'tag' line. Once you find a tag line, you need to append all of the tag line array elements (except the 0th) to the preceeding array - and then 'delete' the tag line one. Since deleting an array element dynamically goes further than I think you are able & willing, I will suggest that the 0th element be 'populated' with a flag symbol which will be checked when actually appending records.

If you are still reading this, please provide some feedback.

Assuming we are still 'together', the only issues left are the pading the taable with additional fields as required, and actually appending the records. You appear to know how to append the fields, and the above discusses obtaining the necessary field count, so I'm done with that.

To actually append the records, you either need to use ADO (not a normal part of ver '97) or add the records through code. Assuming the later.

For each 'record'
[tab]with .ret
[tab][tab].addnew
[tab][tab][tab]For each field
[tab][tab][tab][tab]Assign array element to the field
[tab][tab][tab]Next Field
[tab][tab].Update
[tab]End With
Next Record

or something akin to it.

P.S. I wont be suprised if this goee into the balck hole of un-answered.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over