|
| |
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
 |
< Date Question - 9/7/2002 11:40:29
To All,
Does anybody know how to set a SQL search that checks for dates prior to the date entered??? In other words, using the <= method. This is actually for another post. I can' t figure out why < cannot be used.
If I try " myDate <= todaysDate"
The DRW trys to close the opening <.
Example:
WHERE (myDate <= todaysdate)"
does this in the DRW:
WHERE (myDate <= todaysdate)>" 
The > works, = works, and >= works..... The < and <= do NOT work!!!
Any suggestions would be appreciated.
Thanks in Advance.
LLL
_____________________________
|
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: < Date Question - 9/7/2002 14:11:24
bobby,
I don' t know if I can reserve it. Do you mean the field??? I' m lost.
Also, your example showed >=. That works. The problem is < or <=.
Thanks
LLL
_____________________________
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: < Date Question - 9/7/2002 16:27:56
Hi LLL,
Give your data fields in active a Text value not Date/Time
Regards
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: < Date Question - 9/7/2002 17:12:16
Hi,
Another solution which worked for me.
In the DRW HTM view, locate your date field int he grey code. Let' s say your date field is " Mydate" locate it in the grey code where it sayd
' ::Mydate::' and change the ' into #, so your date field in the grey code becomes
#::MyDate::#
Save the page and that' s it.
Then Check the SQL line in the HTML view to make sure that it says:
fp_sQry=" SELECT * FROM Dates WHERE (MyDate < #::DateEntered::#)"
I repeat: Complete your DRW, set the date " <" or " <=" ..etc, no matter then go to the grey code after you finish and save it.
Regards
Hisham
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: < Date Question - 9/7/2002 19:30:12
Hisham,
Thanks for your reply. I tired this prior to sending the post. But I tried it again to make sure I wasn' t going crazy. It didn' t work. The gray area is where I usually make my custom SQL query changes. But if you look 7 lines below the gray SQL, you will see an identical SQL query in the brown area; (it' s the brown area on my machine, it may be a different color on your machine). This is the SQL query that does the work. If you change the gray SQL and it does not match SQL syntax, the DRW will change the brown SQL incorrectly, or erase it when you save.
If I type the following in the gray SQL:
fp_sQry=" SELECT * FROM Dates WHERE (MyDate < #::DateEntered::#)"
Then the brown SQL looks like the following on save:
fp_sQry=" SELECT * FROM DistNewUserServices WHERE (DateJoined <= #::eDate::#)>o"
See - the DRW is trying to close the first < bracket.
If I ignore it and try to run the query anyway I get the following error message:
Too few parameters. Expected 1.
This is weird. The SQL works with =, > and >=, but it does not work with < and <=. From what I can figure, there is an SQL character that represents < that we don' t know about. I tried numerous SQL sites and Microsoft' s too. Nobody has much to say about <. It seems that dates are taboo in SQL. Everybody' s got examples, but none that demonstrate dates.
Thanks for all your help. 
If you think of something else, I' m ALL ears. 
Cheers!!!
LLL
_____________________________
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: < Date Question - 9/8/2002 0:49:51
Hi LLL,
I went to the HTML view of the DRW, and on the line above the sql (brown) line I spotted ' ::TheDateField::' and changed it to #::TheDateField::#, without adding any " , then saved the page while I am in HTML view, and then I saw that the sql (brown) line has changed the date into #::TheDateField::#, the same way I put it in the grey area.
I need to refere to this to make sure:In your post you mentioned that You put #::DateEntered::# and you got ' ::eDAte::' , was it a mistake in the post of this is how you used it?
Please take a look at the following link and test it. In my database I entered a date in December and 4 or 5 dates in August. Try and type
08/20/2002 or 05/01/2003 or 08/18/2002, and you will see all dates falling before these dates.
http://www.fmhs.uaeu.ac.ae/nml/HishamDate.asp
This is the code in my page: I highlighted what I changed in red:
<html>
<head>
<meta name=" GENERATOR" content=" Microsoft FrontPage 5.0" >
<meta name=" ProgId" content=" FrontPage.Editor.Document" >
<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not Edit.
FP_CharSet = " windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv=" Content-Type" content=" text/html; charset=windows-1252" >
<title>New Page 1</title>
</head>
<body>
<form BOTID=" 0" METHOD=" POST" ACTION=" HishamDate.asp" >
</form>
</body>
</html>
<form BOTID=" 0" METHOD=" POST" ACTION=" HishamDate.asp" >
<table BORDER=" 0" >
<tr>
<td><b>DateEntered</b></td>
<td>
<input TYPE=" TEXT" NAME=" DateEntered" VALUE=" <%=Request(" DateEntered" )%>" size=" 20" ></td>
</tr>
</table>
<p><br>
<input TYPE=" Submit" ><input TYPE=" Reset" ><!--webbot bot=" SaveAsASP" clientside suggestedext=" asp" preview=" " startspan --><!--webbot bot=" SaveAsASP" endspan --></p>
<p> </p>
</form>
<table width=" 100%" border=" 1" >
<thead>
<tr>
<td><b>Name</b></td>
<td><b>DateEntered</b></td>
</tr>
</thead>
<tbody>
<!--webbot bot=" DatabaseRegionStart" s-columnnames=" Name,DateEntered" s-columntypes=" 202,135" s-dataconnection=" Dates" b-tableformat=" TRUE" b-menuformat=" FALSE" s-menuchoice s-menuvalue b-tableborder=" TRUE" b-tableexpand=" TRUE" b-tableheader=" TRUE" b-listlabels=" TRUE" b-listseparator=" TRUE" i-listformat=" 0" b-makeform=" TRUE" s-recordsource=" Dates" s-displaycolumns=" Name,DateEntered" s-criteria=" [DateEntered] LT {DateEntered} +" s-order s-sql=" SELECT * FROM Dates WHERE (DateEntered <#::DateEntered::#)" b-procedure=" FALSE" clientside suggestedext=" asp" s-defaultfields=" DateEntered=01/01/1900" s-norecordsfound=" No records returned." i-maxrecords=" 0" i-groupsize=" 0" botid=" 0" u-dblib=" _fpclass/fpdblib.inc" u-dbrgn1=" _fpclass/fpdbrgn1.inc" u-dbrgn2=" _fpclass/fpdbrgn2.inc" tag=" TBODY" preview=" <tr><td colspan=64 bgcolor=" #FFFF00" align=" left" width=" 100%" ><font color=" #000000" >This is the start of a Database Results region.</font></td></tr>" startspan b-procedure --><!--#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 %>
<%
(look here at the fp_sQry, I did not touch it)
fp_sQry=" SELECT * FROM Dates WHERE (DateEntered < #::DateEntered::#)"
fp_sDefault=" DateEntered=01/01/1900"
fp_sNoRecords=" <tr><td colspan=2 align=left width=" " 100%" " >No records returned.</td></tr>"
fp_sDataConn=" Dates"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=" "
fp_sMenuValue=" "
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file=" _fpclass/fpdbrgn1.inc" -->
<!--webbot bot=" DatabaseRegionStart" endspan i-checksum=" 58094" --><tr>
<td>
<!--webbot bot=" DatabaseResultColumn" s-columnnames=" Name,DateEntered" s-column=" Name" b-tableformat=" TRUE" b-hashtml=" FALSE" b-makelink=" FALSE" clientside b-MenuFormat preview=" <font size=" -1" ><<</font>Name<font size=" -1" >>></font>" startspan --><%=FP_FieldVal(fp_rs," Name" )%><!--webbot bot=" DatabaseResultColumn" endspan i-checksum=" 3900" --></td>
<td>
<!--webbot bot=" DatabaseResultColumn" s-columnnames=" Name,DateEntered" s-column=" DateEntered" b-tableformat=" TRUE" b-hashtml=" FALSE" b-makelink=" FALSE" clientside b-MenuFormat preview=" <font size=" -1" ><<</font>DateEntered<font size=" -1" >>></font>" startspan --><%=FP_FieldVal(fp_rs," DateEntered" )%><!--webbot bot=" DatabaseResultColumn" endspan i-checksum=" 28315" --></td>
</tr>
<!--webbot bot=" DatabaseRegionEnd" b-tableformat=" TRUE" b-menuformat=" FALSE" u-dbrgn2=" _fpclass/fpdbrgn2.inc" i-groupsize=" 0" clientside tag=" TBODY" preview=" <tr><td colspan=64 bgcolor=" #FFFF00" align=" left" width=" 100%" ><font color=" #000000" >This is the end of a Database Results region.</font></td></tr>" startspan --><!--#include file=" _fpclass/fpdbrgn2.inc" -->
<!--webbot bot=" DatabaseRegionEnd" endspan i-checksum=" 62730" --></tbody>
</table>
<p> </p>
<p> </p>
I just changed that and saved the file.
Regards
Hisham
< Message edited by hhammash -- 9/7/2002 12:52:34 AM >
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: < Date Question - 9/8/2002 12:19:16
Thanks LLL,
I just tried and tried all possibilities until I found a solution.
LLL, if you can solve another problem, we can say that date matters are complete.
In the DRW the date is displayed as mm/dd/yyyy. How can we change it to dd/mm/yyyy.
I tried that from access and many other options, it did not work. I want to force it, even if the users PC is setup to mm/dd/yyyy.
Thanks LLL
Hisham
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: < Date Question - 9/8/2002 12:50:53
Hisham,
Yes, I saw your orginal post on this problem. I think I responded and said to change the date format in Access. But you responded and said that Access was OK with your date format, and that it was Frontpage that was creating the problem.
Unfortunately, in FP 2000 (the version I am using) you don' t have much say in how " Additional Field" formats are entered. It' s a " WYSIWYG" scenario. That' s the problem. You need to be able to change that format.
I don' t remember all the specifics of your original post but I' m assuming that you want the date entered automatically as a form is submitted - HENCE " Additional Fields" . One way to solve this problem would be to remove the automatic feature and have the date manually entered. Then you could set the date format to whatever you want.
Another solution to your problem would be to put a .VBScript at the top of your page (before the DRW) that takes today' s date values and assembles them into the correct format you want.
Like I did for s2nb1, I am going to play with this feature today and see if I can find something.
LLL
_____________________________
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: < Date Question - 9/8/2002 13:17:40
Hi LLL,
The possible solutions:
1- To program the date field in the Submission form Using Java to have Input mask.
2- To leave the date field without format in Access
3- The VBScript you mentioned. (which I prefer)
Thanks
Hisham
| |
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: < Date Question - 9/8/2002 14:16:26
Hisham,
You liked the VBScript idea??? OK, I' ll play with it today.
LLL
_____________________________
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: < Date Question - 9/8/2002 14:29:36
Thanks LLL
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: < Date Question - 9/8/2002 15:31:45
Hi LLL,
SOLVED
Go to your custom query in Frontpage, enter the following sql format:
SELECT Name, FORMAT (DateEntered,' dd/mm/yyyy' ) As MyDate FROM Dates
The SQL reads Select Field1, Fomat(DateField,' dd/mm/yyyy' ) From TableName.
It is working for me. Please check the URL below, which has a DRW from a Table with no query, to see the mm/dd/yyyy, and under it, you will see the same DRW but generated by the SQL above.
http://www.fmhs.uaeu.ac.ae/nml/DateFormat.asp
Regards
Hisham
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: < Date Question - 9/9/2002 0:12:53
Hi LLL,
Thanks a lot for you nice reply.
Yesterday I could not sleep before solving it.
Best regards
Hisham
| |
|
|
|
s2bn1
Posts: 192
Joined: 2/2/2002
From: Dubai United Arab Emirates
Status: offline
|
RE: RE: < Date Question - 9/9/2002 12:50:21
Thanks a lot Hisham. You are great .
s2bn1
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: < Date Question - 9/9/2002 13:10:30
Thanks s2bn1
You are welcome.
Regards
Hisham
| |
|
 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 
