DAO.Recordset Offset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I'm used to program in VBA Excel where I can move through spreadsheet with
OFFSET.
Is there any way to use Offset in Access Table?

I already know to move with:
Do Until rstlinks.EOF
var_aux = rstlinks.Fields("FieldName").Value
rstlinks.MoveNext
Loop

But I would like to use something like:
Do Until rstlinks.EOF
rstlinks.Fields("FieldName").OFFSET(x,y).Value =
rstlinks.Fields("FieldName").OFFSET(x-1,y).Value
rstlinks.MoveNext
Loop

Thanks!
 
hi Marcelo,
I'm used to program in VBA Excel where I can move through spreadsheet with
OFFSET.
Is there any way to use Offset in Access Table?
Click to expand...
No, cause tables in Access are not based on the same concept as Excel
spreadsheets.

Example:

Table with columns A and B:

A | B
-----
1 | 2
3 | 4

As you can retrive data with SQL, you can create the following query:

SELECT B, A FROM Table ORDER BY B DESC

which gives us the following result

B | B
-----
4 | 3
2 | 1

As you may see, an OFFSET relation without knowing about the structure
of the underlying data is senseless. In Excel this structure is fixed in
the spreadsheet layout.
Access works on data while Excel works on structure.
But I would like to use something like:
Do Until rstlinks.EOF
rstlinks.Fields("FieldName").OFFSET(x,y).Value =
rstlinks.Fields("FieldName").OFFSET(x-1,y).Value
rstlinks.MoveNext
Loop
Click to expand...
Please tell us about your actual problem, so when can give you some clues.


mfG
--> stefan <--
 
Thanks Stefan, you're right about the Access data x Excel structure.
To solve this question, I would try:
1) order the table to fit the structure it would be in excel
2) then move through this fixed ordered data


My problem is the followig:
I have a TABLE and I want to compare a field_A in a row_2 with the field_A
in previous row_1, and according to the comparison, a result will be
registered in field_C.
To do it, the table is already ordered, and I will use a procedure (module)
with the code.
Example:

TABLE
A | B | C
-----------
56 | h |
14 | h |

MODULE (pseudocode)
If row(2).column(A).value = row(1).column(A).value then
row(2).column(C).update = "YES" else "NO"

To the above example, the result would be:
A | B | C
 
hi Marcelo,
My problem is the followig:
I have a TABLE and I want to compare a field_A in a row_2 with the field_A
in previous row_1, and according to the comparison, a result will be
registered in field_C. Ok.

MODULE (pseudocode)
If row(2).column(A).value = row(1).column(A).value then
row(2).column(C).update = "YES" else "NO"
Click to expand...
The following code needs some enhancements, but it should show you the
right way:

Public Sub UpdateTable()

Dim rs As DAO.Database

Dim OldValue As Variant ' use the correct data type here

Set rs = CurrentDb.OpenRecordset("yourTable")

rs.MoveFirst
OldValue = rs![A]
rs.MoveNext
Do While Not rs.Eof
rs.Edit
rs![C] = (rs![A] = OldValue)
rs.Update
OldValue = rs![A]
rs.MoveNext
Loop
rs.Close

Set rs = Nothing

End Sub

mfG
--> stefan <--
 
Back
Top