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

 

[schase]

well now I'm getting

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

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

/showmessage.asp, line 59


Out of curiousity, on the dteStart variable - what is "d" ?

"Damn the torpedoes, full speed ahead!"

-Adm. James Farragut

Stuart

 

[schase]

SELECT * FROM qryMessages WHERE fldFrom=stuart AND fldDate between #6/13/2002 3:14:02 PM# and #6/13/2002 3:14:02 PM#

Where i'm getting that now, why would the date/time be exactly the same? "Damn the torpedoes, full speed ahead!"

-Adm. James Farragut

Stuart

 

[JuanitaC]

Sorry, I forgot to add the single quotes back into that last post for the fldFrom:
SELECT * FROM qryMessages WHERE fldFrom='" & Request.Cookies("EPHuserName&quot & "' AND fldDate between #" & dteStart & "# and #" & Now() & "#"

As for the dteStart being the same as Now(), check the values that you are using to perform your calculation... for instance, if request.form("vTime&quot give you 0, 0*-1 is 0, so the value you end up with will be the same as Now().

Lastly, the "d" in the DateAdd function is the interval you want to add to the date. In this case, "day" is the interval. Check out DevGuru for the different intervals you can use:

http://www.devguru.com/Technologies/vbscript/quickref/dateadd.html

 

[schase]

Thanks Juanita,

That worked pretty good!

Only one problem - and it may be by my design. - by mistake.

I've got the vTime form on the same page - so hopefully the user can pick a different day and it will reload with the new value -

But it initially loads the page as blank - I have to select 1 and hit go for it to load the messages. And it will only do one day no matter what I select. Which surprises me, I thought it would take the value.

"Damn the torpedoes, full speed ahead!"

-Adm. James Farragut

Stuart

 

[JuanitaC]

I found the reason you can only select 1 day - every interval in your select box has 1 as the value:

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>

Change the 1 to the value you want:
select name = vTime>
<option value=1>1</option>
<option value=3>3</option>
<option value=7>7</option>
<option value=15>15</option>
<option value=30>30</option>
....
</select>

 

[schase]

lol I just saw that.

:: slaps self ::

Is there a way to default to showing 7 days versus no days at first?

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

-Adm. James Farragut

Stuart

 

[JuanitaC]

You are on to the right path here, but you might need to add to it:

if IsNull(searchtime) OR searchtime=&quot;&quot; then
searchtime = -7
else
seachtime=searchtime*-1 ' negative days
end if

 

[schase]

Thanks Juanita,

If I can bug one last time.

I realized that I was pulling the person who sent the messages rather than the intended recipient. I figured a quick change the values should work right? ugggh!

I'm getting a Data type mismatch in criteria expression.
Except the field names are all correct. Is there a different format for a Session?


&quot;SELECT * FROM qryMessages WHERE fldUser='&quot; & Session(&quot;svUserID&quot & &quot;' AND fldDate between #&quot; & dteStart & &quot;# and #&quot; & Now() & &quot;#&quot; &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[JuanitaC]

That should work, just make sure that you actually have a value in the Session: write out the SQL statement like we did earlier.

 

[schase]

yup - thanks again, guess I'm tired - doing stupid stuff. I realized the session was numeric.

got it.

thank you so much

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

-Adm. James Farragut

Stuart

 

[schase]

Hey,

Dunno if your still looking at this, but I can't get the value of -7 or any value to default the page.

It will not show anything unless either I just barely left a message or I click a number of days.

Any help would be greatly appreciated. &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Chopstik]

In your drop down box, you can make 7 your default choice. Simply change the code to look like this:

<option value=1 selected>7</option>

This should then make it so that if the user doesn't choose anything, it would always be 7. Hope this helps.

 

[schase]

I tried that, no go

I also tried this

<% 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


no go there either. &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Chopstik]

When you run your page and you look at that dropdown box, you're saying that the drop down box is empty and there is nothing there? You only see the choices if you click on the down arrow button on the right hand side of the drop down box? This is the case even if you add the &quot;selected&quot; attribute into the code for that line? That should work, not sure why it wouldn't.

Also, your code looks right. Try putting in a response.write statement after your if/then/else statement and see what the value of searchtime is. Also, try to put response.write statement in the middle of the code and see if you can get a response there. That should help to debug it.

 

[schase]

I've got the selected and it does show down on the form. And once I click submit it will reload the page to show.

But the default value when the page loads initially shows nothing.

I did a response.write (thanks for pointing that out, for some reason I keep forgetting it)

It shows the initial value as zero.

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

-Adm. James Farragut

Stuart

 

[Chopstik]

Ok, when you first load the page, there appears to be no default value. Then, when the user chooses one of the values from the drop down box, it will reload the page and then you will have a default value in the drop down box? Is this correct?

Not too sure about how this one works... I'm sure it's something simple...

 

[schase]

thats correct,

I'm guessing it's not pulling the correct value

But I did find a work-around.

I can send a querystring value across as -7 with the form value in it.

it works.

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

-Adm. James Farragut

Stuart

 

[Chopstik]

Not sure why it wouldn't pull the correct &quot;selected&quot; value the first time around, but if sending a querystring value works, then that should be good enough. Good luck.

 

[schase]

I dont know, It didnt seem to like POST and request.form

but it's all happy-like with GET and request.querystring &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart