|
| |
|
|
rickiez
Posts: 22
Joined: 8/11/2005
Status: offline
 |
Between Dates Results Help - 2/21/2006 8:11:04
I need to make a query that will show betweend dates in access using an SQL query. I've tried something like:
where treasurecheck.timestamp between ::1:: and ::2::
I get no "no records found". I need a user to be able to enter a start and end date and I need it to show everything in that period....any thoughts?? Thanks in advance.
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 9:39:36
quote:
betweend dates in access
For that to work well (at least IMO) they need to be true Date/Time fields and the actual query in a DRW should look like this:
...between #::1::# and #::2::#
at least for Access that is.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rickiez
Posts: 22
Joined: 8/11/2005
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 9:58:31
It's automatically entered by the submission form as a timestamp as long date format. It is a date/time field.
| |
|
|
|
rickiez
Posts: 22
Joined: 8/11/2005
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 10:42:09
When I try it I get the following error:
Server error: Unable to retrieve schema information from the query:
SELECT treasurecheck.CheckNumber, treasurecheck.TellerNumber, treasurecheck.Amount FROM treasurecheck WHERE treasurecheck.TimeStamp BETWEEN #1# AND #2# ORDER BY treasurecheck.CheckNumber;
against a database using the connection string
DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/treasurecheck.mdb.
The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'treasurecheck.TimeStamp BETWEEN #1# AND #2#'.
Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 11:04:18
Can you post the full DRW or DB connection code?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rickiez
Posts: 22
Joined: 8/11/2005
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 12:22:13
<!--webbot bot="DatabaseRegionStart" s-columnnames="CheckNumber,TellerNumber,Amount" s-columntypes="202,202,202" s-dataconnection="compliance" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice="CheckNumber" s-menuvalue="CheckNumber" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns="CheckNumber,TellerNumber,Amount" s-criteria s-order s-sql="SELECT treasurecheck.CheckNumber, treasurecheck.TellerNumber, treasurecheck.Amount<br>FROM treasurecheck<br>WHERE (treasurecheck.TimeStamp Between ::PS:: AND ::PE::) AND (treasurecheck.Branch Like '::BN::')<br>ORDER BY treasurecheck.CheckNumber;<br>" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="PS=&PE=&BN=" s-norecordsfound="No records returned." i-maxrecords="256" 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" width="100%"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr>" b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="TRUE" startspan --><!--#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 treasurecheck.CheckNumber, treasurecheck.TellerNumber, treasurecheck.Amount FROM treasurecheck WHERE (treasurecheck.TimeStamp Between ::PS:: AND ::PE::) AND (treasurecheck.Branch Like '::BN::') ORDER BY treasurecheck.CheckNumber; "
fp_sDefault="PS=&PE=&BN="
fp_sNoRecords="<tr><td colspan=3 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="compliance"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="CheckNumber"
fp_sMenuValue="CheckNumber"
fp_sColTypes="&CheckNumber=202&TellerNumber=202&Amount=202&"
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="52494" --><tr>
<td><font face="Tunga" size="1" color="#5C1729">
<!--webbot bot="DatabaseResultColumn" s-columnnames="CheckNumber,TellerNumber,Amount" s-column="CheckNumber" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>CheckNumber<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"CheckNumber")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="28286" --></font></td>
<td><font face="Tunga" size="1" color="#5C1729">
<!--webbot bot="DatabaseResultColumn" s-columnnames="CheckNumber,TellerNumber,Amount" s-column="TellerNumber" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>TellerNumber<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"TellerNumber")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="30966" --></font></td>
<td><font face="Tunga" size="1" color="#5C1729">
<!--webbot bot="DatabaseResultColumn" s-columnnames="CheckNumber,TellerNumber,Amount" s-column="Amount" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>Amount<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"Amount")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="15126" --></font></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" 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="9297" -->
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 14:06:58
Are you sure PS and PE are Date/Time fields? If this is an auto-generated DRW, it looks as tho those fields are numbers and not dates.
Could you confirm this?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rickiez
Posts: 22
Joined: 8/11/2005
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 14:34:48
PS and PE come from an ASP page where you would enter the the dates in two text boxes.....is that wrong?
| |
|
|
|
rickiez
Posts: 22
Joined: 8/11/2005
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 14:40:10
It is autogenerated, but when I try and put between #::PS::# and #::PE::#; it errors out with a "Syntax error in date in query expression"
| |
|
|
|
rickiez
Posts: 22
Joined: 8/11/2005
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 15:40:40
the KB article didn't fix the problem though.
| |
|
|
|
rickiez
Posts: 22
Joined: 8/11/2005
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 15:54:38
Nah, I just get the following error:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
C:\INETPUB\WWWROOT\COMPLIANCE\TREASURECHECK\EDITOR\../../../_fpclass/fpdblib.inc, line 48
| |
|
|
|
rickiez
Posts: 22
Joined: 8/11/2005
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 15:55:20
yes i tried it, I get that error.
| |
|
|
|
rickiez
Posts: 22
Joined: 8/11/2005
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 15:55:53
I'm assuming all you wanted me to do was open it in Wordpad and replace my string w/ yours.
| |
|
|
|
rickiez
Posts: 22
Joined: 8/11/2005
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 17:09:00
I've been able to get it to work, but I have to surround the date in #'s in the text boxes on the page that submits the variables to the query. If that helps. Thanks.
| |
|
|
|
Spooky
Posts: 26606
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: Between Dates Results Help - 2/21/2006 17:54:02
It sounds like the DRW wasnt modified?
Within the grey code, you should be able to add the hashes
Another option is to use the Spooky diet and modify the SQL directly
<!--webbot bot="DatabaseRegionStart" s-columnnames="CheckNumber,TellerNumber,Amount" s-columntypes="202,202,202" s-dataconnection="compliance" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice="CheckNumber" s-menuvalue="CheckNumber" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns="CheckNumber,TellerNumber,Amount" s-criteria s-order s-sql="SELECT treasurecheck.CheckNumber, treasurecheck.TellerNumber, treasurecheck.Amount<br>FROM treasurecheck<br>WHERE (treasurecheck.TimeStamp Between #::PS::# AND #::PE::#) AND (treasurecheck.Branch Like '::BN::')<br>ORDER BY treasurecheck.CheckNumber;<br>" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="PS=&PE=&BN=" s-norecordsfound="No records returned." i-maxrecords="256" 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" width="100%"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr>" b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="TRUE" startspan --><!--#include file="../../../_fpclass/fpdblib.inc"-->
_____________________________
If you arent part of the solution, then there is good money to be made prolonging the problem
§þ k¥
| |
|
 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 
