Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I am getting a csv file to import into Access and then do some calculations and export data to Excel.

    My challenge is the incoming data has 72 columns, 68 of which contain numbers or are empty. After importing the records are appended to a final table where the column types are Number. However, when appended I get null values, which are expected (I guess). I need the data in the columns to be summed. So in order to put zeros in the records with null in the column, I am trying the following:


    [codebox]
    Set rst = db.OpenRecordset("tmpHoursOut", dbOpenDynaset)

    With rst
    Do While .EOF = False
    For i = 1 To .Fields.Count
    If .Fields(DataType) = "Number" Then
    If IsNull(.Fields(i).Value) Then
    .Fields(i).Value = 0
    End If
    End If
    Next i
    .MoveNext
    Loop
    .Close
    End With
    Set rst = Nothing
    [/codebox]



    I am sure I am not accessing the properties correctly, but am at wits end. Any help is greatly appreciated.

    Ken

  2. #2
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I think I found the problem. Should have used:

    If .Fields(i).Type = 6 Then

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I believe the Fields array is zero-based, so your For clause should be:

    For i = 0 To .Fields.Count - 1
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks. I didn't realize that.

    Also, since the first few columns don't need to be tested (because I know them to be text and not null), I could use

    For i = 4 To .Fields.Count - 1

    Thanks for taking time out to share.

    Ken

Posting Permissions

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