Show last "X" days 3

[schase]

Hi all,

I would like to filter a recordset based on the value in a drop down box. - Default would show last 7 days worth of records, then drop down to show say 1,3,7,10,15,20,25,30
where if it is selected, than the page reloads to show.

any ideas?

Thank you
"I like a man who grins when he fights."

-- Prime Minister Winston Churchill

Stuart

 

[Bastien]

easy
have a select box with the variables in it...

<form name=moretime action=search.asp method=post>
<select name = vTime>
<option value=1>1</option>
<option value=1>3</option>
<option value=1>7</option>
<option value=1>15</option>
<option value=1>30</option>
....
</select>
<input type =submit value=submit>
</form>

action.asp
<% dim searchtime, sql, conn, dteStart 'any others needed
searchtime=CInt(request.form(&quot;vTime&quot) 'convert string to integer for sql query
if searchtime=&quot;&quot; then
searchtime = -7
else
seachtime=searchtime*-1 ' negative days
end if

dteStart = DateAdd(&quot;d&quot;,searchtime,now())

sql=&quot;select * from table where RecordOfDate between &quot; & dteStart & &quot; and &quot; & now()

objRS.open sql

%>

This is rough and i have not included the connection set ups, etc.

hth
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[schase]

I think we're close, but it's trying to find from tomorrow to today.

Syntax error (missing operator) in query expression 'fldUser=1AND fldDate between 6/14/2002 11:33:06 AM and 6/13/2002 11:33:06 AM'.

Heres my sql line.

&quot;SELECT * FROM qryMessages WHERE fldUser=&quot; & Session(&quot;svUserID&quot& &quot;AND fldDate between &quot; & dteStart & &quot; and &quot; & Now()

Thank you for your assistance. &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Chopstik]

I think you're missing a space in you SELECT statement. Try this:

&quot;SELECT * FROM qryMessages WHERE fldUser=&quot; & Session(&quot;svUserID&quot& &quot; AND fldDate between &quot; & dteStart & &quot; and &quot; & Now()

 

[Chopstik]

I think you're missing a space in your SELECT statement. Try this:

&quot;SELECT * FROM qryMessages WHERE fldUser=&quot; & Session(&quot;svUserID&quot& &quot; AND fldDate between &quot; & dteStart & &quot; and &quot; & Now()

 

[Bastien]

try doing a response.write sql to see what the statement looks like...easier to adjust when you can see what is in the statement.

hth
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[schase]

The space didn't work unfortunately. Same error message.

- I can't response Write until after the recordsets done - and it won't load that part.

Syntax error (missing operator) in query expression 'fldUser=1 AND fldDate between 6/14/2002 1:23:51 PM and 6/13/2002 1:23:51 PM'. &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Chopstik]

Maybe I'm misunderstanding something here, but why is your fldDate between tomorrow and today? 6/14 is tomorrow, no? Can dteStart be a future date? Or this might not be relevant at all. Just a thought... At the moment, not sure what else might be happening. SQL looks correct...

 

[schase]

I have no idea why it's doing that.

heres my recordset and all.

<% dim searchtime, rsMessage, conn, dteStart 'any others needed
searchtime=CInt(request.form(&quot;vTime&quot) 'convert string to integer for sql query
if searchtime=&quot;&quot; then
searchtime = -7
else
seachtime=searchtime*-7 ' negative days
end if

dteStart = DateAdd(&quot;d&quot;,searchtime,Now())
%>
<%
Dim rsMessage__MMColParam
rsMessage__MMColParam = &quot;1&quot;
if (Session(&quot;svUserID&quot <> &quot;&quot then rsMessage__MMColParam = Session(&quot;svUserID&quot
%>
<%
set rsMessage = Server.CreateObject(&quot;ADODB.Recordset&quot
rsMessage.ActiveConnection = MM_EPHIntra_STRING
rsMessage.Source = &quot;SELECT * FROM qryMessages WHERE fldUser=&quot; & Session(&quot;svUserID&quot& &quot; AND fldDate between &quot; & dteStart & &quot; and &quot; & Now()
rsMessage.CursorType = 0
rsMessage.CursorLocation = 2
rsMessage.LockType = 3
rsMessage.Open()
rsMessage_numRows = 0
%> &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Chopstik]

Two things to try or think:

1. - Why are you multiplying your searchtime times -7 instead of -1? Wouldn't you want to multiply it times -1?

2. - Change your SELECT statement to read as follows:

rsMessage.Source = &quot;SELECT * FROM qryMessages WHERE fldUser=&quot; & Session(&quot;svUserID&quot & &quot; AND fldDate between &quot; & dteStart & &quot; and &quot; & Now()

There didn't appear to be another space between (Session(&quot;svUserID&quot&) so I put it in there. That also seems like it might explain your error message. Hope this helps.

 

[schase]

I was messin with it to see if changing the -1 would do anything or not - I got the same error msg either way.

Now I'm getting the error message of:

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

my code's like this:
rsMessage.Source = &quot;SELECT * FROM qryMessages WHERE fldUser=&quot; & Session(&quot;svUserID&quot & &quot; AND fldDate between &quot; & dteStart & &quot; and &quot; & Now() &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[JuanitaC]

I don't know what database you are using, but if you are using SQL Server, you need single quotes around your dates:

rsMessage.Source = &quot;SELECT * FROM qryMessages WHERE fldUser=&quot; & Session(&quot;svUserID&quot & &quot; AND fldDate between '&quot; & dteStart & &quot;' and '&quot; & Now() & &quot;'&quot;

 

[schase]

I'm using Access2000 - I get the same error as before &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Chopstik]

Ok, I'm really grasping at straws here b/c I'm not sure what else to look at. Is all your code in your ASP page written on one line or on multiple lines? If it's written on multiple lines, then you don't have the underscore character in your code to concatenate your line(s) of code. If it's all on one line, then it's irrelevant.

The other issue may be with Access 2000, but don't know enough about it to help. Sorry.

 

[JuanitaC]

Copy the SQL string and write it to the screen above where you are building your recordset, then try running the query in your database... you often get more useful error messages there.

Response.write &quot;SELECT * FROM qryMessages WHERE fldUser=&quot; & Session(&quot;svUserID&quot & &quot; AND fldDate between '&quot; & dteStart & &quot;' and '&quot; & Now() & &quot;'&quot;

set rsMessage = Server.CreateObject(&quot;ADODB.Recordset&quot
rsMessage.ActiveConnection = MM_EPHIntra_STRING
rsMessage.Source = &quot;SELECT * FROM qryMessages WHERE fldUser=&quot; & Session(&quot;svUserID&quot & &quot; AND fldDate between '&quot; & dteStart & &quot;' and '&quot; & Now() & &quot;'&quot;
rsMessage.CursorType = 0
rsMessage.CursorLocation = 2
rsMessage.LockType = 3
rsMessage.Open()
rsMessage_numRows = 0

 

[schase]

It's all on one line Chopstik - i'ma grasping myself here

well this is what it returned.

SELECT * FROM qryMessages WHERE fldUser= AND fldDate between '6/13/2002 2:36:30 PM' and '6/13/2002 2:36:30 PM'
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'fldUser= AND fldDate between '6/13/2002 2:36:30 PM' and '6/13/2002 2:36:30 PM''.

/showmessage.asp, line 62

Line 62 is the recordset open command.

&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[schase]

found one error, now I'm using this

&quot;SELECT * FROM qryMessages WHERE fldFrom=&quot; & Request.Cookies(&quot;EPHuserName&quot & &quot; AND fldDate between '&quot; & dteStart & &quot;' and '&quot; & Now() & &quot;'&quot;

And I get the error:
SELECT * FROM qryMessages WHERE fldFrom=stuart AND fldDate between '6/13/2002 2:45:36 PM' and '6/13/2002 2:45:36 PM'
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/showmessage.asp, line 57
&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[JuanitaC]

If your field fldFrom is a string field, you need single quotes around it too:

&quot;SELECT * FROM qryMessages WHERE fldFrom='&quot; & Request.Cookies(&quot;EPHuserName&quot & &quot;' AND fldDate between '&quot; & dteStart & &quot;' and '&quot; & Now() & &quot;'&quot;

 

[schase]

well now I'm getting

SELECT * FROM qryMessages WHERE fldFrom='stuart' AND fldDate between '6/13/2002 2:53:20 PM' and '6/13/2002 2:53:20 PM'
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/showmessage.asp, line 59
&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[JuanitaC]

I just looked up in Access... it uses the # for dates, not the single quote, so try this:

&quot;SELECT * FROM qryMessages WHERE fldFrom=&quot; & Request.Cookies(&quot;EPHuserName&quot & &quot; AND fldDate between #&quot; & dteStart & &quot;# and #&quot; & Now() & &quot;#&quot;