Help - about SELECT by date in Access

Help - about SELECT by date in Access

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:

23/07/2006
02/08/2006
15/08/2006

Here is the ASP Code:

<%session.lcid=2057%>
<%
selDate = FormatDateTime(selDate, 2)
strSQL = "SELECT * FROM tblList WHERE bdate = #" & selDate & "#"
Set rs = ObjConn.Execute(strSQL)
%>

Code (markup):

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, thank you very much for your time and sorry for my poor English.

 

FormatDateTime(Date(),2) returns date in mm/dd/yyyy

So you have to convert your date to dd/mm/yyyy

temp_array = split(seldate, "/")
seldateUSFormat = temp_array(1) & "/" & temp_array(0) & "/" & temp_array(2)

strSQL = "SELECT * FROM tblList WHERE bdate = #" & seldateUSFormat & "#"

 

hi,

if you use dd-mmm-yyyy (e.g. 11-mar-2006) it will work for any international setting

 

access dates always seem so backwards to me. i work in mysql and as/400 which both use the yyyy-mm-dd convention - i've kinda gotten to where i prefer it that way.

if it's a true date field - couldn't you just convert the string to date and bounce it off of the db using cdate()? correct me if i'm wrong - it's been forever since i worked with access and dates.

Fluid has a nice suggestion - and a very interesting idea about splitting the date into an array like that. more elegant than i would have come up with - kudos fluid

vectorgraphx

 

The problem has been fixed~
Thank you very much to all of you guys.