Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    New Coder
    Join Date
    Oct 2007
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Select data between two dates

    I am trying to select records from an Access database that has a date field formatted dd/mm/yyyy format and are between two dates defined in Dim parameters.
    The code I have tried is this:
    Code:
    <%
    Dim Today, Future
    Today = (Date())
    Future = (DateAdd("d",60,Today))
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\access_db\concerts.mdb;"
    objConn.Open
    Set objRs4=Server.CreateObject("ADODB.RecordSet")
     sql4="SELECT * FROM concert_data WHERE Cncrt_dateasp BETWEEN '" & Today & "' AND '" & Future & "' ORDER BY Cncrt_dateseq DESC"
     objRs4.Open sql4, Conn
    %>
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index
    
    Repeat1__numRows = -1
    Repeat1__index = 0
    objRs4_numRows = objRs4_numRows + Repeat1__numRows
    %>
    When I execute this script I get the following error:
    "ADODB.Recordset error '800a0bb9'

    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

    /performdates.asp, line 12"
    Line 12 = objRs4.Open sql4, Conn

    Your help is really appreciated.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,032
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    First of all, the error is dead obvious:
    Code:
    objConn.Open
    objRs4.Open sql4, Conn
    No place prior to that objRs4.Open line have you ever defined the variable Conn.

    ***********

    But now we come to your next problem: Your query won't work.

    You say your date is in the format dd/mm/yyyy.

    Okay, consider these two dates, 60 days apart:
    Code:
    20/09/2012
    19/11/2012
    And now let's assume that the value in your database is
    Code:
    03/10/2012
    There is NO POSSIBLE WAY that '03' can be between '20' and '19'.

    When you store dates AS STRINGS, the ONLY way you can then compare them is if you store them as yyyy/mm/dd.

    Because STRINGS are compared ONE CHARACTER AT A TIME. And the first mismatch trigger the < or > result. In this case, the '0' of '03' is less than both the '2' or '20' and the '1' of '19' and so is NOT between them.

    There MAY be hope for you:

    What is the DATA TYPE of the field Cncrt_dateasp in your table?

    Use Access (the program, that is) to look at your table and examine that table deifinition and tell us the data type.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Oct 2007
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Revised code

    Hi Old Pendant,
    You helped me solve part of the problem.
    The date format was/is the issue as you correctly observed.

    Revised code:
    Code:
    <%
    Dim Today, Future
    'Today = ("2012-09-18")
    'Future = ("2012-10-23")
    Today = (Date())
    Future = (DateAdd("d",30,Today))
    response.write (Today) 
    response.write (Future)
    
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Hosting\wayn4797\access_db\concerts.mdb;"
    objConn.Open
    Set objRs4=Server.CreateObject("ADODB.RecordSet")
     sql4="SELECT * FROM concert_data WHERE Cncrt_dateseq BETWEEN '" & Today & "' AND '" & Future & "' ORDER BY Cncrt_dateseq DESC"
     objRs4.Open sql4, objConn, 3, 3
    ' rs.Open strSQLQuery, conn, 3, 3
    %>
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index
    
    Repeat1__numRows = -1
    Repeat1__index = 0
    objRs4_numRows = objRs4_numRows + Repeat1__numRows
    %>
    If I remove the ' from in front of the Today and Future with the dates in yyyy-mm-dd format it works fine. That is the format of the Cncrt_dateseq data in the database.
    Now I need to know how to get the (Date()) and (DateAdd("d",30,Today)) to be in the correct format.

    Any ideas?

    Thank you so much for your help.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,032
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    If I remove the ' from in front of the Today and Future with the dates in yyyy-mm-dd format it works fine.
    That makes no sense.

    If you do that, they you will be doing something like
    Code:
    ... WHERE Cncrt_dateseq BETWEEN 2012-09-19 AND 2012-11-18 ...
    and the SQL engine will see that as *SUBTRACTION*!! That is, it will see it as
    2012
    minus 09 (==>>1993)
    minus 19 (==>>1974)

    At least with the '...' around those values I could see it working.

    Are you *SURE* the values in the database look like '2012-10-03' and not 20121003 (that is, numbers)??

    Take a screen shot of an Access display of the table data and show it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Oct 2007
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks again for your help Old Pedant.
    The database values for Cncrt_dateseq are as shown. They are a text field. There is also a field Cncrt_dateasp that is in a dd/mm/yyyy format. I tried using that but get extra events in the result, see it here, http://www.wayneoquin.com/performdates.asp

    Here is screen shot of database table.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,032
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    Okay... It's too bad those are text fields, but luckily Access (JET) has an answer for that.

    We can use *EITHER* the CNCRT_DATE or the CNCRT_DATESEQ field. Doesn't matter. Either will work. (But not the cncrt_dateasp field.)
    Code:
    SELECT * FROM concert_data 
    WHERE CDATE(Cncrt_dateseq) BETWEEN DATE() AND DATE()+60
    ORDER BY Cncrt_dateseq DESC
    Try that! In Access, before you try using it in your ASP code.

    Notice that you NO LONGER need to pass in Today and Future from the ASP code. Let Access do the date arithmetic for you!

    If that works (and I'm 90% sure it will), then you can just convert it to ASP thus:
    Code:
    sql4 = "SELECT * FROM concert_data " _
         & " WHERE CDATE(Cncrt_dateseq) BETWEEN DATE() AND DATE()+60 " _
         & " ORDER BY Cncrt_dateseq DESC"
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,032
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    If it's not obvious, CDATE() in Access works the same as it does in VBScript: IT takes the TEXT form of a date (or date + time) and converts it into the internal DATETIME format used by Access (and by VBScript). So in this case, it converts the text in the DB field and converts it to a DATETIME value.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New Coder
    Join Date
    Oct 2007
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi again Old Pedant,
    Your are a genius!
    It works with Cncrt_Date. It does not work with Cncrt_Dateseq.
    One last request.
    I want to "highlight" the performance on the day of the performance would the code be like this:
    Code:
    <%if len(trim((objRS4.Fields.Item("Cncrt_Dateseq").Value))) = (DATE()) then%>
    <h1><%=(objRS4.Fields.Item("Cncrt_Date").Value)%></h1>
    <%else%>
    <h3><%=(objRS4.Fields.Item("Cncrt_Date").Value)%></h3>
    <%End if%>
    I have tried both Cncrt_Date and Cncrt_Dateseq fields but neither work. I have also tried it with and without the brackets around the DATE.
    Any suggestions?

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,032
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    No. Not at all. WHY would you use LEN( )??? That just gets the length of the string. It would *NEVER* equal a DATE() value!

    Code:
    <%
    If DATEVALUE( CDATE( objRS4("Cncrt_Dateseq") ) ) = DATE() Then
        Response.Write "<h1>" & objRS4("Cncrt_Date") & "</h1>"
    Else
        Response.Write "<h3>" & objRS4("Cncrt_Date") & "</h3>"
    End If
    %>
    p.s.: Although you *can* use code such as objRS4.Fields.Item("Cncrt_Date").Value as the incredibly idiotic DreamWeaver code does, there is no reason to do so!

    Simply doing objRS4("Cncrt_Date") produces the IDENTICAL result *AND* executes roughly 3 times faster!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    New Coder
    Join Date
    Oct 2007
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Oh Dear! Asleep at the keyboard again . That's what happens when you blindly copy working code from section to another without reviewing the code. ARRGH!!!
    I tried your code above and it throws this error, Microsoft VBScript runtime error '800a000d'

    Type mismatch: 'CDATE'

    /performdates.asp, line 43
    Line 43 reads:
    Code:
    <% If DATEVALUE( CDATE( objRS4("Cncrt_Dateseq") ) ) = DATE() Then %>
    Any ideas?

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,032
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    Well, it *SHOULD* work if all your values in that column match what you showed in that screen shot.

    Example code:
    Code:
    <%
    Response.Write CDATE("2012-05-30") & "<hr/>"
    Response.Write CDATE("2011-02-10") & "<hr/>"
    Response.Write CDATE("2009-09-10") & "<hr/>"
    %>
    But if any of your values there are not legal dates in the form YYYY-MM-DD then of course you could get that error.

    So maybe do this:
    Code:
    <%
    On Error Resume Next
        cncrtDate = objRS4("Cncrt_Dateseq")
        dt = DATEVALUE( CDATE( cncrtDate ) )
        If Err.Number > 0 Then 
            Response.Write "INVALID DATE:"
            dt = DATE()
        End If
    On Error GoTo 0
    If  dt = DATE() Then
        Response.Write "<h1>" & cncrtDate & "</h1>"
    Else
        Response.Write "<h3>" & cncrtDate & "</h3>"
    End If
    %>
    Now you will get the INVALID DATE message and it will show you which of the values from your DB has got a problem.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,032
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    AHHH!!! I *MISSED* this comment in one of your prior posts:
    It works with Cncrt_Date. It does not work with Cncrt_Dateseq.
    That clearly indicates that one of the values in the CNCRT_DATESEQ indeed has a problem! If Access couldn't use that column, then neither could VBScript.

    So just change to using that column in the IF test:
    Code:
    <%
    If DATEVALUE( CDATE( objRS4("Cncrt_Date") ) ) = DATE() Then
        Response.Write "<h1>" & objRS4("Cncrt_Date") & "</h1>"
    Else
        Response.Write "<h3>" & objRS4("Cncrt_Date") & "</h3>"
    End If
    %>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    New Coder
    Join Date
    Oct 2007
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Ha! that fixed the problem. It works fine now. Thank you for all your help. Again, you bailed me out.

  • #14
    New Coder
    Join Date
    Sep 2012
    Location
    USA
    Posts
    13
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Hi! Query to get records between two particular date like 07/08/2012 and 10/08/2012 then use below query:



    SELECT * FROM #temp WHERE CONVERT(VARCHAR(10),CrDate,103) BETWEEN CONVERT(VARCHAR(10),'07/08/2012',103) AND CONVERT(VARCHAR(10),'10/08/2012',103)

    OR


    Syntax Query to get data between any two dates then you use below query :


    SELECT Column1,Column2,Column3 FROM TABLENAME WHERE Column3 BETWEEN GetDate()-1 AND GetDate()

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,032
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    WRONG ANSWER

    Those queries are for SQL SERVER ONLY.

    This thread was about JET OLEDB queries. That is, queries into an ACCESS database.

    Neither the Access nor JET drivers have CONVERT() or GETDATE() functions.

    Those queries aren't even very good if you are using SQL Server.

    JUST FOR EXAMPLE:
    Code:
        ... WHERE column3 BETWEEN GetDate()-1 AND GetDate()
    GetDate() returns the current date *AND TIME*.

    So let's say you make that query at 17:00:00 (5 PM) today.

    Essentilly, you are asking for
    Code:
     ... WHERE column3 BETWEEN (5 PM yesterday ) AND ( 5 PM today )
    So, for example, you won't find records from 11 AM yesterday.

    WORSE: If column3 is a DATE-ONLY column, then you wont' find ANY records from yesterday.

    I think you might want to go revisit your SQL Server documentation to find better ways than what you are doing there.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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