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 & "#"
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