|
| |
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
 |
Default Date and Search by Range - 1/26/2005 8:40:20
Gang,
I am trying to accomplish a couple things with regards to searching by date.
1. I'd like to set a default date that would show all DB information for the last 7 days.
2. I want to make the user be able to search for information between a certain date range.
I'm not quite sure where to start, as I have searched the forum for the previous date questions, but am just kind of confused by it all.
Thanks in advance,
JB
|
|
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 10:32:23
Ok...here's my first attempt at the search code:
fp_sQry="SELECT * FROM CriticalProjects WHERE (Layer LIKE '%::Layer::%' AND ProjectOwner LIKE '%::ProjectOwner::%' AND Scanner LIKE '%::Scanner::%' AND Product LIKE '%::Product::%' AND ID LIKE '%::ID::%') AND ((Purpose LIKE '%::WordSearch::%') OR (ScannerInstructions LIKE '%::WordSearch::') OR (SemInstructions LIKE '%::WordSearch::%')) AND ( ProjectDate BETWEEN '#FromDate#' AND '#ToDate#') ORDER BY ProjectDate DESC"
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&ProjectDate=%"
Here is the error I generate:
Database Results Error
Description: Syntax error converting datetime from character string.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for SQL Server
One or more form fields were empty. You should provide default values for all form fields that are used in the query.
All suggestions welcome.....
JB
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 11:37:29
quote:
ProjectDate BETWEEN '#FromDate#' AND '#ToDate#'
Try without the apostrophes (at least for Access)
(ProjectDate BETWEEN #FromDate# AND #ToDate#)
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 11:38:51
quote:
(ProjectDate BETWEEN #FromDate# AND #ToDate#)
OOPS! I just saw you were using SQL Server. Try without the # signs and use the apostrophes:
(ProjectDate BETWEEN 'FromDate' AND 'ToDate')
That any help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 12:15:00
rdouglass,
Thanks for the help. I now have the query working by using:
(ProjectDate BETWEEN '::FromDate::' AND '::ToDate::')
Now I need to be able to set the default date to the current date minus 7 days. Any ideas?
JB
| |
|
|
|
dzirkelb1
Posts: 1306
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 12:20:01
Is it possible in the drw to do Date()-7 for the default value?
| |
|
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 12:36:52
I'm not sure how to code it.
I have:
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&ProjectDate > 'Date()-7'"
I have also tried
ProjectDate > Date()-7
But get no records returned.....
JB
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 12:46:54
quote:
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&ProjectDate > 'Date()-7'"
Been a while since I hacked up any DRW's but how about this:
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&ProjectDate=" & DateAdd("d",-7,Date())
That any help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 12:54:45
....ProjectDate>"& DateAdd("d",-7,Date())
still gives me no records returned. :(
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 13:06:24
Assuming the DRW is on a diet, (inside the DRW) can you do a:
Response.write(fp_sDefault)
To see if the date is being calc'd?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 13:12:03
Response.write(fp_sDefault)
generates:
Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&ProjectDate>1/19/2005
So it looks like it's getting the date, just not filtering with it.
JB
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 13:15:51
quote:
So it looks like it's getting the date, just not filtering with it.
Did you see my post immediately above reguarding the wrong default fields?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 13:19:12
That did the trick :)
Thanks a ton rdouglass!
One last thing I am trying to figure out...but it's for another post.
Thanks again!
JB
| |
|
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
|
RE: Default Date and Search by Range - 1/26/2005 17:28:30
OK...I thought I had what I needed, but not so fast :(
Is there a way to wildcard the From and To values for the date?
Here's my deal, when a user initially goes to the query results page, I want it to default to information from the last 14 days. However, if the user submits different search criteria, I want his query to pull all ata matching his criteria regardless of the date, unless he enters a date range.
JB
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Default Date and Search by Range - 1/27/2005 9:26:07
quote:
when a user initially goes to the query results page, I want it to default to information from the last 14 days. However, if the user submits different search criteria, I want his query to pull all ata matching his criteria regardless of the date, unless he enters a date range.
Right where the fp_sDefault line is, maybe add some logic like so:
IF Instr(Request.servervariables(HTTP_REFERER),Request.servervariables(URL))+0 = 0 THEN
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&FromDate=" & DateAdd("d",-7,Date()) & "&ToDate=" & Date()
ELSE
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%"
END IF
See what I'm doing? If the page didn't post to itself, then use the default dates; otherwise don't use 'em
Haven't tested, but I think it should work. Any help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
|
RE: Default Date and Search by Range - 1/27/2005 12:10:13
rdouglass...
This line:
IF Instr(Request.servervariables(HTTP_REFERER),Request.servervariables(URL))+0 = 0 THEN
is giving me this error:
Request object error 'ASP 0102 : 80004005'
Expecting string input
/project_folder_test/completed_projects.asp, line 231
The function expects a string as input.
JB
| |
|
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
|
RE: Default Date and Search by Range - 1/27/2005 13:05:40
That got rid of the error, however I get no recordds when I post from the page with anything other than a form/to date.
Here's the code I have:
fp_sQry="SELECT * FROM CriticalProjects WHERE (ID LIKE '%::ID::%' AND Layer LIKE '%::Layer::%' AND ProjectOwner LIKE '%::ProjectOwner::%' AND Scanner LIKE '%::Scanner::%' AND Product LIKE '%::Product::%' AND ID LIKE '%::ID::%') AND ((Purpose LIKE '%::WordSearch::%') OR (ScannerInstructions LIKE '%::WordSearch::') OR (SemInstructions LIKE '%::WordSearch::%')) AND ( ProjectDate BETWEEN '::FromDate::' AND '::ToDate::') ORDER BY ProjectDate DESC"
IF Instr(Request.servervariables("HTTP_REFERER"),Request.servervariables("URL"))+0 = 0 THEN
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&FromDate=" & DateAdd("d",-14,Date()) & "&ToDate=" & Date()
ELSE
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%"
END IF
Also, I get no records if I only enter a from date.
The only way I get records is if I go to the page from another url, or if I give a from/to date and post from the page. Otherwise I get nothing.
JB
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Default Date and Search by Range - 1/27/2005 13:15:02
quote:
The only way I get records is if I go to the page from another url, or if I give a from/to date and post from the page. Otherwise I get nothing.
Exactly. Isn't that what you wanted? To know if the page has been posted to itself?
Do you have situations where users will go directly to the page as opposed to using a link? If so, maybe something like:
IF (Instr(Request.servervariables("HTTP_REFERER"),Request.servervariables("URL"))+0 = 0) OR (trim(Request.servervariables("HTTP_REFERER"))&"" = "") THEN
Haven't tested so just a quick guess....
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
|
RE: Default Date and Search by Range - 1/27/2005 13:29:31
Maybe that's what I said I wanted...but not what I meant to say I wanted. Here's the deal....
This database gets about 200+ new entries a week and right now when the user comes to the search/results page they default view was all entries in the database.
As you can imagine, this would be a huge amount of items in a month or so. Therefore, I wanted to default to the last 2 weeks of entries when a user comes to the page.
We've accomplished this. Thanks.
Now the user should be able to search by his name or some other parameter and pull up all entries matching that criteria regardles off the date they were submitted.
I also what them to be able to insert a from/to date if they want to refine their search even more.
This is not currently capable. How can this part be accomplished?
Side Note: It would also be nice to make the to date default to the current day whenever a from date is entered and a to date is not.
Thanks for your help so far,
JB
| |
|
|
|
Aelaron
Posts: 223
Joined: 6/8/2004
Status: offline
|
RE: Default Date and Search by Range - 1/27/2005 14:25:47
OK, here's the report:
I get the expected results when going directly to the page, to the page from a link, and if I enter a from/to date.
I get no recordds if I search with any parameter besides a from/to date.
JB
| |
|
|
|
rdouglass
Posts: 9265
From: Biddeford, ME USA
Status: offline
|
RE: Default Date and Search by Range - 1/27/2005 14:35:31
quote:
I get no recordds if I search with any parameter besides a from/to date.
OK. One last stab at it:
IF (Instr(Request.servervariables("HTTP_REFERER"),Request.servervariables("URL"))+0 = 0) OR (trim(Request.servervariables("HTTP_REFERER"))&"" = "") THEN
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%&FromDate=" & DateAdd("d",-7,Date()) & "&ToDate=" & Date()
ELSE
fp_sDefault="Layer=%&ProjectOwner=%&Scanner=%&Product=%&ID=%&Purpose=%&ScannerInstructions=%&SemInstructions=%"
IF trim(Request.Form("FromDate")&"") = "" THEN
fp_sDefault = fp_sDefault & "&FromDate=1/1/1900"
END IF
IF trim(Request.Form("ToDate")&"") = "" THEN
fp_sDefault = fp_sDefault & "&ToDate=" & Date()
END IF
END IF
See what I did? Check the fields and if blank, set them to something and add it to the default. I used 1/1/1900 as a FromDate. That should grab stuff from the beginning.
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
DRW desperation
Posts: 1
Joined: 8/28/2005
Status: offline
|
RE: Default Date and Search by Range - 8/28/2005 21:05:04
Aelaron,
Been pulling my hair out trying to work this one out and its killing me.
I like you have a date field that I wish to default using the Frontpage DRW. Default date of 1 month ago.
DateAdd("m",-1,Date())
I figured the Dateadd function out but cannot get the code to stick on the page.
I am a relative novice in Frontpage and are attempting to use the wizard as opposed to the editor. When setting the default in the wizard it does not save correctly.
So I attempt to key the code in directly. I do this is the grey and marone coloured areas but continue to get date and time conversion issues.
Can you provide any tips as to where I might be going wrong or maybe what code I should be placing when using the wizard?
Any help would be greatly appreciated.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Default Date and Search by Range - 8/28/2005 21:15:50
Welcome to OutFront. Your code changes get undone by FP's webbots. Run the macro here:
http://www.frontpagewebmaster.com/m-279509/tm.htm
It will delete the bot portion of the code so you can then edit the ASP. The bot portion is for the most part simply bloat anyway. The only functionality you lose is the ability to rerun the wizard on the page in question so you are forced to hand code any changes.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
