dates again :o(

[Alistair]

diary_date = request.form("diary_date") - (from a populated drop down
list)

strSQL = "SELECT saz_title, saz_text from saz_details where saz_date =#" &
diary_date & "#"

a response.write gives

SELECT saz_title, saz_text from saz_details where saz_date =#07/06/2004#

yet no records are found??

I have 4 records with dates 7/06/2004, stored in access 2000 DB as date/time

any ideas?...the sun is obviously making my head hurt as I should really
know all about dates by now.

many thanks

[Patrice]

Are you wroking using the MM/DD/YYYY format ?

Also make sure the date in the DB doesn't have hours minutes seconds...

Patrice

"Alistair" <forget_it> a écrit dans le message de
news:10************@corp.supernews.com...

diary_date = request.form("diary_date") - (from a populated drop down
list)

strSQL = "SELECT saz_title, saz_text from saz_details where saz_date =#" &
diary_date & "#"

a response.write gives

SELECT saz_title, saz_text from saz_details where saz_date =#07/06/2004#

yet no records are found??

I have 4 records with dates 7/06/2004, stored in access 2000 DB as date/time
any ideas?...the sun is obviously making my head hurt as I should really
know all about dates by now.

many thanks

[Mark Schupp]

reformat diary_date as yyyy-mm-dd

Also, date/time columns include time so it can be difficult to get a match
without using a range of dates. For example:

SELECT saz_title, saz_text from saz_details where saz_date >=#2004-07-06
00:00:00# and saz_date <#2004-07-07 00:00:00#

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com
"Alistair" <forget_it> wrote in message
news:10************@corp.supernews.com...

diary_date = request.form("diary_date") - (from a populated drop down
list)

strSQL = "SELECT saz_title, saz_text from saz_details where saz_date =#" &
diary_date & "#"

a response.write gives

SELECT saz_title, saz_text from saz_details where saz_date =#07/06/2004#

yet no records are found??

I have 4 records with dates 7/06/2004, stored in access 2000 DB as date/time
any ideas?...the sun is obviously making my head hurt as I should really
know all about dates by now.

many thanks

[Alistair]

"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:ek**************@tk2msftngp13.phx.gbl...

reformat diary_date as yyyy-mm-dd

Also, date/time columns include time so it can be difficult to get a match
without using a range of dates. For example:

SELECT saz_title, saz_text from saz_details where saz_date >=#2004-07-06
00:00:00# and saz_date <#2004-07-07 00:00:00#

--

The drop down list is actually populated from the dates in the database..

so, if there are 4 records say 1/1/2004, 5/9/2004, 12/12/2004,3/9/2005 then
these are the options.

I have a script which pulls in those dates as they stand so why can't I then
select one of those dates??

<select name="diary_date">
<%
set rs = server.CreateObject ("ADODB.Recordset")
strSQL = "SELECT saz_date FROM saz_details"
rs.Open strSQL, conn, 1
do while not rs.EOF
record = rs("saz_date")
response.write "<option>" & record & "</option>"
rs.movenext
loop
%>

^^^ populated list.

the results of which give me 4 dates in the format 07/06/2004

if I then take this and query the database

strSQL = "SELECT saz_title, saz_text from saz_details where saz_date =#" &
diary_date & "#"

....they dont exist!!!

[Bob Barrows [MVP]]

The dates are being displayed according to the Regional Settings for the
IUSR account. I strongly suspect the time component of your dates is being
dropped. Try Mark's suggestion:

where saz_date >=#" & diary_date & "# AND saz_date <#" & _
dateadd("d",1,CDate(diary_date)) & "#

Bob Barrows

Alistair wrote:

"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:ek**************@tk2msftngp13.phx.gbl...

reformat diary_date as yyyy-mm-dd

Also, date/time columns include time so it can be difficult to get a
match without using a range of dates. For example:

SELECT saz_title, saz_text from saz_details where saz_date

>=#2004-07-06 00:00:00# and saz_date <#2004-07-07 00:00:00#

--

The drop down list is actually populated from the dates in the
database..

so, if there are 4 records say 1/1/2004, 5/9/2004,
12/12/2004,3/9/2005 then these are the options.

I have a script which pulls in those dates as they stand so why can't
I then select one of those dates??

<select name="diary_date">
<%
set rs = server.CreateObject ("ADODB.Recordset")
strSQL = "SELECT saz_date FROM saz_details"
rs.Open strSQL, conn, 1
do while not rs.EOF
record = rs("saz_date")
response.write "<option>" & record & "</option>"
rs.movenext
loop
%>

^^^ populated list.

the results of which give me 4 dates in the format 07/06/2004

if I then take this and query the database

strSQL = "SELECT saz_title, saz_text from saz_details where saz_date
=#" & diary_date & "#"

...they dont exist!!!

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Alistair]

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:Ok**************@tk2msftngp13.phx.gbl...

The dates are being displayed according to the Regional Settings for the
IUSR account. I strongly suspect the time component of your dates is being
dropped. Try Mark's suggestion:

where saz_date >=#" & diary_date & "# AND saz_date <#" & _
dateadd("d",1,CDate(diary_date)) & "#

er..thanks...tried that...the response.write seemed correct, but a removal
of response.write, response.end....

and my whole PC ground to a halt..the three fingered salute revealed
dllhost.exe had jumped to 95% of resources and it took over 2 minutes for a
dos widow to open so I sould restart IIS!!,and the page in question never
finished loading....

something is not right methinks

any ideas what causes things like that?

[Alistair]

sorry found it...

missing rs.movenext

thanks all

[Aaron [SQL Server MVP]]

Yeah, did you leave out a movenext perhaps? Reproduce the problem, and show
us the code that causes it.

--
http://www.aspfaq.com/
(Reverse address to reply.)


"Alistair" <forget_it> wrote in message
news:10*************@corp.supernews.com...

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:Ok**************@tk2msftngp13.phx.gbl...

The dates are being displayed according to the Regional Settings for the
IUSR account. I strongly suspect the time component of your dates is being dropped. Try Mark's suggestion:

where saz_date >=#" & diary_date & "# AND saz_date <#" & _
dateadd("d",1,CDate(diary_date)) & "#

er..thanks...tried that...the response.write seemed correct, but a removal
of response.write, response.end....

and my whole PC ground to a halt..the three fingered salute revealed
dllhost.exe had jumped to 95% of resources and it took over 2 minutes for

a dos widow to open so I sould restart IIS!!,and the page in question never
finished loading....

something is not right methinks

any ideas what causes things like that?