SQL and date range


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: SQL and date range

  1. #1
    cal Guest

    SQL and date range


    Hi---Thanks for all of the help---I am moving forward. If I could post one
    more.

    SQL = "SELECT * FROM Contact WHERE [Next Appointment Date] > #" & txtStartDate.Text
    & "# <= _
    #" & txtEndDate.Text & "#;"


    I am trying to select a date that is greater than the start date and less
    than the end date entered by
    the user in text boxes. But however I am not successful in getting the correct
    answer.
    What am I doing wrong


    thanks
    cal

  2. #2
    Arthur Wood Guest

    Re: SQL and date range


    Cal,
    What do you mean by "not successful"?.

    1) Do you get an error message?
    2) DO you get records that fall outside the range. (if so, on which side?
    Dates before Start, or dates after End, or both)

    You might also try this construct:

    WHERE [Next Appointment Date] BETWEEN #" & txtStartDate.Text & "# AND #"
    & DateAdd("d",-1,txtEndDate.Text) & "#"


    Arthur Wood

    "cal" <sticks2@earthlink.net> wrote:
    >
    >Hi---Thanks for all of the help---I am moving forward. If I could post

    one
    >more.
    >
    >SQL = "SELECT * FROM Contact WHERE [Next Appointment Date] > #" & txtStartDate.Text
    >& "# <= _
    > #" & txtEndDate.Text & "#;"
    >
    >
    >I am trying to select a date that is greater than the start date and less
    >than the end date entered by
    >the user in text boxes. But however I am not successful in getting the correct
    >answer.
    >What am I doing wrong
    >
    >
    >thanks
    >cal



  3. #3
    cal Guest

    Re: SQL and date range


    Arthur,
    What I am getting is not the first record (date) that matches the
    criteria. For example, if the search
    dates are 11/15 to 11/18. I get a return date of the 11/18, but in the data
    base I have a date of 11/16
    that should of been returned.
    I tried your suggestion and I still get the next date not the first one that
    matches the selection. I dont get
    any error messages. The date returned is always inside the range but just
    not the next one.
    thanks for your help

    cal



    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >
    >Cal,
    > What do you mean by "not successful"?.
    >
    >1) Do you get an error message?
    >2) DO you get records that fall outside the range. (if so, on which side?
    >Dates before Start, or dates after End, or both)
    >
    >You might also try this construct:
    >
    >WHERE [Next Appointment Date] BETWEEN #" & txtStartDate.Text & "# AND #"
    >& DateAdd("d",-1,txtEndDate.Text) & "#"
    >
    >
    >Arthur Wood
    >
    >"cal" <sticks2@earthlink.net> wrote:
    >>
    >>Hi---Thanks for all of the help---I am moving forward. If I could post

    >one
    >>more.
    >>
    >>SQL = "SELECT * FROM Contact WHERE [Next Appointment Date] > #" & txtStartDate.Text
    >>& "# <= _
    >> #" & txtEndDate.Text & "#;"
    >>
    >>
    >>I am trying to select a date that is greater than the start date and less
    >>than the end date entered by
    >>the user in text boxes. But however I am not successful in getting the

    correct
    >>answer.
    >>What am I doing wrong
    >>
    >>
    >>thanks
    >>cal

    >



  4. #4
    Arthur Wood Guest

    Re: SQL and date range


    Cal,
    Sorry but I did not look at your query closely. You have to change the
    Where clause to:

    WHERE [Next Appointment Date] > #" & txtStartDate.Text & "# AND [Next Appointment
    Date] <= #" & txtEndDate.Text & "#;"

    see if that gets what you need (need the AND Clause, here)

    you should also be able to use

    WHERE [Next Appointment Date] BETWEEN #" & txtStartDate.Text & "# AND #"
    & txtEndDate.Text & "#;"


    Arthur Wood

    "cal" <sticks2@earthlink.net> wrote:
    >
    >Hi---Thanks for all of the help---I am moving forward. If I could post

    one
    >more.
    >
    >SQL = "SELECT * FROM Contact WHERE [Next Appointment Date] > #" & txtStartDate.Text
    >& "# <= _
    > #" & txtEndDate.Text & "#;"
    >
    >
    >I am trying to select a date that is greater than the start date and less
    >than the end date entered by
    >the user in text boxes. But however I am not successful in getting the correct
    >answer.
    >What am I doing wrong
    >
    >
    >thanks
    >cal



  5. #5
    cal Guest

    Re: SQL and date range


    Arthur---I get the date that is inbetween the two dates entered. For instance
    if there are 5 days in
    inbetween the two the returned response is the date that is 2
    days from the start date.
    It is weird.....

    cal


    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >
    >Cal,
    > Sorry but I did not look at your query closely. You have to change the
    >Where clause to:
    >
    >WHERE [Next Appointment Date] > #" & txtStartDate.Text & "# AND [Next Appointment
    >Date] <= #" & txtEndDate.Text & "#;"
    >
    >see if that gets what you need (need the AND Clause, here)
    >
    >you should also be able to use
    >
    >WHERE [Next Appointment Date] BETWEEN #" & txtStartDate.Text & "# AND #"
    >& txtEndDate.Text & "#;"
    >
    >
    >Arthur Wood
    >
    >"cal" <sticks2@earthlink.net> wrote:
    >>
    >>Hi---Thanks for all of the help---I am moving forward. If I could post

    >one
    >>more.
    >>
    >>SQL = "SELECT * FROM Contact WHERE [Next Appointment Date] > #" & txtStartDate.Text
    >>& "# <= _
    >> #" & txtEndDate.Text & "#;"
    >>
    >>
    >>I am trying to select a date that is greater than the start date and less
    >>than the end date entered by
    >>the user in text boxes. But however I am not successful in getting the

    correct
    >>answer.
    >>What am I doing wrong
    >>
    >>
    >>thanks
    >>cal

    >



  6. #6
    Arthur Wood Guest

    Re: SQL and date range


    Cal,
    I can't see why you should be getting only those dates which are 2 days
    from the start date. Can you e-mail me a copy of your databse. Perhaps
    I can see something else that you are missing (this is Access right?)

    Arthur Wood

    "cal" <sticks2@earthlink.net> wrote:
    >
    >Arthur---I get the date that is inbetween the two dates entered. For instance
    >if there are 5 days in
    > inbetween the two the returned response is the date that is 2
    >days from the start date.
    > It is weird.....
    >
    >cal
    >
    >
    >"Arthur Wood" <wooda@saic-trsc.com> wrote:
    >>
    >>Cal,
    >> Sorry but I did not look at your query closely. You have to change

    the
    >>Where clause to:
    >>
    >>WHERE [Next Appointment Date] > #" & txtStartDate.Text & "# AND [Next Appointment
    >>Date] <= #" & txtEndDate.Text & "#;"
    >>
    >>see if that gets what you need (need the AND Clause, here)
    >>
    >>you should also be able to use
    >>
    >>WHERE [Next Appointment Date] BETWEEN #" & txtStartDate.Text & "# AND #"
    >>& txtEndDate.Text & "#;"
    >>
    >>
    >>Arthur Wood
    >>
    >>"cal" <sticks2@earthlink.net> wrote:
    >>>
    >>>Hi---Thanks for all of the help---I am moving forward. If I could post

    >>one
    >>>more.
    >>>
    >>>SQL = "SELECT * FROM Contact WHERE [Next Appointment Date] > #" & txtStartDate.Text
    >>>& "# <= _
    >>> #" & txtEndDate.Text & "#;"
    >>>
    >>>
    >>>I am trying to select a date that is greater than the start date and less
    >>>than the end date entered by
    >>>the user in text boxes. But however I am not successful in getting the

    >correct
    >>>answer.
    >>>What am I doing wrong
    >>>
    >>>
    >>>thanks
    >>>cal

    >>

    >



  7. #7
    Paul Clement Guest

    Re: SQL and date range

    On 17 Nov 2000 07:20:05 -0800, "cal" <sticks2@earthlink.net> wrote:


    Arthur,
    What I am getting is not the first record (date) that matches the
    criteria. For example, if the search
    dates are 11/15 to 11/18. I get a return date of the 11/18, but in the data
    base I have a date of 11/16
    that should of been returned.
    I tried your suggestion and I still get the next date not the first one that
    matches the selection. I dont get
    any error messages. The date returned is always inside the range but just
    not the next one.
    thanks for your help

    Try adding "ORDER BY [Next Appointment Date] ASC" to the end of your SQL statement.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center