Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Posts
    4

    Smile Unanswered: Microsoft JET Database Engine (0x80040E07)

    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

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  3. #3
    Join Date
    Dec 2002
    Posts
    4
    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.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Post your asp code using this query.

  5. #5
    Join Date
    Dec 2002
    Posts
    4
    Hello,
    Here is the whole page including asp code and the query:

    <!--#include file="increc.asp"--> (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>&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;</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&nbsp; 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>&nbsp;&nbsp;&nbsp;&nbsp;</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>&nbsp;</p>

    </center>

    </body>
    <%

    oConn.Close
    Set oConn = Nothing

    %>

    Thanks for your help

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •