Database Discussion - Database Select by Date

Print Page | Close Window

Database Select by Date

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=13897
Printed Date: 28 March 2026 at 2:21pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Database Select by Date

Posted By: Diddl
Subject: Database Select by Date
Date Posted: 19 February 2005 at 6:53am

Hi

I'm having a problem with the following lines:

strSQL = "Select TOP 1 strips.* from strips where dato <= DateSerial(year(now),month(now),day(now)) ORDER BY DATO DESC"
Set RsStrips = objconn.execute(strSQL)

strSQL = "Select TOP 1 strips.* from strips where dato < "&RsStrips("dato")&" Order By Dato DESC"

Set RsPrevStrips = objconn.execute(strSQL)

It won't accept the RsStrips("dato") as a valid date, so it won't return any values.

I'm using an Access Database with DD-MM-YYYY Output - i have tried converting it to YYYY-MM-DD but nothing helps.. it works fine if i just use either date() or now(), but i want to use a dynamic date.

Replies:

Posted By: dj air
Date Posted: 19 February 2005 at 7:01am

try
DateValue(RsStrips("dato"))

use that in the second query if that desn't also try CDate(RsStrips("dato"))

Posted By: Diddl
Date Posted: 19 February 2005 at 7:14am

I've tried both methods -

The DateValue method - returns an Type Mismatch error, while the CDATE apparently writes an date to the statement, but it still doesn't return any date - i tried CDate(date()) and that worked, but i won't recognise any other kind of date.

Posted By: dj air
Date Posted: 19 February 2005 at 2:38pm

is the field in the database a date/time field?

Posted By: Diddl
Date Posted: 19 February 2005 at 3:02pm

Yes. - it's a date time field - because i have to order the fields by date.

Posted By: dj air
Date Posted: 19 February 2005 at 3:26pm

can ypou post the results of a made query.. ie can you do a response.write of the SQL statement. so we cn see what it looks like.

Posted By: Diddl
Date Posted: 19 February 2005 at 3:43pm

Somehow i got the Cdate method to work - thanks for the help.

Posted By: Diddl
Date Posted: 19 February 2005 at 5:55pm

I just found out - it didn't work the way i wanted it to.

The SQL string just takes the lowest row in the table.

fx.

RsStrips("dato") Returns the value = 05-12-2004 (from an Date field in access) - the sql string should return the top 1, date that's higher than 05-12-2004. But instead it just returns either the highest date (at the moment) 14-02-2005 or the lowest date - 01-12-2004

I won't filter so it only selects row with a date after 05-12-2004

the sql string is currently:

strSQL = "Select TOP 1 Strips.* from strips where dato > "&CDate(RsStrips("dato"))&" Order By Dato ASC"
Set RsNext = objconn.execute(strSQL)

The only difference in selecting ASC or DESC - is that it's either chooses the highest row or the lowest row in the table.. what's wrong, i just can't figure it out?

Posted By: dj air
Date Posted: 19 February 2005 at 6:10pm

put this

"&CDate(RsStrips("dato"))&"

in #

so #"&CDate(RsStrips("dato"))&"#

try that. it should select records greater than the set date. it shouldn't return any record before the set date.

Posted By: Diddl
Date Posted: 19 February 2005 at 6:23pm

Sorry to say, it still returns the wrong value.

I have a response.write here:

Select Date = 02-12-2004
Output Date = 01-12-2004

I'm just lost - because if i write a normal "IF "OUTPUT date" > "SelectDate" it will tell me that the Select Date is higher than the Output date... using the same strings etc... as i do in the sql string.

Posted By: dj air
Date Posted: 19 February 2005 at 6:26pm

try changing the > to < .. within the SQL.

you may need to change the ASC, but see what that does.. i think i had a simular problem with my tv guide application once...

Posted By: Diddl
Date Posted: 19 February 2005 at 6:29pm

Just tried it - it just resulted in the following:

ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Posted By: dj air
Date Posted: 19 February 2005 at 6:33pm

right so its not finding records befind it.

i think i had to add 00:00:00 to the end of the values because they where date values and didn't use the time.. and then it may work .. add that to the CDated version (before CDate())

so CDate(rs.fieldname & " 00:00:00")

put that in the query

Posted By: Diddl
Date Posted: 19 February 2005 at 6:43pm

Tried it again - had no effect, it still makes the same result.

Here is my to query's

strSQL = "Select * from strips where ID = "&Request.querystring("id")&""
Set RsStrips = objconn.execute(strSQL)

strSQL = "Select Top 1 strips.* from strips where dato > #"&CDate(RsStrips("dato")&" 00:00:00")&"# Order By Dato ASC"
Set RsNext = objconn.execute(strSQL)

I still think it's funny -because i can make date(), now(), that filters, but not on a specific date.

Posted By: dj air
Date Posted: 19 February 2005 at 6:52pm

have you tried writing a date value within the query not using a command like Date() or NOW()

and see what that does.. im not sure why its not it should be.. have you tried >= that date? or does it have to be greater than the date stated?

Posted By: Diddl
Date Posted: 19 February 2005 at 6:56pm

I tried writing a date - and it just returns the same result, no matter which date i choose.

And i tried once with >= but no effect there either, and it has to be a greater date.

Posted By: dj air
Date Posted: 19 February 2005 at 6:59pm

the other option is to use

where Day(deto) > Day(veriable) AND Month(deto) > Month(veriable) AND Year(deto) > Year(Veriable) ORDER BY deto ASC;

Posted By: Diddl
Date Posted: 20 February 2005 at 4:50am

I tried that - but it just behaves very weird - example..

i split the RsStrips("dato") into Strday, Strmonth, Stryear

And when entered the values into the above string - it gives me some random result

I just tried to write: Response.write day(strDay) - and it gave me a weird day and Response.write(strYear) gave me the result 1905???

The StrYear is 2004

Posted By: dj air
Date Posted: 20 February 2005 at 10:48am

you need to put the veriable within the Day() or Month() or year() so

Day(RsStrips("dato"))

and the same for other mnth and year.