none
Dates with 2 digit months not displaying in VBscript-SQL from MS Access database

    Question


  • I am searching an MS Access database via an asp written in VBscript.  I am looking for entries that are less than 45 days old.  The query worked great until October rolled around.  Then none of the new entries were being displayed.  I discovered that any month that has 2 digits, such as October with 10 or even Jan-Sept if I enter a "0" in the month (ie. 09 vs just 9), will not display.

    The SQL statement is below (created in Dream Weaver):
    <%
    Dim rs_blog__MMColParam
    rs_blog__MMColParam = "entrydate"
    %>
    <%
    Dim MinDate
    MinDate = DateAdd("d",-45,Date())
    %>
    <%
    Dim rs_blog
    Dim rs_blog_numRows

    Set rs_blog = Server.CreateObject("ADODB.Recordset")
    rs_blog.ActiveConnection = MM_intranet_STRING
    rs_blog.Source = "SELECT id, employee, subject, blog, entrydate  FROM blog  WHERE entrydate >= '" + Replace(MinDate, "'", "''") + "'  ORDER BY id DESC"

    How do I get the data that has 2 digits in the month to display, and only show the entries that are no more than 45 days old?
    Thanks!
    Sherry

    Wednesday, October 14, 2009 1:57 PM

Answers

  • in Access Sql, the date values must be formatted withint "#" in the US format (mm/dd/yyyy),
    so you can change the rs_blog.Source :
    "SELECT id, employee, subject, blog, entrydate  FROM blog  WHERE entrydate >= #" + Format(MinDate, "mm/dd/yyyy") + "#  ORDER BY id DESC"

    but you choose better a parametric command instead of a recordset

    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    • Marked as answer by slammott Friday, October 16, 2009 5:57 PM
    Wednesday, October 14, 2009 2:46 PM

All replies

  • in Access Sql, the date values must be formatted withint "#" in the US format (mm/dd/yyyy),
    so you can change the rs_blog.Source :
    "SELECT id, employee, subject, blog, entrydate  FROM blog  WHERE entrydate >= #" + Format(MinDate, "mm/dd/yyyy") + "#  ORDER BY id DESC"

    but you choose better a parametric command instead of a recordset

    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    • Marked as answer by slammott Friday, October 16, 2009 5:57 PM
    Wednesday, October 14, 2009 2:46 PM
  • This is the VB.NET (Windows Forms) forum.  To get accurate answers for your VBScript questions try the VBScript forum that can be found in this link:

    For VBA, Office (VSTO), Macros and VBScript Questions


    :)


    Doug

    SEARCH ... then ask
    Wednesday, October 14, 2009 4:03 PM
  • Diego! 
    This worked after I ensured the MS Access database field "entrydate" was formatted to Date/Time.  When I tried formatting this in the past, I got a "type mismatch' error.  Your suggestion resolved that!
    Thanks!
    Sherry
    Friday, October 16, 2009 5:58 PM