| Author |
 Topic  |
|
Alfred
Senior Member
USA
1527 Posts |
 Posted - 31 October 2003 : 12:30:59
|
I checked the database by manual count, and can never get the correct number to display with this code:
quote:
strPreviousDay = "1/1/" & Year(Now())
strSql = "SELECT COUNT(M_NAME) AS myNAME_COUNT FROM FORUM_MEMBERS WHERE M_SINCE > #" & strPreviousDay & "#"
set rs = Server.CreateObject("ADODB.Recordset")
rs.open strSql, my_Conn
response.write rs("myNAME_COUNT")
rs.close
set rs = nothing
Can you see what's wrong in this code and why it gives me an incorrect number for myNAME_COUNT? |
Alfred
The Battle Group
CREDO
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 31 October 2003 : 12:49:09
|
probalby because M_SINCE isn't a date field. It's a text field.
As a matter of fact, I don't think there is a M_SINCE field in the MEMBERS table. |
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
Edited by - Nikkol on 31 October 2003 12:51:50 |
 |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 31 October 2003 : 13:24:33
|
Oh, I am sorry, Nikkol - it had M_DATE there, and I had changed it to M_SINCE just to test it.
But now I realize that M_DATE is a text field as well.
So I took out the ## again, but it now returns a zero as the value.
quote:
strPreviousDay = "1/1/" & Year(Now())
strSql = "SELECT COUNT(M_NAME) AS myNAME_COUNT FROM FORUM_MEMBERS WHERE M_DATE > " & strPreviousDay & ""
set rs = Server.CreateObject("ADODB.Recordset")
rs.open strSql, my_Conn
response.write rs("myNAME_COUNT")
rs.close
set rs = nothing
%>
|
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 31 October 2003 : 15:02:51
|
Oh yes, shouldn't it be this way then?:
quote:
strPreviousDay = DateToStr("1/1/" & Year(Now()))
strSql = "SELECT COUNT(M_NAME) AS myNAME_COUNT FROM FORUM_MEMBERS WHERE M_DATE > #" & strPreviousDay & "#"
set rs = Server.CreateObject("ADODB.Recordset")
rs.open strSql, my_Conn
response.write rs("myNAME_COUNT")
rs.close
set rs = nothing
%>
It gives an error though. quote:
strSql = "SELECT COUNT(M_NAME) AS myNAME_COUNT FROM FORUM_MEMBERS WHERE M_DATE > #" & strPreviousDay & "#"
set rs = Server.CreateObject("ADODB.Recordset")
rs.open strSql, my_Conn
response.write rs("myNAME_COUNT")
rs.close
set rs = nothing
%>
and if I take out the ## it gives a "syntax error": quote:
Error Type:
Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 'M_DATE > #20030101000000#'.
/BG/forums/news.asp, line 113
|
Alfred
The Battle Group
CREDO
|
Edited by - Alfred on 31 October 2003 15:08:34 |
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 31 October 2003 : 15:23:20
|
Ok, since I need the PreviousDay date for both text and date fields I have done this: quote:
strPreviousDayMem = DateToStr("1/1/" & Year(Now())) 'is for the text field
strPreviousDayGam = "1/1/" & Year(Now()) ' for the date fields
for the text field then it should be tis: quote:
...WHERE M_DATE > '" & strPreviousDayMem & "'"
and for the date fields this: quote:
WHERE M_DATE > #'" & strPreviousDayMem & "'#"
Is that correct now? |
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 31 October 2003 : 15:39:46
|
Got it!
This is the code for date fields: quote:
strSql = "SELECT COUNT(START_DATE) AS my_COUNT FROM GAMES WHERE START_DATE > #" & strPreviousDayGam & "# and RESULT=''"
and it displays, but for some reason it does not count correctly. It says 25 while there are 40 records with no result! |
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 31 October 2003 : 15:47:02
|
Yes, but I still don't know what that difference is.
If a record has no entry in a field, is it '' or is it null?
And all unfinished gams should be the same in that respect, no?
When I look into Access I just see empty fields there. |
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 31 October 2003 : 15:54:59
|
if a record does not appear to have an entry, it could be either empty or null, so you would have to check for both.
you aren't necessarily going to have just one or the other. it depends on what your code is doing. |
Nikkol ~ Help Us Help You | ReadMe | 3.4.03 fixes | security fixes ~ |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 31 October 2003 : 16:01:21
|
I see.
So, would this allow for both cases: quote:
strSql = "SELECT COUNT(START_DATE) AS my_COUNT FROM GAMES WHERE START_DATE > #" & strPreviousDayGam & "# and (RESULT='' or result=null)"
set rs = Server.CreateObject("ADODB.Recordset")
rs.open strSql, my_Conn
set rs = Server.CreateObject("ADODB.Recordset")
rs.open strSql, my_Conn
response.write rs("my_COUNT")
rs.close
set rs = nothing
|
Alfred
The Battle Group
CREDO
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 31 October 2003 : 16:27:25
|
That was it, Nikkol!
Bewitched stuff, right on Halloween...
Thanks for the help!
Happy witching! |
Alfred
The Battle Group
CREDO
|
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 03 November 2003 : 02:05:40
|
If one looks for the opposite condition (result <> '')
how is the syntax for null (or result Is not Null)? |
Alfred
The Battle Group
CREDO
|
|
|
|
Topic |
|
Topic Locked
