<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Reports, temp tables and stored procedures</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1141" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Tim:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I have apps with a lot of >= and <= type
parameters on the form which calls the report. What I have moved to is
setting up a filter in SQL in the _Open event of the report. Alternatively
I sometimes assemble the SQL statement and make that the record source of the
report.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I also give the user combo boxes to select fields
for primary, secondary, and tertiary sort which you can also set in code in the
OnOpen event of the report.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I can post some code examples if you need
them.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Rocky</FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=Tim.Pain@e20.akzonobel.com
href="mailto:Tim.Pain@e20.akzonobel.com">Pain, T. (Tim)</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Tuesday, April 22, 2003 7:23
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> [AccessD] Reports, temp tables
and stored procedures</DIV>
<DIV><BR></DIV><!-- Converted from text/rtf format -->
<P><FONT face=Arial size=2>I have a number of reports that are driven from
forms that let the user input the report parameters, the code behind the form
first clears a temporary work table, then builds and runs a query based on the
forms parameters and then in code loops through this temp table updating other
fields not populated by the first query. At the end of this it outputs a
report based on the temp work table.</FONT></P>
<P><FONT face=Arial size=2>Having recently moved the backend to SQL Server and
I think I ought to be able to re-write the above to use #temp tables and
stored procedures, but I have no real idea where to start. I reasonalby OK
writing simple stored procedures but it's the creating and initial populating
the #temp table, then looping back through it with further updates and then
using the #temp table as the basis for the report that has me
puzzled.</FONT></P>
<P><FONT face=Arial size=2>Does anybody have a example they could let me have
to get me started please?</FONT> </P>
<P><FONT face=Arial size=2>Many thanks</FONT> <BR><B><I><FONT
face="Times New Roman" color=#000080>Tim Pain</FONT></I></B> <BR><FONT
face="Times New Roman" color=#000080 size=1>Akzo Nobel Surface Chemistry
Ltd</FONT> <BR><U><FONT face="Times New Roman" color=#000080
size=1><</FONT></U><A href="MailTo:Tim.Pain@AkzoNobel.com"><U></U><U><FONT
face="Times New Roman" color=#0000ff
size=1>MailTo:Tim.Pain@AkzoNobel.com</FONT></U></A><U></U><U></U><U><FONT
face="Times New Roman" color=#000080 size=1>></FONT></U>
</P><BR><BR><BR><BR>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BODY></HTML>