Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Newbie need help - SELECT by date in Access

Reply
Thread Tools

Newbie need help - SELECT by date in Access

 
 
mongkb
Guest
Posts: n/a
 
      08-15-2006
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.

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-15-2006
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/...e36562fee7804e

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

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl

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"


 
Reply With Quote
 
 
 
 
mongkb
Guest
Posts: n/a
 
      08-15-2006
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"


 
Reply With Quote
 
mongkb
Guest
Posts: n/a
 
      08-15-2006
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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date, date date date.... Peter Grison Java 10 05-30-2004 01:20 PM
select of select box will select multiple in another box palmiere Javascript 1 02-09-2004 01:11 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP .Net 1 11-08-2003 09:14 PM
Given a date, how to find the beginning date and ending date of that week Matt C++ 2 11-08-2003 08:30 PM
Date & Time chooser for java 1.1 - using only the mouse to select time & date Chris Berg Java 0 10-27-2003 10:59 PM



Advertisments