Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling in blank fields (Access 2000)

    We import a 200k file with dept numbers onthe first line for its group, then blank fields until the next new dept name. How can I quickly fill in the dept numbers into the blank fields for those records without the number in them?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling in blank fields (Access 2000)

    You can use the following code. It uses DAO, so you need to set a reference to the Microsoft DAO 3.6 Object Library (in Tools | Refetences...)

    Sub CompleteValues(strTable, strField)
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varDept As Variant

    ' Open recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)

    ' Get out if no records
    If rst.EOF Then GoTo ExitHandler

    ' Loop through records
    Do While Not rst.EOF
    ' Test if field us null
    If IsNull(rst.Fields(strField)) Then
    ' Nul, so set field to variable
    rst.Edit
    rst.Fields(strField) = varDept
    rst.Update
    Else
    ' Not null, so set variable to field value
    varDept = rst.Fields(strField)
    End If
    ' Go to next record
    rst.MoveNext
    Loop

    ExitHandler:
    ' Cleaning up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    ' Report error
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Arguments:
    <UL><LI>strTable is the name of the table to be updated; it might also be the name of a query, or even an SQL statement (as long as the query is updateable)
    <LI>strField is the name of the field whose null values must be replaced.[/list]Call it like this:

    CompleteValues "tblDepartments", "DeptNo"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •