DAO.Recordset Offset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marcelo - 01 Nov 2006 15:01 GMT

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!

Reply to this Message

Stefan Hoffmann - 01 Nov 2006 15:16 GMT

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?

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

Please tell us about your actual problem, so when can give you some clues.

mfG
--> stefan <--

Reply to this Message

Marcelo - 01 Nov 2006 16:23 GMT

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
-----------
56 | h |
14 | h | NO

Reply to this Message

Stefan Hoffmann - 01 Nov 2006 17:16 GMT

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"

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 <--

Reply to this Message