Web Wiz - Solar Powered Eco Web Hosting

  New Posts New Posts RSS Feed - Database Select by Date
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Database Select by Date

 Post Reply Post Reply Page  123>
Author
Diddl View Drop Down
Newbie
Newbie
Avatar

Joined: 11 September 2003
Location: Denmark
Status: Offline
Points: 24
Post Options Post Options   Thanks (0) Thanks(0)   Quote Diddl Quote  Post ReplyReply Direct Link To This Post Topic: Database Select by 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.
Back to Top
dj air View Drop Down
Senior Member
Senior Member
Avatar

Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
Post Options Post Options   Thanks (0) Thanks(0)   Quote dj air Quote  Post ReplyReply Direct Link To This Post 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"))
Back to Top
Diddl View Drop Down
Newbie
Newbie
Avatar

Joined: 11 September 2003
Location: Denmark
Status: Offline
Points: 24
Post Options Post Options   Thanks (0) Thanks(0)   Quote Diddl Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
dj air View Drop Down
Senior Member
Senior Member
Avatar

Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
Post Options Post Options   Thanks (0) Thanks(0)   Quote dj air Quote  Post ReplyReply Direct Link To This Post Posted: 19 February 2005 at 2:38pm
is the field in the database a date/time field?
Back to Top
Diddl View Drop Down
Newbie
Newbie
Avatar

Joined: 11 September 2003
Location: Denmark
Status: Offline
Points: 24
Post Options Post Options   Thanks (0) Thanks(0)   Quote Diddl Quote  Post ReplyReply Direct Link To This Post Posted: 19 February 2005 at 3:02pm
Yes. - it's a date time field - because i have to order the fields by date.
Back to Top
dj air View Drop Down
Senior Member
Senior Member
Avatar

Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
Post Options Post Options   Thanks (0) Thanks(0)   Quote dj air Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Diddl View Drop Down
Newbie
Newbie
Avatar

Joined: 11 September 2003
Location: Denmark
Status: Offline
Points: 24
Post Options Post Options   Thanks (0) Thanks(0)   Quote Diddl Quote  Post ReplyReply Direct Link To This Post Posted: 19 February 2005 at 3:43pm
Somehow i got the Cdate method to work - thanks for the help.
Back to Top
Diddl View Drop Down
Newbie
Newbie
Avatar

Joined: 11 September 2003
Location: Denmark
Status: Offline
Points: 24
Post Options Post Options   Thanks (0) Thanks(0)   Quote Diddl Quote  Post ReplyReply Direct Link To This Post 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?

Edited by Diddl - 19 February 2005 at 5:57pm
Back to Top
 Post Reply Post Reply Page  123>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.07
Copyright ©2001-2024 Web Wiz Ltd.


Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Policy

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2024 Web Wiz Ltd. All rights reserved.