lost - linking date field based on a record with smaller Unique ID

[stan.chernov@gmail.com]

I am totaly lost, here. I have a report with very difficult formatting. Main problem is that it has data for a week at a time, but it doesn't include the date of a record inside the actual record. I can import it to access, but once there, i can't sweem to match up records to dates. Data goes somewhat like this:

Table Headers
Record ID *** Date *** Field_1 *** Field_2 *** etc
Actual Import Data:
00000001 *** 01/01/2010 *** NULL *** NULL *** NULL
00000002 *** NULL *** Name1 *** Plan1 *** Notes1
00000003 *** NULL *** Name 2 *** Plan2 *** Notes2
00000004 *** 01/02/2010 *** NULL *** NULL *** NULL
00000005 *** NULL *** Name3 *** Plan3 *** Notes3
00000006 *** NULL *** Name 4 *** Plan4 *** Notes4


What i am trying to make my query do is to check if the date field is null, and if it is, to get a date from priv record with data in date field. So in this example, records 02 and 03 would have date from 01 (01/01/2010) and records 05 and 06 would get date from 04 (01/02/2010).

I am guessing i need to make a subq to pull records with date fields (sort of like small headers through the report) and flag them as date fields. Then use some sort of logic to bounce rest of the records and finaly link them to the first records in a list (moving down... so if record id is 006, it would check 05 (null), then 04 (01/02/2010) and would put that date in a field.



Any help? A link to a tutorial would be great... i really don't mind to do the leg work, just need to know what i am looking for.

[pbaldy]

Are you looking to update the data in the table, or just display it correctly in a query?

[stan.chernov@gmail.com]

i'd like to change the data in the actual table... if needed though i am fine with just displaying it in a query and then making a "make table" query if needed.

at this poing i'll take whatever i can ;0)

[pbaldy]

Either is probably doable, I just wanted to head down the correct path. I'd open a recordset on the table, ordered by the ID field. As you loop through the recordset, put the date value into a variable. For each record, if the date value is null update it with the variable value, else change the variable to the current value.

[stan.chernov@gmail.com]

i am fairly suck in VB... can you give me some pointers... do i need to create a new module? a new procedure? i assume i'll set a variable.

but thanks for the help... i'll go ahead and start googling looping in vba.

[pbaldy]

The main tools you want are a loop and the Edit method of a recordset (of the two available, I'd use DAO). I use templates a lot, and this is my DAO loop template:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM TableName ORDER BY FieldName"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    'code here
    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing

[stan.chernov@gmail.com]

thanks man, this should be enough for me to get started...

[pbaldy]

No problemo; post back if you get stuck.