Computing dates from multiple tables

[Mihail]

Hi all !

I think is a basic question but I am newbie in Access.

Situation:
- I work in an unbound form.
- In current database (say DB) I have a table (say TBL) with fields (say F1, F2 ... Fk ... Fn) and, of course, many records.
- I dimension a variable (say V).

Ask:
Please, teach me, step by step, how refer TBL and how I can to "scan" TBL with step S on the field Fk to obtain something like this:

V = 0 'Initiate V
i = 1 'First record
do until i > last record 'Escape from DO
V = V + ValueFrom(Row=i,Field=Fk)
i=i+S
loop

Actually, for this purpose, I use a not visible LISTBOX based on a query and I use the .Column(Column,Row) . Ugly.

Thank you in advance.

One more question: How to use CODE TAGS to indent lines ? Thank you.

[TheSmileyCoder]

If you have a field named Amount for instance, you can do a DOMAIN sum on that, the function is called DSUM. You can look up more details in the access help file.

DSUM("[Amount]","[TBL]",WHERE)

The WHERE is optional, it could be something like "[CustomerID]=257"


I think is what your "really" looking for, but if I am wrong, just let me known, and I can give an example of using and manipulating a recordset.

[Mihail]

Thank you for reply, TheSmileyCoder.

No. The task is not to SUM a field.

There are two tasks in my example:
- To open a table and access the records without using a control (like ListBox or ComboBox). I do not know how to do that.
- To access a certain value into a certain record into a certain field. Something like in Excel: MyVar = cells(R,C). For Access tables, C is a field with a certain name.

YES. In the last row of your post you understand what I wish. Thank you !

[TheSmileyCoder]

To open a table (Or a query) you can use:

Dim rsDao as dao.Recordset
Set rsDAO=CurrentDb.OpenRecordset("SELECT * from [tbl] ",dbOpenDynaset)
'data manipulation goes here
 
'Cleanup
  Set rsDao=nothing
 

An example of such Data manipulation could be:

Dim V as Variant
Do While Not rsDao.Eof
  V=V+rsDao.Field(0)
  'OR
  V=V+rsDao.Field(5)
  V=V+rsDao!MyFieldName
  rsDao.MoveNext
loop

[Mihail]

Thank you again TheSmileyCoder.

It is exactly what I am looking for.

One more question:
Is it possible to start at row... r > 1 ?
Or is necessary to use rsDao.MoveNext in a DO-LOOP or FOR-NEXT cycle until the cursor is on the desired record ?

[TheSmileyCoder]

You can use:

rsDao.AbsolutePosition=10

for example.

or if you have the primary key field named KEY_Customer:

RsDao.FindFirst "KEY_Customer=10"

I sometimes open a full recordset, then use this method to cycle through some of the records:

Dim strCrit as string
strCrit="KEY_Customer=10"
rsDao.FindFirst strCrit
Do while not RsDao.NoMatch
 'My manipulation here
 
  RsDao.FindNext strCrit
loop

[Mihail]

Thank you, TheSmileyCoder.
That complete solve my question.