Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2008
    Location
    Brisbane, Australia
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL text with different results (2002 SP3)

    When I design a query in the Access window, I can copy the SQL and paste it into VB6 code. One query recently, quite a simple one, revealed 36 records, which made good sense, considering the environment.. The SQL text, used to open a recordset, produced a RecordCount of 543. Interested to see what the extra records were about, I intoduced a "INTO' phrase into the text and the result was a new table with 36 records. Is my Access broken?
    Since all I really want is the count (in this case 36) I used the Count() aggregate function in the text. In the Access Query designer this produced a successful 36. How do I get this into a variable to use it?
    Attached Files Attached Files

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

    Re: SQL text with different results (2002 SP3)

    Welcome to Woody's Lounge!

    You don't need to open a recordset to do this, you can use DCount to get the record count:

    Dim intCount As Integer
    intCount = DCount("*", "OvernightShifts", "PersonnelID=" & PersID)

    If you click in the word DCount (in the Visual Basic Editor) and press F1, you'll get info about the DCount function.

  3. #3
    New Lounger
    Join Date
    Sep 2008
    Location
    Brisbane, Australia
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL text with different results (2002 SP3)

    Thank you HansV. I had never heard of DCount()
    It still leaves as a mystery why the different results in the first part of my question.
    Now I need not know, Therefore I will possibly never know.
    Correction: I now realize that I have always known of the Domain Aggregate functions. I have even used them. Thanks for reminding me.

Posting Permissions

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