Newbie need help - SELECT by date in Access

Discussion in 'ASP General' started by mongkb, Aug 15, 2006.

  1. mongkb

    mongkb Guest

    Hi all, I've created a table in Access and I assigned the date format
    to a field , named 'bdate', to "dd/mm/yyyy", the 'bdate' records are:

    1. 23/07/2006
    2. 02/08/2006
    3. 15/08/2006

    SQL:
    strSQL = "SELECT * FROM tblList WHERE bdate = #" & selDate & "#"
    Set rs = ObjConn.Execute(strSQL)

    Problem:
    This SQL works fine with record no.1 and 3, but select no.2 will result
    EOF, but if I change the selDate to 08/02/2006, the record no.2 will be
    found.

    I've made a debug page here, it clearly shows the detail of the issue:
    http://www.mong.hk/test/c.asp

    Can someone help me to solve this problem and thank you very much for
    your time.
    mongkb, Aug 15, 2006
    #1
    1. Advertising

  2. mongkb wrote:
    > Hi all, I've created a table in Access and I assigned the date format



    Assuming this is a Date/Time field and not a Text field (you should always
    supply this information so we don't have to guess), this property only
    controls how Access deals with the date values stored in the Jet database:
    it has no effect on how the values are actually stored.

    > to a field , named 'bdate', to "dd/mm/yyyy", the 'bdate' records are:
    >
    > 1. 23/07/2006
    > 2. 02/08/2006
    > 3. 15/08/2006


    No, they aren't. This is what you see when Access displays the date values.
    This is not what's being stored in that field. Jet stores Date/Time values
    as Double numeric values, with the whole number portion representing the
    number of days since the seed date, and the decimal portion representing the
    time of day - .0 = midnight, .5 = noon). So your dates are actually stored
    as:
    1. 38921.0
    2. 38931.0
    3. 38944.0

    So, Jet does not stored any format. Access adds special Access-specific
    properties (such as the Format property) which it uses when storing and
    retrieving values from the table. External applications, such as ADO,
    because they are dealing directly with the Jet database engine, cannot
    utilize these Access-specific poperties. If you look up what the Jet SQL
    section of the Access online help has to say about date values, you will see
    that when date literals are involved, Jet will correctly handle only two
    formats: the US format (mm/dd/yyyy) and the ISO format (yyyy-mm-dd). The
    latter format is preferred when supplying date literals to ADO, because it
    is less ambiguous.

    Here is some reading material to help with this:
    http://www.aspfaq.com/show.asp?id=2313 vbscript
    http://www.aspfaq.com/show.asp?id=2040 help with dates
    http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion

    Further points to consider:
    Your use of dynamic sql is leaving you vulnerable to hackers using sql
    injection:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

    See here for a better, more secure way to execute your queries by using
    parameter markers:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Personally, I prefer using stored procedures, or saved parameter queries
    as
    they are known in Access:

    Access:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=

    Bob Barrows





    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Aug 15, 2006
    #2
    1. Advertising

  3. mongkb

    mongkb Guest

    Hi Bob Barrows,

    Thank you very much for you kind response and clear answer,
    it helps and I've leanred so much from your post.


    Bob Barrows [MVP] wrote:> Access:
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    mongkb, Aug 15, 2006
    #3
  4. mongkb

    mongkb Guest

    Hi Bob Barrows,

    Thank you very much for your kind response and clear answer,
    it helps and I've leanred so much from your post.


    Bob Barrows [MVP] wrote:> Access:
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    mongkb, Aug 15, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    570
    Matthew Speed
    Nov 8, 2003
  2. Chris Berg
    Replies:
    0
    Views:
    772
    Chris Berg
    Oct 27, 2003
  3. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,193
    Michael Borgwardt
    May 30, 2004
  4. Matt
    Replies:
    2
    Views:
    493
    Pete Becker
    Nov 8, 2003
  5. palmiere
    Replies:
    1
    Views:
    377
    Erwin Moller
    Feb 9, 2004
Loading...

Share This Page