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

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:10cbq334bi0ja5@corp.supernews.com...[color=blue]
> 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[/color]
date/time[color=blue]
>
> any ideas?...the sun is obviously making my head hurt as I should really
> know all about dates by now.
>
> many thanks
>
>
>
>
>[/color]

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:10cbq334bi0ja5@corp.supernews.com...[color=blue]
> 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[/color]
date/time[color=blue]
>
> any ideas?...the sun is obviously making my head hurt as I should really
> know all about dates by now.
>
> many thanks
>
>
>
>
>[/color]

"Mark Schupp" <mschupp@ielearning.com> wrote in message
news:ekGf6dXTEHA.2944@tk2msftngp13.phx.gbl...[color=blue]
> 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#
>
> --[/color]

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!!!

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:[color=blue]
> "Mark Schupp" <mschupp@ielearning.com> wrote in message
> news:ekGf6dXTEHA.2944@tk2msftngp13.phx.gbl...[color=green]
>> 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[color=darkred]
>> >=#2004-07-06 00:00:00# and saz_date <#2004-07-07 00:00:00#[/color]
>>
>> --[/color]
>
> 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!!![/color]

--
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.

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OkqbJ7XTEHA.2408@tk2msftngp13.phx.gbl...[color=blue]
> 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)) & "#
>[/color]

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?

sorry found it...

missing rs.movenext

thanks all

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:10cc07frffm721c@corp.supernews.com...[color=blue]
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:OkqbJ7XTEHA.2408@tk2msftngp13.phx.gbl...[color=green]
> > The dates are being displayed according to the Regional Settings for the
> > IUSR account. I strongly suspect the time component of your dates is[/color][/color]
being[color=blue][color=green]
> > dropped. Try Mark's suggestion:
> >
> > where saz_date >=#" & diary_date & "# AND saz_date <#" & _
> > dateadd("d",1,CDate(diary_date)) & "#
> >[/color]
>
> 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[/color]
a[color=blue]
> 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?
>
>[/color]