SQL Statement looping

[PorkyJr]

Trying to pull a report from an access 2k database that grabs the top 5
records for each day in a range (inputed by user). Yes, I know that the
* character is not a good idea, but I put it here for brevity.

Here is my SQL Statement:

LTQry = "SELECT TOP 5 * FROM History " & _
"WHERE Calendar = '" & tmpDte & "'"

Here is my code:

Do until tmpDte > rEndDte
Set RsTstOtpt = OBJdbConn.Execute(LTQry)
Do While Not RsTstOtpt.EOF
%>
<table>
<tr>
<td>
<%=RsTstOtpt("Event") %>
</td>
<td>
<%=RsTstOtpt("CustName") %>
</td>
<td>
<%=RsTstOtpt("Calendar") %>
</td>
<td>
<%=RsTstOtpt("History") %>
</td>
</tr>
</table>
<%
RsTstOtpt.MoveNext
Loop
RsTstOtpt.Close
Set RsTstOtpt = Nothing
tmpDte = DateAdd("d",1,tmpDte)
Loop

Here is the error message:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
(in line where the SQL statement is executed)


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Ray at]

Dates are delimited with # instead of ' in Access.

Ray at work

"PorkyJr" <nospam@nospam.net> wrote in message
news:%23RGHda%23zDHA.2328@TK2MSFTNGP10.phx.gbl...

>
>
> Trying to pull a report from an access 2k database
>
> Here is my SQL Statement:
>
> LTQry = "SELECT TOP 5 * FROM History " & _
> "WHERE Calendar = '" & tmpDte & "'"
>
>
> Here is the error message:
> Microsoft JET Database Engine (0x80040E07)
> Data type mismatch in criteria expression.

[Jeff Cochran]

On Wed, 31 Dec 2003 13:39:46 -0800, PorkyJr <nospam@nospam.net> wrote:

>Trying to pull a report from an access 2k database that grabs the top 5
>records for each day in a range (inputed by user). Yes, I know that the
>* character is not a good idea, but I put it here for brevity.
>
>Here is my SQL Statement:
>
>LTQry = "SELECT TOP 5 * FROM History " & _
> "WHERE Calendar = '" & tmpDte & "'"
>
>Here is my code:
>
>Do until tmpDte > rEndDte
>Set RsTstOtpt = OBJdbConn.Execute(LTQry)
>Do While Not RsTstOtpt.EOF
>%>
><table>
><tr>
><td>
><%=RsTstOtpt("Event") %>
></td>
><td>
><%=RsTstOtpt("CustName") %>
></td>
><td>
><%=RsTstOtpt("Calendar") %>
></td>
><td>
><%=RsTstOtpt("History") %>
></td>
></tr>
></table>
><%
>RsTstOtpt.MoveNext
>Loop
>RsTstOtpt.Close
>Set RsTstOtpt = Nothing
>tmpDte = DateAdd("d",1,tmpDte)
>Loop
>
>Here is the error message:
>Microsoft JET Database Engine (0x80040E07)
>Data type mismatch in criteria expression.
>(in line where the SQL statement is executed)

First, do a Response.Write LTQry and make sure you're getting the
query as you expected. Best guess is you have a date type versus
string type issue in your tmpDte. What type does your database use?

Jeff

[PorkyJr]

Did a response.write LTQry and it is looping as expected; however it is
not inserting the value of variable tmpDte. It doesn't matter if I use
the ' or # or ().

Thoughts?


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Ray at]

Can you post your Response.Write of LTQry?

Ray at work

"PorkyJr" <nospam@nospam.net> wrote in message
news:O8MMqaU0DHA.2156@TK2MSFTNGP12.phx.gbl...

>
> Did a response.write LTQry and it is looping as expected; however it is
> not inserting the value of variable tmpDte. It doesn't matter if I use
> the ' or # or ().
>
> Thoughts?
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[PorkyJr]

Sorry, figured it out. Just had my SQL statement in the wrong place in
the code. Works like a champ when I put the SQL statement inside the
loop.



*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!