Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO query record count (2000)

    The following msgbox is returning -1 for the recordset record count,
    yet the records returned by the next code line to excel starting in Range("AdoField1") is 140
    Why is the rs1.RecordCount not able to get the correct record count?

    ''' OPEN CONNECTION
    cn.Open strCon

    '''OPEN RECORDSET
    rs1.Open "Select " & sFields & _
    " From " & sTable & _
    " WHERE (((DOB) Between #" & StartDate & "# And #" & EndDate & "#))", cn
    <font color=red>MsgBox rs1.RecordCount</font color=red>
    Range("AdoField1").Offset(1, 0).CopyFromRecordset rs1

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ADO query record count (2000)

    Does the MS Article: PRB: ADO: Recordcount May Return -1 help?

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO query record count (2000)

    Yes, Steve.
    Thanks for the link.

    That solved the recordCount.
    Now, can you tell me how to get a Date field as in (DOB) to format as a Date: mm/dd/yy

    I thought the #....# would accomplish this:

    '''OPEN RECORDSET
    rs1.CursorLocation = adUseClient 'this allows the RecordCount property to work
    rs1.Open "Select " & sFields & _
    " From " & sTable & _
    " WHERE (((DOB) Between #" & StartDate & "# And #" & EndDate & "#))", cn

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ADO query record count (2000)

    If DOB is a datefield in excel then something like this perhaps (I don't use ADO at all)

    " WHERE (((" & Format(DOB, "mm/dd/yy") & ") Between #" & StartDate & "# And #" & EndDate & "#))", cn

    Steve

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

    Re: ADO query record count (2000)

    You can format the column that receives the DOB field with mm/dd/yy format.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO query record count (2000)

    Steve, I'm not able to get your method to work.
    Hans, I need to do this via vba since users may choose to select DOB or not as a field to extract (the db has 40 fields). DOB has Date/Time data type in the mdb. Also, the order of fields extracted will not always be the same.

    The following code works to some extent, but not tested in all areas and it seems clumbsy. I think there may be a better way but just haven't found it.


    '''FORMAT THE COLUMNS
    On Error Resume Next
    Set rDOB = ExtractFields.Find(What:=UCase("*DOB*"))
    rDOB.Select
    If Err <> 0 Then
    Err = 0
    Set rDOB = ExtractFields.Find(What:=UCase("*Birth*"))
    rDOB.Select
    If Err <> 0 Then Exit Sub
    End If
    Selection.EntireColumn.NumberFormat = "mm/dd/yy"

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

    Re: ADO query record count (2000)

    Another way would be to loop through the fields of the recordset, and if the Type of the field is adDate, and if so, format the corresponding column in the worksheet.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO query record count (2000)

    Thanks Hans,

    Just what I was looking for. It's generic and will pick up any Date field and format it correctly.

Posting Permissions

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