Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Looking up value from field when field is date

Status
Not open for further replies.
Spyder1000

Spyder1000

Technical User
Nov 2, 2004
109
US
I've got an access backend with a date field. I'd like to be able to limit returned results based on this field. The following is the code that i've written which works great with a text field but gives an error when used with a date.

The error:
Code: 
Microsoft JET Database Engine error '80040e07' 

Data type mismatch in criteria expression. 

/GT4Challenge/viewrecord.asp, line 22

My code:
Code: 
<HTML>
<BODY>
<br><b>Date</b></br>
<form method="POST" name="date">
<input type="text" name="date" size="16"><p>&nbsp;</p>
</p>
<input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2">
</select>
<p>&nbsp;</p>
</form>

<%
Set objConn = Server.CreateObject("ADODB.Connection")
  Set RS = Server.CreateObject("ADODB.Recordset") 
  FilePath = Server.MapPath("database.mdb")
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";" 
  objConn.Open strConn  

SQL_query = "SELECT * FROM tbl_maintable WHERE [Date] = '"&request.form("Date")&"' Order By [shippernumber]"

RS.Open SQL_query, objConn
%>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-top-width:0" bordercolor="#111111" width="80%" id="AutoNumber2">
  <tr>
    <td width="15%" bgcolor="#000080" align="center" style="border-bottom-style: solid; border-bottom-width: 1; border-top-style:none; border-top-width:medium">
    <b><font color="#FFFFFF">Account Number</font></b></td>
        <td width="35%" bgcolor="#000080" align="center" style="border-bottom-style: solid; border-bottom-width: 1; border-top-style:none; border-top-width:medium">
    <font color="#FFFFFF"><b>Tracking Number</b></font></td>
        <td width="25%" bgcolor="#000080" align="center" style="border-bottom-style: solid; border-bottom-width: 1; border-top-style:none; border-top-width:medium">
    <font color="#FFFFFF"><b>Refund Amount</b></font></td>
    <td width="25%" bgcolor="#000080" align="center" style="border-bottom-style: solid; border-bottom-width: 1; border-top-style:none; border-top-width:medium">
    <font color="#FFFFFF"><b>Status</b></font></td>
  </tr>
<%WHILE NOT RS.EOF%>
</table>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-left-width:0; border-right-width:0; border-top-width:0" bordercolor="#111111" width="80%" id="AutoNumber1">
  
<tr>
    <td width="15%" style="border-bottom-style: solid; border-bottom-width: 1; border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium" align="center"><%=RS("shippernumber")%>&nbsp;</td>
    <td width="32%" style="border-bottom-style: solid; border-bottom-width: 1; border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium" align="center"><%=RS("trackingnumber")%>&nbsp;</td>
	<td width="25%" style="border-bottom-style: solid; border-bottom-width: 1; border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium" align="center"><%=RS("refundamount")%>&nbsp;</td>
    <td width="25%" style="border-bottom-style: solid; border-bottom-width: 1; border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium" align="center"><%=RS("status")%>&nbsp;</td>
</tr>
</table>
<%
RS.MoveNext
WEND
%>
</BODY>
 
Sort by date Sort by votes
try this:
Code: 
SQL_query = "SELECT * FROM tbl_maintable WHERE [Date] = #"&request.form("Date")&"# Order By [shippernumber]"

-DNG
 
Upvote 0 Downvote
I used your advice and got it to work but I still have a problem. When the page initially loads it gives the error of:

Microsoft JET Database Engine error '80040e07'
Syntax error in date in query expression '[dateentered] = ##'.
/GT4Challenge/test/viewrecord.asp, line 21

But after I put a date in and press the submit button it works fine. Is there a way to avoid this?

Code: 
<HTML>
<BODY>
<br><b>Date</b></br>
<form method="POST" name="date">
<input type="text" name="combodate" size="16" value="02/13/1980"><p>&nbsp;</p>
</p>
<input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2">
</select>
<p>&nbsp;</p>
</form>

<%
Set objConn = Server.CreateObject("ADODB.Connection")
  Set RS = Server.CreateObject("ADODB.Recordset") 
  FilePath = Server.MapPath("database.mdb")
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";" 
  objConn.Open strConn  

SQL_query = "SELECT * FROM tbl_maintable WHERE [dateentered] = #"&request.form("combodate")&"# Order By [shippernumber]"

RS.Open SQL_query, objConn
%>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-top-width:0" bordercolor="#111111" width="80%" id="AutoNumber2">
  <tr>
    <td width="15%" bgcolor="#000080" align="center" style="border-bottom-style: solid; border-bottom-width: 1; border-top-style:none; border-top-width:medium">
    <b><font color="#FFFFFF">Account Number</font></b></td>
        <td width="35%" bgcolor="#000080" align="center" style="border-bottom-style: solid; border-bottom-width: 1; border-top-style:none; border-top-width:medium">
    <font color="#FFFFFF"><b>Tracking Number</b></font></td>
        <td width="25%" bgcolor="#000080" align="center" style="border-bottom-style: solid; border-bottom-width: 1; border-top-style:none; border-top-width:medium">
    <font color="#FFFFFF"><b>Refund Amount</b></font></td>
    <td width="25%" bgcolor="#000080" align="center" style="border-bottom-style: solid; border-bottom-width: 1; border-top-style:none; border-top-width:medium">
    <font color="#FFFFFF"><b>Status</b></font></td>
  </tr>
<%WHILE NOT RS.EOF%>
</table>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; border-left-width:0; border-right-width:0; border-top-width:0" bordercolor="#111111" width="80%" id="AutoNumber1">
  
<tr>
    <td width="15%" style="border-bottom-style: solid; border-bottom-width: 1; border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium" align="center"><%=RS("shippernumber")%>&nbsp;</td>
    <td width="32%" style="border-bottom-style: solid; border-bottom-width: 1; border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium" align="center"><%=RS("trackingnumber")%>&nbsp;</td>
	<td width="25%" style="border-bottom-style: solid; border-bottom-width: 1; border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium" align="center"><%=RS("refundamount")%>&nbsp;</td>
    <td width="25%" style="border-bottom-style: solid; border-bottom-width: 1; border-left-style:none; border-left-width:medium; border-right-style:none; border-right-width:medium; border-top-style:none; border-top-width:medium" align="center"><%=RS("status")%>&nbsp;</td>
</tr>
</table>
<%
RS.MoveNext
WEND
%>
</BODY>
</HTML>
 
Upvote 0 Downvote
what you have to do is make your date form field required field. then you dont have a problem of getting the empty string...

this can be done both at the client side(using javascript) or at the server side (using asp)...whatever you wish to...

using asp, this is how you do:

Code: 
if request.form("combodate")<>"" then
SQL_query = "SELECT * FROM tbl_maintable WHERE [dateentered] = #"&request.form("combodate")&"# Order By [shippernumber]"
else
response.write "Please enter date. Go back to the form"
end if

using javascript...
please search this forum for some previous posts and i already posted the code...

-DNG
 
Upvote 0 Downvote
Or you could make it default to today's date:

<%

TheDate=Request.Form("Date")
If TheDate="" Then TheDate=Date

SQL_query = "SELECT * FROM tbl_maintable WHERE [Date] = #"& TheDate &"# Order By [shippernumber]"

%>

One thing I have found with dates in the past is that when looking at dates in a recordset you have to re-order them to yyyy/mm/dd otherwise it does some quite strange things.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
563
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
603
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
497
penguinspeaks
penguinspeaks
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
574
JScannell
JScannell
leifoet
  • Locked
  • Question
How to display an existing date in Input type=&quot;date&quot;?
Replies
4
Views
460
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top