fulfilling empty rows based on condition

[vojinb]

Hi,
I have column in table (integer type) where I have values like

1001
-blank-
-blank-
1002
-blank-
-blank-
1003

What I want to do is to fulfill blank rows in next manner:
between 1001 and 1002 all blank values to be 1001
between 1002 and 1003 all blank values to be 1002 and so on,
but query
"update table set field =1001 where field between 1001 and 1002" not working,it is same when I try with > and <

 

[stopher]

Tables do not have any sense of order. So Access has no way of knowing that the second and third record are related to the first.

You will need a key field. e.g. suppose you have a date field that identifies that the second and third fields follow the first. Then that forms the basis for being able to create some logic to add the blanks. Maybe you could provide your whole table structure.

Chris

 

[vojinb]

Hi, i do have id field (autonumber) but the thing is that table is imported from text file and there is no other way to fulfill this blank fields. Can it be something where when it gets to value 1001 check the id and when it gets to 1002 again check id an between update the blanks?

 

[stopher]

Here's some code:

Dim rs As DAO.Recordset
Dim currentValue As Long

Set rs = CurrentDb.OpenRecordset("select * from [COLOR="red"]tblData[/COLOR] order by [COLOR="Red"]ID[/COLOR]")

With rs

    Do Until .EOF
        If IsNull(![COLOR="red"]myInteger[/COLOR]) Then
            .Edit
            ![COLOR="red"]myInteger [/COLOR]= currentValue
            .Update
        Else
            currentValue = ![COLOR="red"]myInteger[/COLOR]
        End If
        .MoveNext
    
    Loop
    
End With

Set rs = Nothing

Pop it into a module as a sub. You'll need to edit it to your field/table names (which I've highlighted). Note that ID that I've used here is the field that you have your records ordered by (your autonumber if you like)

hth
Chris

 

[vojinb]

Thanks,
I've tried with code but nothing happened,
and I'm not sure that I have explained well
I have table like:

ID CLASS VALUE
1 1001 -some value-
2 -blank- -some value-
3 -blank- -some value-
4 1002 -some value-
5 -blank- -some value-
6 1003 some value

What I want is to fulfill blanks with one class value until it reach another non blank.
for example for ID's 2 and 3 class must be 1001, when it reach 1002 keeps is like that, and further on for all blanks until 1003 value to be 1002.
ID is autonumber

 

[stopher]

What happened when you ran the code? Did you change the relevant bits in the code? Where are you running the code?

Chris

 

[vojinb]

when I run code nothing happened.Yes I have changed values.
Here is code:bilansstanja is table, ID is auto-number as key in table
Dim rs As DAO.Recordset
Dim currentValue As Long

Set rs = CurrentDb.OpenRecordset("select * from bilansstanja order by ID")

With rs

Do Until .EOF
If IsNull(!ID) Then
.Edit
!ID = currentValue
.Update
Else
currentValue = !ID
End If
.MoveNext

Loop

End With

Set rs = Nothing
Me.bilansstanjasubform1.Requery

 

[stopher]

Try this:

Dim rs As DAO.Recordset
Dim currentValue As Long

Set rs = CurrentDb.OpenRecordset("select * from bilansstanja order by ID")

With rs

Do Until .EOF
If IsNull(![COLOR="red"]Class[/COLOR]) Then
.Edit
![COLOR="red"]Class [/COLOR]= currentValue
.Update
Else
currentValue = ![COLOR="Red"]Class[/COLOR]
End If
.MoveNext

Loop

End With

Set rs = Nothing
Me.bilansstanjasubform1.Requery

Chris

 

[vojinb]

Thanks!!)
this worked, excellent,
Thanks once more

 

[stopher]

Thanks!!)
this worked, excellent,
Thanks once more

That's good to hear

 

[Taruz]

Hi..

Chris solve the problem, but I'll give an alternative to the query..:

update bilansstanja set class=dlast("class","bilansstanja","[Id]<=" & [Id] & " and len([class])>0 ")