| Author |
 Topic  |
|
Alfred
Senior Member
USA
1527 Posts |
 Posted - 16 October 2003 : 13:58:50
|
If this selects records of the last 30 days:quote:
strSql = "SELECT * FROM Games WHERE Start_Date > #" & DateAdd("d",-30,Now()) & "# and victor=''"
set LateNews = my_Conn.Execute(strSql)
should this not select records of the last 3 days?: quote:
strSql = "SELECT * FROM Games WHERE Start_Date > #" & DateAdd("d",-3,Now()) & "# and victor=''"
set LateNews = my_Conn.Execute(strSql)
|
Alfred
The Battle Group
CREDO
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 16 October 2003 : 14:22:56
|
| Yes, with the second statement you will get all games with a start_date somewhere in the last 3 days, and a blank victor. |
 |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 16 October 2003 : 14:29:35
|
That is what I thought it should do.
However, it only works if I take out the "blank victor"! |
Alfred
The Battle Group
CREDO
|
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 16 October 2003 : 14:34:56
|
Right, so there is nothing that matches the criteria of a blank victor then  |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 16 October 2003 : 14:39:10
|
That would make sense, but unfortunately is not so.
I wanted to pull all unfinished games, which is why a put a blank for victor. When nothing was returned, I specifically recorded some unfinished games for the last 3 days.
I checked in the DB, and the records are all there.
At first I did this:quote:
strSql = "SELECT * FROM Games WHERE Start_Date > #" & DateAdd("d",-3,Now()) & "# and VICTOR = ''"
set LateNews = my_Conn.Execute(strSql)
which should select all unfinished games, right?
But it never did, and I added this to the response.write: quote:
if lateNews.eof then
response.write "NO NEW ATTACKS REPORTED!"
else
LateNEWS.movefirst
do while not LateNEWS.EOF
SO now it keeps telling me that there are no new games reported, although I just put some in. |
Alfred
The Battle Group
CREDO
|
Edited by - Alfred on 16 October 2003 14:46:51 |
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 16 October 2003 : 14:48:57
|
I never understood the difference between blank and null.
When do I have to use isNull.xyz(xxx)? |
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 16 October 2003 : 14:50:52
|
| the entry is null if you never inserted a value for it. the entry is empty if you set it equal to '' ... which can only be the case for text fields. |
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 16 October 2003 : 15:11:06
|
I see.
So in the case of a game record, when there is no victor yet, it should be NULL, right?
I insert a new record, but without a victor value.
Or does this then automatically set the victor field for ''? |
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 16 October 2003 : 15:40:52
|
| if you insert a new record and you do not specify victor = '', then the value is Null. (Unless you have a default value set on the database of ''). |
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 16 October 2003 : 16:11:43
|
I get it.
There is a field for victor in the report form, but I leave it open.
That means is stays null if I understand you right.
In any event, I am getting results now with this statement: quote:
strSql = "SELECT * FROM Games WHERE Start_Date > #" & DateAdd("d",-2,Now()) & "# and isNUll(VICTOR)"
set LateNews = my_Conn.Execute(strSql)
Thank you very much! |
Alfred
The Battle Group
CREDO
|
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 16 October 2003 : 21:04:18
|
You might want to do the following incase you clear out a games VICTOR value or the below will look for null or blank
strSql = "SELECT * FROM Games WHERE Start_Date > #" & DateAdd("d",-2,Now()) & "# and isNUll(VICTOR) or VICTOR=''"
set LateNews = my_Conn.Execute(strSql)
|
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 17 October 2003 : 17:43:17
|
I understand, and it makes sense.
ANother strange thing happened now though.
I have set the news for thew last 7 days:quote:
strSql = "SELECT * FROM Games WHERE Start_Date > #" & DateAdd("d",-7,Now()) & "# and isNUll(VICTOR) or VICTOR='' ORDER BY Start_Date DESC"
set LateNews = my_Conn.Execute(strSql)
and it does not show the latest game start of today!
I tested the statement on my localhost, where it does show today's record.
How can that be? |
Alfred
The Battle Group
CREDO
|
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 17 October 2003 : 19:11:50
|
| Are you running the same code AND DATA on your server ? |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 17 October 2003 : 21:03:18
|
Same code, but there are different games registered.
I just noticed that the same day's games do not show on the server, but do on localhost.
So now, out of curiosity I transferred the DB from the server to local and guess what - it shows today's entries! |
Alfred
The Battle Group
CREDO
|
Edited by - Alfred on 17 October 2003 21:25:59 |
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 17 October 2003 : 22:00:03
|
Well if the code is the same (& working properly) the only thing that can be different is your data.
You MIGHT want to look at installing Table Editor on your site then you can see the data in your db a lot easier. |
|
|
|
Topic |
|
Topic Locked
