Newbie need help - SELECT by date in Access

[mongkb]

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.

 

[Bob Barrows [MVP]]

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

Bob Barrows

 

[mongkb]

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:

 

[mongkb]

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: