Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    I'm trying to create a query to allow me to create a report of patients who have not had a certain drug prescription refilled with a certain number of days. I've created an entry in the table for a 'stop date' for patients who have had the medication stopped for whatever reason. I tried to set the query to only look for that drug, with the number of days, and with the 'stop date' = Null, since no date will be entered. However, when I do such, it shows all the entries, including the one with an entry in the 'stop date' section.

    Can null be used in this way? Is there a better way to do this?

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The correct criteria would be Is Null, not "=Null"

    If that does not fix it, please post a stripped down version of database. Only include the necessary your table(s) and the query you are using.

  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
    [quote name='kyhawkeye' post='791862' date='02-Sep-2009 15:10']I'm trying to create a query to allow me to create a report of patients who have not had a certain drug prescription refilled with a certain number of days. I've created an entry in the table for a 'stop date' for patients who have had the medication stopped for whatever reason. I tried to set the query to only look for that drug, with the number of days, and with the 'stop date' = Null, since no date will be entered. However, when I do such, it shows all the entries, including the one with an entry in the 'stop date' section.

    Can null be used in this way? Is there a better way to do this?[/quote]
    As mbarron pointed out, the correct syntax is to use Is Null. But it is important you understand why. ANY comparison with Null yields a Null. Even Null=Null does not produce True, it returns Null.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    [quote name='MarkLiquorman' post='791868' date='02-Sep-2009 16:02']As mbarron pointed out, the correct syntax is to use Is Null. But it is important you understand why. ANY comparison with Null yields a Null. Even Null=Null does not produce True, it returns Null.[/quote]


    Yes, I used 'Is Null' not = Null. I also tried to add a Y/N field, hoping that anything not equal to Yes would give me the desired result, with no luck. The 2 Sutent patients that were stopped still appear on the query's dynaset. What I am doing wrong? Is it something in the table set up? I have replaced the names of the patients and docs with numbers for privacy reasons
    Attached Files Attached Files

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    [quote name='kyhawkeye' post='791911' date='02-Sep-2009 22:28']Yes, I used 'Is Null' not = Null. I also tried to add a Y/N field, hoping that anything not equal to Yes would give me the desired result, with no luck. The 2 Sutent patients that were stopped still appear on the query's dynaset. What I am doing wrong? Is it something in the table set up? I have replaced the names of the patients and docs with numbers for privacy reasons[/quote]
    I downloaded the database, but what am I looking for? You need to point me to where you are having the problem.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Is the problem with the query Sutent alert.
    With this query you have 3 criteria for the drug, which are all on different lines (that is OK) but the other two criteria are only on the top line.
    Each Criteria line needs to be read in its entirety. So I think you need to repeat the Yes and <Date()-14 on the other two lines.
    Regards
    John



  7. #7
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    [quote name='MarkLiquorman' post='791912' date='02-Sep-2009 22:32']I downloaded the database, but what am I looking for? You need to point me to where you are having the problem.[/quote]

    The "Sutent alert" query. Patients 6 and 39 should not be appearing in the query because the treatment was stopped. I have a stop date entered and the box clicked for the Y/N field. Yet they still keep appearing, no matter how I structure the query.

  8. #8
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='791914' date='02-Sep-2009 22:37']Is the problem with the query Sutent alert.
    With this query you have 3 criteria for the drug, which are all on different lines (that is OK) but the other two criteria are only on the top line.
    Each Criteria line needs to be read in its entirety. So I think you need to repeat the Yes and <Date()-14 on the other two lines.[/quote]


    That did it! I thought that would carry down with the other two dose sizes. D'oh!

    I just realized something that could become a problem. As I enter new prescription fills for the active patients, when I run this query in the future, will it look at ALL old entries (as in this, 42 day or more) or just the most recent entry? If the former, what would be the best way to write the query so it only looks at the latest drug entry in the table for that patient (since I have some patients taking more than one strength of their drug)?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'd have to create a series of queries:

    1. A Totals query that returns the max date for each patient.
    2. A query based on the totals query and the original table, joined on patient id and date, that returns the data for the most recent date for each patient.
    3. A query based on the second one that adds the criteria you want.

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I think this question moved over to the thread beginning with
    Post 792179
    Regards
    John



Posting Permissions

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