Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    6

    Unanswered: Recordcount Anomaly

    I must be doing something wrong but I don't know what.

    I am using Acc97.

    This sql statement when used to produce a recordset gives me 2 records but when used in a query design grid it produces 1

    "SELECT T.* FROM tblCMCarePlanST AS T WHERE [CareSTOutID] = 2 AND [CarePSOID]='106163BC' AND CPLTID = 10000090 AND IsNull([CareSTCompleted])"

    The expected result is 1 and when I loop through the recordset and debug print all the field values they are exactly the same in other words both records match.

    There is only 1 matching record in the table and both the recordset and query produce this record but the recordset produces it twice.

    Anyone have any ideas?

    Charles

  2. #2
    Join Date
    Aug 2002
    Posts
    45

    Re: Recordcount Anomaly

    hello
    you could cheat and add DISTINCT


    "SELECT DISTINCT T.* FROM tblCMCarePlanST AS T WHERE [CareSTOutID] = 2 AND [CarePSOID]='106163BC' AND CPLTID = 10000090 AND IsNull([CareSTCompleted])"

    this will only return one of the duplicate records.
    Sent By Royal Mail

  3. #3
    Join Date
    Nov 2002
    Posts
    6

    Re: Recordcount Anomaly

    Originally posted by colonelquinn
    hello
    you could cheat and add DISTINCT


    "SELECT DISTINCT T.* FROM tblCMCarePlanST AS T WHERE [CareSTOutID] = 2 AND [CarePSOID]='106163BC' AND CPLTID = 10000090 AND IsNull([CareSTCompleted])"

    this will only return one of the duplicate records.
    Thanks, but I really don't want to work around it. I know I am doing something wrong, or at least I think I am, but I just can't put my finger on it.

    Charles

  4. #4
    Join Date
    Jan 2003
    Posts
    15
    Long shot.. ignore if it sounds silly:

    "SELECT DISTINCT T.* FROM tblCMCarePlanST AS T WHERE ([CareSTOutID] = 2) AND ([CarePSOID]='106163BC') AND (CPLTID = 10000090) AND (IsNull([CareSTCompleted]))"

Posting Permissions

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