Microsoft JET Database Engine (0x80040E07)

kbains · #1 (**permalink**) 12-11-02, 15:10

I am using access 2000 database and getting the following error:

Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

The quiry is:
"SELECT AffID, Date, Hits, Payment, Commission, Paid, Sum(Hits) AS HitsTotal, Sum(Payment) AS PaymentTotal, Sum(Commission) AS CommissionTotal, Sum(Paid) AS PaidTotal, Commission - Paid AS Balance from earned WHERE earned.Date Between #"& startDate &"# and #"& endDate &"# AND AffID='"& AffId &"' GROUP by AffID, Date, Hits,Payment, Commission, Paid order by Date ASC"

Both AffID and Hits are number fields; Payment, Commission and Paid are currency fields; and Date is Date/Time field. While trying both AffID and date range get posted from the forms to the page having this script but still error is there. I tried hard but in vain. I would appreciate the help in getting rid of this error. Thanks

rnealejr · #2 (**permalink**) 12-11-02, 20:56

Do a response.write of the query and copy and post. Also, paste that code as an access query (in access) - this will help you debug.

kbains · #3 (**permalink**) 12-12-02, 00:28

Quote:

Originally posted by rnealejr
Do a response.write of the query and copy and post. Also, paste that code as an access query (in access) - this will help you debug.

I would appreciate if you can tell me in detail as I was unable to understand your suggestion. Thanks for your time.

rnealejr · #4 (**permalink**) 12-12-02, 13:10

Post your asp code using this query.

kbains · #5 (**permalink**) 12-13-02, 15:40

Hello,
Here is the whole page including asp code and the query:

 (This file provides the access db connection)
<%AffID=Request.Cookies("AffID")%>
<%Name=Request.Cookies("Name")%>

<% Dim startDay, startMonth, startYear, endDay, endMonth, endYear, startDate, endDate
startDay = Request.Form("FromDay")
startMonth = Request.Form("FromMonth")
startYear = Request.Form("FromYear")

endDay = Request.Form("ToDay")
endMonth = Request.Form("ToMonth")
endYear = Request.Form("ToYear")

startDate = startMonth & "/" & startDay & "/" & startYear
endDate = endMonth & "/" & endDay & "/" & endYear
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title></title>
</head>

<body >

<table border="0" cellpadding="0" cellspacing="0" width="418" height="150">
<td height="150" valign="top" width="427">

<div align="center">
<p align="left">
<font face="helvetica, arial" size="2" color="#00007D"><b><i>    &nbs p;          </i></b></font>
<div align="center">
<center>
<table border="0" cellpadding="3" cellspacing="0" width="80%">
<tr>
<td width="100%">
<font face="helvetica, arial" size="2" color="#00007D"><b><i>Date wise  earning Stats
for affiliate account: <font color="#008080"><b><%=Name%></i></b></font>
</td>
</tr>
</table>
</center>
</div>
<center>
<table border="0" width="407" cellpadding="0">
<tr>
<td valign="top" width="401">

<div align="center">
<center>
<table border="0" cellspacing="0" cellpadding="5" width="100%">

<tr>
<td bgcolor="#AA3333"><font color="#FFFFFF" size="2"><b>Date</b></font></td>
<td bgcolor="#AA3333"><font color="#FFFFFF" size="2"><b>Click thrus</b></font></td>
<td bgcolor="#AA3333"><font color="#FFFFFF" size="2"><b>Sales</b></font></td>
<td bgcolor="#AA3333"><font color="#FFFFFF" size="2"><b>Commission</b></font></td>
<td bgcolor="#AA3333"><font color="#FFFFFF" size="2"><b>Paid</b></font></td>
<td bgcolor="#AA3333"><font color="#FFFFFF" size="2"><b>Balance</b></font></td>
</tr>

<tr>
<%
Set oConn = Rec_GetDatabaseConn()
Dim oRS, sql, AffID, Date, Hits, Payment, Commission, Paid, HitsTotal, PaymentTotal, CommissionTotal, PaidTotal, Balance, sSQL, Username

sSQL = "SELECT AffID, Date, Hits, Payment, Commission, Paid, Sum(Hits) AS HitsTotal, Sum(Payment) AS PaymentTotal, Sum(Commission) AS CommissionTotal, Sum(Paid) AS PaidTotal, Commission - Paid AS Balance from earned WHERE earned.Date Between #"& startDate &"# and #"& endDate &"# AND AffID='"& Request.Form("AffID") &"' GROUP by AffID, Date, Hits,Payment, Commission, Paid order by Date ASC"
Set oRS = oConn.Execute(sSQL)
Dim nCol
nCol = "#D5F4FF"

While Not oRS.EOF
If nCol = "#D5F4FF" Then
nCol = "#FFFFFF"
Else
nCol = "#D5F4FF"
End If

%>
<td bgcolor="<%=nCol%>"><font size="2" color="#00007D"><%=oRS("Date")%></font></td>
<td bgcolor="<%=nCol%>"><font size="2" color="#00007D"><%=oRS("Hits")%></font></td>
<td bgcolor="<%=nCol%>"><font size="2" color="#00007D">$<%=oRS("Payment")%></font></td>
<td bgcolor="<%=nCol%>"><font size="2" color="#00007D">$<%=oRS("Commission")%></font></td>
<td bgcolor="<%=nCol%>"><font size="2" color="#00007D">$<%=oRS("Paid")%></font></td>
<td bgcolor="<%=nCol%>"><font size="2" color="#00007D">$<%=oRS("Balance")%></font></td>

</tr>
<%
oRS.MoveNext
Wend
%>
</table>

</center>
</div>

<table border="0" cellpadding="3" cellspacing="0" width="234">
<tr>
<td colspan="2" width="226">

<font face="helvetica, arial" size="2" color="#00007D"><b><i>    </i></b></font>

</td>
</tr>
<tr>
<td colspan="2" width="226">

<font face="helvetica, arial" size="2" color="#00007D"><b><i>Overall stats for this period</i></b></font>

</td>
</tr>
<tr>
<td width="112"><font size="2" color="#00007D">Total
click thrus</font></td>
<td width="106"><%=oRS("HitsTotal")%></td>
</tr>
<tr>
<td width="112"><font size="2" color="#00007D">Total
Sales</font></td>
<td width="106">$<%=oRS("PaymentTotal")%></td>
</tr>
<tr>
<td width="112"><font size="2" color="#00007D">Total
commission</font></td>
<td width="106">$<%=oRS("CommissionTotal")%></td>
</tr>
<tr>
<td width="112"><font size="2" color="#00007D">Paid</font></td>
<td width="106">$<%=oRS("PaidTotal")%></td>
</tr>
<tr>
<td width="112"><font size="2">Balance</font></td>
<td width="106">$<%=oRS("CommissionTotal") - oRS("PaidTotal")%></td>
</tr>
</table>
<table border="0" width="100%" height="25">
<tr>
<td width="100%" height="21">

<font color="#aa3333" face="verdana,arial,helvetica" size="+2">
<hr noShade width="95%">

</font>

</td>
</tr>
</table>
</tr>

</table>
</div>
</td>
</table>
<center>

<p> </p>

</center>

</body>
<%

oConn.Close
Set oConn = Nothing

%>

Thanks for your help

rnealejr · #6 (**permalink**) 12-13-02, 22:33

After sSQL is set just use response.write
response.write sSQL

Examine this output and see if any problems exist - if not then copy this output to the access query block and test.

Internet Services

Web Development

Digital Marketing

Consumer Tech