| Author |
 Topic  |
|
|
Alfred
Senior Member
USA
1527 Posts |
 Posted - 30 August 2003 : 23:26:57
|
I am trying to select data from the latest record in a table, but have trouble using the date field for it:
quote:
strSql = "SELECT MAX(end_date) FROM GAMES"
set intLASTGAME = my_Conn.Execute(strSql)
Is there a simpler code to just select the last record? |
Alfred
The Battle Group
CREDO
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 30 August 2003 : 23:50:06
|
strSql = "SELECT TOP 1 End_Date FROM Games Order by End_Date DESC"
Good for Access or SQL Server. For MySQL it should be something like:
strSql = "SELECT End_Date FROM Games Order by End_Date DESC LIMIT 1"
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 30 August 2003 23:52:21 |
 |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 31 August 2003 : 00:52:27
|
I got it now to select the latest record:quote:
strSql = "SELECT TOP 1 End_Date FROM Games Order by End_Date DESC"
set intLASTGAME = my_Conn.Execute(strSql)
strSql = "SELECT * FROM GAMES WHERE end_date= " & intLastGame("end_date") & ""
set intNEWS = my_Conn.Execute(strSql)
response.write strSql
response.end
but when I try to get the other data from it: quote:
strSql = "SELECT TOP 1 End_Date FROM Games Order by End_Date DESC"
set intLASTGAME = my_Conn.Execute(strSql)
strSql = "SELECT * FROM GAMES WHERE end_date= " & intLastGame("end_date") & ""
set myNEWS = my_Conn.Execute(strSql)
response.write myNEWS("TYPE")
'strSql
response.end
I get an error: quote:
Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/BG/forums/news.asp
|
Alfred
The Battle Group
CREDO
|
Edited by - Alfred on 31 August 2003 01:00:16 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 31 August 2003 : 03:51:01
|
Use a single statement, not two:
strSql = "SELECT TOP 1 * FROM Games Order by End_Date DESC"
When using dates in SQL expressions in Access, they need to be delimited by '#' chars. Thus your current problem. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 31 August 2003 : 12:16:22
|
Yes.
Can one also select any number of records from the top, like for instance:
quote:
strSql = "SELECT TOP 5 * FROM Games Order by End_Date DESC"
or quote:
strSql = "SELECT TOP 10 * FROM Games Order by End_Date DESC"
PS.: I found out by trial and error - it seems to work!
It would be nice to specify a date window, but:
I never seem to get those date delimiters right: quote:
Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/BG/forums/news.asp, line 24, column 30
response.write "On " & myNEWS(#'"end_date"'#) & " " & myNEWS("VICTOR")
-----------------------------^
|
Alfred
The Battle Group
CREDO
|
Edited by - Alfred on 31 August 2003 14:36:10 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 31 August 2003 : 16:15:48
|
Of course the TOP N clause works. The date delimiters needed to be added as text, before the date is added and after the date is added. However the date delimiters only need to be specified when using them in a SQL statement, not in other situations, like when you just want to display the date, as the code you posted seems to be do (with sintax errors).
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 31 August 2003 : 16:59:43
|
I understand.
WHat I woul like to do is display data for say, the last 30 days.
How would I have to code this:
quote:
do while not myNEWS.EOF and while myNEWS("end_date")>now-30
which I was obviously just guessing at. |
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 31 August 2003 : 17:45:30
|
"SELECT * FROM Games WHERE End_Date > #" & DateAdd("d",-30,Now()) & "# ORDER BY End_Date DESC"
|
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
Edited by - Nikkol on 31 August 2003 17:46:15 |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
|
| |
Topic |
|
Topic Locked
