Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136

    Red face Unanswered: Table has a start and end date - need a special query.

    I have a table called Authorizations. In that table I have two fields. On called Start Date and one called End Date.

    Example: We receive authorization for 3 home health visits with a start date of 06/10/05 and an end date of 07/15/05.

    I have a report called Authorizations that shows our nurses how many visits they have authorized and in what time frame they have to do them.

    To run the report I have a form with unbound controls for start and end date criteria.

    Problem: If I select 06/15/05 for the start date and 07/10/05 for the end date, the authorization in the example above will not show up on my report because the start date of my criteria is after the authorizations start date and the end date of my criteria is before the authorization end date.

    This would make the nurse think she can't do any visits in the time frame of 06/15/05 thru 07/10/05, when actually she can.

    I'm a beginner with SQL so this seems very complicated to me. Does anyone have any suggestions as to how I can write my SQL to retrieve all authorizations that are within my criteria selection?

    I hope I made this understandable as to what I'm wanting. Any help would be greatly appreciated.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Something like this?

    SELECT *
    FROM yourAuths
    WHERE (yourAuths.start <= startParam) AND (yourAuths.end >= endParam)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Right now I have:

    WHERE(yourAuths.start >= startParam) AND (yourAuths.end <= endParam)

    I flipped the greater than > and the less than< and it worked! You truly are my hero! THANK YOU!!

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That looks funny. It's asking for a timeframe where the start date date hasn't come yet and the end date has already passed?

    Maybe I'm just getting caught in semantics... in any event, glad it works.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    It worked wonderfully for the example I gave above. I did another test and it didn't work for a different scenario I tried.

    Example: Auth Start Date 10/01/05 Auth End Date 10/15/05.

    If I run the report 10/10/05 thru 10/16/05, the above example does not show up.

    I'm a real newbie, but it seems it would take a very complicated SQL to get what I need. I could train the user to use broad date ranges for the report but I don't know if there is an SQL that would cover all the scenarios the user may come up with.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ah... That's a little different

    Try:

    SELECT *
    FROM yourAuthTable
    WHERE
    (StartDate <= yourStartDateParameter AND yourEndDateParameter <= EndDate) OR
    (StartDate BETWEEN yourStartDateParamter AND yourEndDateparameter AND EndDate BETWEEN yourStartDateParamter AND yourEndDateParameter) OR
    (yourEndDateParameter BETWEEN StartDate <= AND endDate)

    Basically we're using three different critera that can match.
    1. The start date given is between your startdate and enddate. That automatically makes the record acceptable.
    2. Both the start and end dates fall between the provided criteria. That means the entire range is encapsulated in what the user provided. This is of course ok.
    3.The end date specificed falls between the startdate and enddate. This also automatically makes the record acceptable.

    That was deceptive.. had to think about that a couple times... There's one question to be asked, are the visits cumulative? Also, this can't really be done exactly as stated. If you have two sets of visits, one for 1/1/05 - 1/7/05 for 3 visits, the next for 1/8/05 - 1/14/05 and the nurse puts in 1/5/05 - 1/9/05, she's going to see that six visits are available. This could be confusing, does this mean six visits could happen on 1/5/05? Not really, because half of those aren't valid until 1/8.
    Last edited by Teddy; 11-09-05 at 16:41.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    (Isn't it amazing the details you need in order to help someone, I deal with that every day. People wonder why I ask so many questions when they need my help. I tell them, in order for me to be of any help to you I need to understand how it all works.)

    The visits are not cumulative. Each record stands by itself. I just need to be able to pull any records that have visits that can be done anywhere inside the criteria entered. Then when the report prints, the nurse can see her date boundaries.

    In your example:

    3 visits 1/1/05 - 1/7/05 and
    3 visits 1/8/05 - 1/14/05

    Nurse puts in 1/5/05 - 1/9/05. I would like for her to see the following on the report:

    3 visits 1/1/05 - 1/7/05
    3 visits 1/8/05 - 1/14/05

    The way I have it now 1/1/05 - 1/7/05 would not show up because the start date of 1/1/05 is before the critera of 1/5/05. If the 1/14/05 auth end date were changed to 1/10/05, neither of the above record would show up.

    I would want both records to show up because there are visits that can be done in the criteria date range of 1/5/05 - 1/9/05.

    I hope that makes sense.
    Last edited by Zenaida; 11-09-05 at 16:09.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    zenaida you need to consider how you capture your time bands.

    how do you cater for null values - ie if the user doesn't put in a start value or end value.
    OR
    how do you cater for the idiot factor, where a user puts an end date before the start date -one technique is to consider using the between clause in your sql eg "select blah from table where mydatevalue between avalue and bvalue"

    to handle the null values do some error checking and insert a known good vlaue (for example from date - start of data, end date = tomorrows date

    HTH

  9. #9
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I want the user to be forced to enter both a start and end date. I do need to take into consideration the idiot factor, but I don't understand your comment on it. I'm very new to SQL.

  10. #10
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I guess another way to put what I want is ........

    If there are any visits, even just one that can be done within the criteria date range, I want those records to show up.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Good enough. The last example I provided should do the trick. You may have to monkey with paranthesis and so forth. The general idea is you have to use three seperate criteria. One to pull the first range, one for the last, and one for anything in between.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Sounds good. I did try your last example and was having problems with the parenthases. I'm glad to know it's not something I did wrong, I'll put it back in and see if I can get the parenthases to work right.

    Thanks for all your help!!!!

  13. #13
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I've given up, I can't find the syntax error.

    ((Authorizations.[Start Date]) <= [forms]![Report Authorizations]![From]) AND [forms]![Report Authorizations]![Thru]) <= ((Authorizations.[End Date]) OR ((Authorizations.[Start Date]) BETWEEN [forms]![Report Authorizations]![From]) AND [forms]![Report Authorizations]![Thru]) AND ((Authorizations.[End Date]) BETWEEN [forms]![Report Authorizations]![From]) AND [forms]![Report Authorizations]![End] OR [forms]![Report Authorizations]![End] BETWEEN ((Authorizations.[Start Date]) <= "AND" ((Authorizations.[End Date]) AND ((Authorizations.[Auth Received])="Yes"))

    When I tried to save the query, the cursor highlights the last AND in the statement so I put quotes around it (didn't know what else to do). After I did that I tried to save it again then got a syntax error message and the curser goes down and highlights the ; at the end. There are other statements after the one above but this is what the last statement looks like:

    OR (((Authorizations.[Start Date])>=[forms]![Report Authorizations]![From]) AND ((Authorizations.[End Date])<=[forms]![Report Authorizations]![Thru]) AND ((Authorizations.[Auth Received])="Yes") AND ((([Last Name] & "," & " " & [First Name]) Like [forms]![Report Authorizations]![Patient]) Is Null) AND ((([Patients].[Case Manager]) Like [forms]![Report Authorizations]![Manager]) Is Null) AND ((([Patients].[Patient Status]) Like [forms]![Report Authorizations]![Status]) Is Null));

    I don't know the syntax very well. Any help would be greatly appreciated.

  14. #14
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Well I think I figured it out. I added another ) to the end of the last statement so it reads

    Is Null)));

    Now all I have to do is test the query to make sure it works.

  15. #15
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by Teddy
    WHERE
    (StartDate <= yourStartDateParameter AND yourEndDateParameter <= EndDate) OR
    (StartDate BETWEEN yourStartDateParamter AND yourEndDateparameter AND EndDate BETWEEN yourStartDateParamter AND yourEndDateParameter) OR
    (yourEndDateParameter BETWEEN StartDate <= AND endDate)
    Isn't that the same as
    WHERE
    StartDate <= yourEndDateParameter AND
    yourStartDateParameter <= EndDate
    ?

    Assuming that yourStartDateParameter <= yourEndDateParameter, of course.

Posting Permissions

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