|
| |
|
|
steveg
Posts: 277
Joined: 10/20/2004
Status: offline
 |
Criteria Problem - 10/22/2004 5:30:47
Hi Guys
I'm fairly new at this and so any help would be greatly appreciated.
I have a fairly small database that contains 7 fields:
primary key (sequential number)
name (text)
price (number)
effective date (date field {when price is effective from})
end date (date field {when price stopped being effective})
ref number 1 (number field {a unique reference in the industry})
ref number 2 (number field {a unique reference in the industry})
Customers will enter either ref number 1 or ref number 2 and a date and I want to display to them the record that has an effective date and end date range that contains their entered date. I do not want them to have all records (which would be nice and easy) as we would like to protect our price change history.
I do not appear to be able to specify this criteria using the FrontPage DRW. 
I will be doing more of this type of work in the future so would be happy to also have references to good training/reference material as well as some help on this urgent need.
I hope someone can help.
Thanks
Steve
|
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Criteria Problem - 10/22/2004 9:52:57
quote:
I do not appear to be able to specify this criteria using the FrontPage DRW.
What have you tried?
Can you display all records? IOW, does it work selecting no criteria?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
steveg
Posts: 277
Joined: 10/20/2004
Status: offline
|
RE: Criteria Problem - 10/22/2004 10:04:17
Hi
Thanks for responding. Yes I can create a search on either ref number 1 or ref number 2 and I can sort the output by the effective date, but I have been told that I should not display all the records for that item as it is confidential information and should only display the record with the date in the date range..
I want to have a customer enter a reference number and a date and the record that has the date in the range between its effective and end date is the only one displayed. I have little experience of ASP and this seems to be outside of the capabilities of DRW. (I hope I am wrong)
I am therefore looking for a little help to get this started/created.
Cheers
Steve
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Criteria Problem - 10/22/2004 10:36:04
This should work but you'll need to put this DRW on a diet.
**Save a copy of your page first**
In Step 3 of the DRW, select More Options:Criteria and add 2 criteria:
EffectiveDate IsGreaterThan EffectiveDate
EndDate IsLessThan EndDate
(or whatever field names match yours)
Save the DRW and put it on a diet. You can see how here:
http://www.outfront.net/spooky/adv_drw_diet.htm
Then, find the line that looks something like:
fp_sQry="SELECT * FROM tblItems WHERE (RefNumber1 = ::RefNumber1:: OR RefNumber2 = ::RefNumber2:: AND EffectiveDate < #::EffectiveDate::# AND EndDate > #::EndDate::#)"
(or whatever your line looks like based on your initial criteria.
You need to change this line to something like:
fp_sQry="SELECT * FROM tblItems WHERE (RefNumber1 = ::RefNumber1:: OR RefNumber2 = ::RefNumber2:: AND (EffectiveDate < #::myFormDateField::# AND EndDate > #::myFormDateField::#))>"
Change the name myFormDateField to whatever the name of your date field is on your submitting form. Of course, be sure to match up field names but if you're using the DRW, they should be all right as is (except for the noted one of course). Also you may want to change the "<" and ">" to "<=" and ">=" to have your selection be inclusive.
This is assuming an Access DB and I'm using FP 2000 DRW as an example but I hope this gives you an idea.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
steveg
Posts: 277
Joined: 10/20/2004
Status: offline
|
RE: Criteria Problem - 10/25/2004 6:04:47
I have followed the instructions but I get no response from the query. Does the date field respond to a greater/lesser than? Also there might be a further twist in that all of the records that are current have no end date???
I used the macro to diet my code. My code is as follows:
<table BORDER="0">
<tr>
<td><b>GCNinput</b></td>
<td>
<input TYPE="TEXT" NAME="GCNinput" VALUE="<%=Request("GCNinput")%>" size="20"></td>
</tr>
<tr>
<td><b>EFFECTIVEDATEinput</b></td>
<td>
<input TYPE="TEXT" NAME="EFFECTIVEDATEinput" VALUE="<%=Request("EFFECTIVEDATEinput")%>" size="20"></td>
</tr>
</table>
<p><br>
<input TYPE="Submit"><input TYPE="Reset"></p>
<p> </p>
</form>
<!--#include file="../../../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM MAC WHERE (GCN = ::GCNinput:: AND (""Effective Date"" <= '::EFFECTIVEDATEinput::' AND ""End Date"" >= '::EFFECTIVEDATEinput::')) ORDER BY ""Effective Date"" ASC"
fp_sDefault="GCNinput=99999&EFFECTIVEDATEinput=01/01/1996"
fp_sNoRecords="Please enter another GCN."
fp_sDataConn="4dmac"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=6
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<p><b>TRADE -
NAME:</b> <%=FP_FieldVal(fp_rs,"TRADE - NAME")%></p>
<p><b>4D MAC:</b> <%=FP_FieldVal(fp_rs,"4D MAC")%></p>
<p><b>Effective Date:</b> <%=FP_FieldVal(fp_rs,"Effective Date")%></p>
<p><b>End Date:</b> <%=FP_FieldVal(fp_rs,"End Date")%></p>
<p><b>GCN:</b> <%=FP_FieldVal(fp_rs,"GCN")%></p>
<p><b>GSN-1:</b> <%=FP_FieldVal(fp_rs,"GSN-1")%></p>
<hr>
<!--#include file="../../../_fpclass/fpdbrgn2.inc"-->
</body></html>
Cheers
Steve
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Criteria Problem - 10/25/2004 10:21:54
quote:
Also there might be a further twist in that all of the records that are current have no end date???
So is that true of ALL current records and false of ALL non-current stuff? If so, you may be able to change the query to something like:
fp_sQry="SELECT * FROM MAC WHERE (GCN = ::GCNinput:: AND ((""Effective Date"" <= '::EFFECTIVEDATEinput::') AND (""End Date"" IS NULL))) ORDER BY ""Effective Date"" ASC"
Is that any better?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
steveg
Posts: 277
Joined: 10/20/2004
Status: offline
|
RE: Criteria Problem - 10/25/2004 10:44:05
I'm really sorry to be a pain here. I probably wasn't clear enough with my last post. There are potentially multiple records for a GCN number. The difference between the records would be the price of the item and the date range that the price was valid for. A customer might want to find a price at a particular date in time or they might want a price that happens to be a current price. An item will not get an end date until there is a new record because the price has changed. The new record will not have an end date.
Could I say something like:
fp_sQry="SELECT * FROM MAC WHERE (GCN = ::GCNinput:: AND (((""Effective Date"" <= '::EFFECTIVEDATEinput::') AND (""End Date"" IS NULL)))) OR (((""Effective Date"" <= '::EFFECTIVEDATEinput::') AND (""End Date"" >= '::EFFECTIVEDATEinput::')))) ORDER BY ""Effective Date"" ASC"
Sorry for not being clear enough in the previous post.
Cheers
Steve
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Criteria Problem - 10/25/2004 12:20:49
quote:
fp_sQry="SELECT * FROM MAC WHERE (GCN = ::GCNinput:: AND (((""Effective Date"" <= '::EFFECTIVEDATEinput::') AND (""End Date"" IS NULL)))) OR (((""Effective Date"" <= '::EFFECTIVEDATEinput::') AND (""End Date"" >= '::EFFECTIVEDATEinput::')))) ORDER BY ""Effective Date"" ASC"
How 'bout this instead:
fp_sQry="SELECT * FROM MAC WHERE ((GCN = ::GCNinput::) AND (""Effective Date"" <= '::EFFECTIVEDATEinput::') AND ((""End Date"" IS NULL) OR (""End Date"" >= '::EFFECTIVEDATEinput::')) ORDER BY ""Effective Date"" ASC"
That any better? As I see it, that should work if the date ranges don't overlap.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
steveg
Posts: 277
Joined: 10/20/2004
Status: offline
|
RE: Criteria Problem - 10/25/2004 12:40:28
There seems to be an extra ( at the beginning. Is it superflouos or should it be closed after the effective date element and prior to the end date element?
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Criteria Problem - 10/25/2004 12:53:57
Oh yeah, it looks like you have to put in default values as well or it will error on your first entry to that page.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
steveg
Posts: 277
Joined: 10/20/2004
Status: offline
|
RE: Criteria Problem - 10/25/2004 14:29:03
I thought I had used the code with the brackets. I have just copied and pasted again. Yes I am using Access. I now get this message:
Database Results Error
The database connection named '' is undefined.
This problem can occur if:
* the connection has been removed from the web
* the file 'global.asa' is missing or contains errors
* the root folder does not have Scripting permissions enabled
* the web is not marked as an Application Root
I can't see that anything else has changed?
Cheers
Steve
| |
|
|
|
steveg
Posts: 277
Joined: 10/20/2004
Status: offline
|
RE: Criteria Problem - 10/25/2004 16:25:56
Guess what! We have success.
When I cut and pasted the new code, I cut 3 lines too many. I put them back et voila. I have now formatted the page etc. and it looks great.
I have to offer my sincere thanks for all your help. Your patience is also greatly appreciated. This is a great forum (don't know what I would have done without it) and I will be spreading the word!!
A coupla questions: I wanted the GSN search to be separate from the GCN search so I copied the page and renamed it. I changed all references to GCN to GSN and the page doesn't work and I get an error message like this:
Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine
I am obviously missing something stupid that I should change but sometimes you just can't see for looking!!
Also, I would like to pretty it up a bit more by having a calendar module to select the effective date from. Do you know of any that "plug-in" or are simple to set-up?
Cheers
Steve
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Criteria Problem - 10/26/2004 9:09:33
quote:
I wanted the GSN search to be separate from the GCN search so I copied the page and renamed it. I changed all references to GCN to GSN and the page doesn't work and I get an error message like this:
Maybe you could elaborate a little on this; I'm not quite sure what you mean. Did you just do a Find&Replace on those letters? Is it in a different table? different fields? different DB?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Criteria Problem - 10/26/2004 9:53:00
quote:
<%
fp_sQry="SELECT * FROM MAC WHERE (GSN-1 = ::GSNinput::) AND ([Effective Date] <= #::EFFECTIVEDATEinput::#) AND (([End Date] IS NULL) OR ([End Date] >= #::EFFECTIVEDATEinput::#)) ORDER BY [Effective Date] ASC"
Try putting brackets around GSN-1 as in:
SELECT * FROM MAC WHERE ([GSN-1] = ::GSNinput::) ...
ALso near the bottom of the page you have:
<%=FP_FieldVal(fp_rs,"GCN")%>
did you mean:
<%=FP_FieldVal(fp_rs,"GSN-1")%>
Does that get you any closer? If not, try putting a Response.write in there to see what SQL you're actually sending to the DB like so:
....
fp_sQry="SELECT * FROM MAC WHERE (GSN-1 = ::GSNinput::) AND ([Effective Date] <= #::EFFECTIVEDATEinput::#) AND (([End Date] IS NULL) OR ([End Date] >= #::EFFECTIVEDATEinput::#)) ORDER BY [Effective Date] ASC"
Response.write(fp_sQry)
fp_sNoRecords="<tr><td colspan=7 align=left width=""100%"">Please enter another GSN.</td></tr>"
....
That should display the actual SQL in the browser. Can you post that here?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Criteria Problem - 10/26/2004 10:29:46
quote:
should reset clear the fields and leave you with an empty form?
It should reset the form back to your initial values. If they were blank, it should reset them to blank; if they were populated, then they should be populated on reset as well.
I posted a link to a popup calendar script that I like not too long ago. Let me find it and I'll repost here.
EDIT: Here's my other post................
I like this one 'cause the calendar that pops up is in HTML and you can customize the look quite nicely. Hope it helps.
http://javascript.internet.com/calendars/popup-date-picker.html
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
 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 
: 
but I am now getting this error message: 
(see that's why I use brackets - they're far less confusing to me
