Filling blank fields with data

[john.constantine]

Say we want to automatically fill fields with ever value appears in
the previous field so -

First field has value ABC , next is blank, next is blank, next has
value DEF etc

ABC
blank
blank
DEF
blank
GHI
blank
blank
blank

We want the blanks to be filled so it looks like this

ABC
ABC
ABC
DEF
DEF
GHI
GHI
GHI
GHI

 

[John W. Vinson]

Say we want to automatically fill fields with ever value appears in
the previous field so -

First field has value ABC , next is blank, next is blank, next has
value DEF etc

ABC
blank
blank
DEF
blank
GHI
blank
blank
blank

We want the blanks to be filled so it looks like this

ABC
ABC
ABC
DEF
DEF
GHI
GHI
GHI
GHI

Relational tables have no defined order. There is no such thing as "the record
before" or "the record after". You can only do this if there is some other
field, or combination of fields, which define the desired order of records.
What is the actual structure and contents of your table? Is it imported from
other source (such as an Excel spreadsheet or text file) which *does* have a
defined order?

 

[john.constantine]

Say we want to automatically fill  fields  with ever value appears in
the previous field   so -

First field has value ABC , next is blank, next is blank, next has
value DEF etc

ABC
blank
blank
DEF
blank
GHI
blank
blank
blank

We want the blanks to be filled so it looks like this

ABC
ABC
ABC
DEF
DEF
GHI
GHI
GHI
GHI

Yes

 

[john.constantine]

Relational tables have no defined order. There is no such thing as "the record
before" or "the record after". You can only do this if there is some other
field, or combination of fields, which define the desired order of records.
What is the actual structure and contents of your table? Is it imported from
other source (such as an Excel spreadsheet or text file) which *does* have a
defined order?
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Yes imported from excel ,yes it has a number of other fields ,yes it
has another field with sequential numbers, the sequential numbers are
indexed so it always appears in the order above ,

 

[John W. Vinson]

Yes imported from excel ,yes it has a number of other fields ,yes it
has another field with sequential numbers, the sequential numbers are
indexed so it always appears in the order above ,

You should then be able to run an Update query like:

UPDATE yourtable AS X
SET X.field1 =
(SELECT Field1 FROM yourtable AS Y
WHERE Y.sortfield =
(SELECT Max(Z.Sortfield) FROM yourtable AS Z
WHERE Z.sortfield < X.sortfield
AND Z.field1 IS NOT NULL))

Untested air code, you will of course need to supply your own table and
fieldnames.

The logic is to use an inner subquery to find the largest value of the
sortfield less than the record being updated, and an outer subquery to find
the value of the ABC field.

Since you have the data in Excel now - which DOES have a defined order - you
might want to consider using fill-down or a calculated field in Excel prior to
importing, though the update query should be workable.

 

[Gina Whipp]

John,

Here's some code I've never used but sounds like it will fit your
requirements...

Function CopyFieldRecords(pstrRST As String, pstrField As String, pstrID As
String) As Boolean

Dim db As Database
Dim rec As Recordset
Dim vCopyDown As Variant

CopyFieldRecords = True

On Error GoTo err_copyrecords

vCopyDown = Null
Set db = CurrentDb()
Set rec = db.OpenRecordset("Select * FROM [" & YourTable & "]")


While Not rec.EOF
'If the field isn't blank then use this to copy down
If Nz(rec(pstrField), "") <> "" Then
vCopyDown = rec(pstrField)
Else
'Only if we have something to copy down
If Nz(vCopyDown, "") <> "" Then
rec.Edit
rec(pstrField) = vCopyDown
rec.Update
End If
End If
rec.MoveNext
Wend

exit_copyrecords:
Exit Function

err_copyrecords:
MsgBox Error, vbCritical, "Copy Fields Down Records"
CopyFieldRecords = False
GoTo exit_copyrecords

End Function

***END OF CODE


'Call the function like this
pstrRST = "YourTableName"
pstrFieldToCopy = "YourFieldName" 'name of the field that you want to
copy down
pstrID = "IDField"
Call CopyFieldRecords(pstrRST, pstrFieldToCopy, pstrID)


Originally posted by David back in 1999
CopyFieldRecords(pstrrec As String, pstrfieldkey As String, pstrfieldid As
String)

pstrrec is the table/query name
pstrfieldkey the field that needs copying down the table
pstrfieldid the order the fields should be in - create an autonumber if they
don't have a specific order key


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm