<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1106" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=625171600-10092003><FONT face=Arial color=#0000ff size=2>It
isn't. You have to resolve the UDF first and then pass its result through. For
example:</FONT></SPAN></DIV>
<DIV><SPAN class=625171600-10092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=625171600-10092003><FONT face=Arial color=#0000ff size=2>Dim
strSQL as String</FONT></SPAN></DIV>
<DIV><SPAN class=625171600-10092003><FONT face=Arial color=#0000ff size=2>strSQL
= "SELECT * FROM myTables WHERE PK = " &
myFuncThatReturnsAPK()</FONT></SPAN></DIV>
<DIV><SPAN class=625171600-10092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=625171600-10092003><FONT face=Arial color=#0000ff size=2>Then
you can pass the result string. The key to making this work is resolve all the
values first.</FONT></SPAN></DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Brett
Barabash<BR><B>Sent:</B> Tuesday, September 09, 2003 1:52 PM<BR><B>To:</B>
'Access Developers discussion and problem solving'<BR><B>Subject:</B> RE:
[AccessD] Access 2000 Pass-Through Query Reports<BR><BR></FONT></DIV>
<DIV><SPAN class=949125020-09092003><FONT face=Arial color=#0000ff size=2>I'm
having the same problem. </FONT></SPAN></DIV><SPAN
class=949125020-09092003></SPAN><FONT face=Tahoma>
<DIV><SPAN class=949125020-09092003></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>S<SPAN class=949125020-09092003>ince pass-through
queries send the unparsed SQL directly to the ODBC driver, how is it possible
to use a custom VBA function in them? </SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=949125020-09092003>I'm confused.</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=949125020-09092003></SPAN></FONT></FONT></FONT><BR><FONT size=2><SPAN
class=949125020-09092003> </SPAN>-----Original
Message-----<BR><B>From:</B> KSEABOLT@parkcenter.org
[mailto:KSEABOLT@parkcenter.org]<BR><B>Sent:</B> Tuesday, September 09, 2003
12:25 PM<BR><B>To:</B> Access Developers discussion and problem
solving<BR><B>Subject:</B> RE: [AccessD] Access 2000 Pass-Through Query
Reports<BR><BR></DIV></FONT></FONT>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px"><BR><FONT face=sans-serif
size=2>OK. What am I doing wrong? When I try to set the
passthru SQL this way I get an error. "Incorrect syntax near '&'.
(#170)</FONT> <BR><BR><FONT face=sans-serif size=2>Seems like that's why we
abandoned this approach before - because all we could get to work in
passthru was a literal string containing syntactically correct T-SQL.
Is there a setting I can toggle somewhere or some other syntax I can
try?</FONT> <BR><BR><FONT face=sans-serif size=2>Thanks.</FONT>
<BR><BR><FONT face=sans-serif size=2>- Ken</FONT> <BR><BR><BR><BR><BR>
<TABLE width="100%">
<TBODY>
<TR vAlign=top>
<TD>
<TD><FONT face=sans-serif size=1><B>"Arthur Fuller"
<artful@rogers.com></B></FONT> <BR><FONT face=sans-serif
size=1>Sent by: accessd-bounces@databaseadvisors.com</FONT>
<P><FONT face=sans-serif size=1>09/09/2003 02:55 PM</FONT> <BR><FONT
face=sans-serif size=1>Please respond to Access Developers discussion
and problem solving </FONT> <BR></P>
<TD><FONT face=Arial size=1>
</FONT><BR><FONT face=sans-serif size=1>
To: "Access Developers discussion and
problem solving" <accessd@databaseadvisors.com></FONT> <BR><FONT
face=sans-serif size=1> cc:
</FONT> <BR><FONT face=sans-serif size=1>
Subject: RE: [AccessD] Access
2000 Pass-Through Query
Reports</FONT></TD></TR></TBODY></TABLE><BR><BR><BR><FONT face=Arial
color=blue size=2>My fave way to do this is to use a static function that
sets/gets the parm of interest. (God I must sound like a very squeaky wheel
on this subject :-)</FONT> <BR><FONT face="Times New Roman"
size=3> </FONT> <BR><FONT face=Arial color=blue size=2>Assuming a
get/set func that sets and gets a parm of interest, your code reduces
to:</FONT> <BR><FONT face="Times New Roman" size=3> </FONT> <BR><FONT
face=Arial color=blue size=2>EXEC "mySproc " & MyStaticValue()</FONT>
<BR><FONT face="Times New Roman" size=3> </FONT> <BR><FONT face=Arial
color=blue size=2>Here is the template for my static funcs:</FONT> <BR><FONT
face="Times New Roman" size=3> </FONT> <BR><FONT face=Arial color=blue
size=2>Static Function CurrentX(Optional lngNew As Long) As Long<BR>'Copy
this template each time you need a new set/get function<BR>'Then Replace "X"
with the name of your object, i.e.,"Employee"<BR>'Replace all in current
proc and you're done.</FONT> <BR><FONT face="Times New Roman"
size=3> </FONT> <BR><FONT face=Arial color=blue size=2>
Dim lngCurrent As Long<BR> If lngNew <> 0 Then lngCurrent
= lngNew<BR> CurrentX = lngCurrent<BR> #If conDebug
= 1 Then<BR> Debug.Print "Current X: ",
CurrentX<BR> #End If<BR>End Function</FONT> <BR><FONT
face=Tahoma size=2>-----Original Message-----<B><BR>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Brett
Barabash<B><BR>Sent:</B> Tuesday, September 09, 2003 8:52 AM<B><BR>To:</B>
'accessd@databaseadvisors.com'<B><BR>Subject:</B> [AccessD] Access 2000
Pass-Through Query Reports<BR></FONT><BR><FONT face=Arial size=2>What is the
best approach to using a parameterized SQL Server stored procedure as a
recordsource for an A2k report?</FONT> <BR><FONT face=Arial size=2>I was
thinking of basing the report on a pass-through query (containing an EXEC
statement), but it appears that I would need to modify the pass-through SQL
each time the report is run, to set the parameter values.</FONT>
<P><FONT face=Arial size=2>Brett Barabash, MCP <BR>Tappe Construction, Co.
<BR>Eagan, MN <BR>bbarabash@tappeconstruction.com <BR>(651) 256-6831 </FONT>
<P><FONT face=Arial size=2>"One thing a computer can do that most humans
can't is be sealed up in a cardboard box and sit in a warehouse."
-Jack Handey</FONT>
<P><FONT face="Times New Roman" size=3></FONT><BR><FONT
face="Times New Roman"
size=3><BR><BR>--------------------------------------------------------------------------------------------------------------------<BR>This
email and any files transmitted with it are confidential and<BR>intended
solely for the use of the individual or entity to whom<BR>they are
addressed.<BR>If you have received this email in error please notify
the<BR>originator of the message. This footer also confirms that
this<BR>email message has been scanned for the presence of computer
viruses.<BR><BR>Any views expressed in this message are those of the
individual<BR>sender, except where the sender specifies and with
authority,<BR>states them to be the views of Tappe Construction
Co.<BR><BR>Scanning of this message and addition of this footer is
performed<BR>by SurfControl E-mail Filter software in conjunction with
<BR>virus detection software.</FONT><FONT face="Courier New"
size=2>_______________________________________________<BR>AccessD mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></FONT><BR><BR></P></BLOCKQUOTE><BR><BR>--------------------------------------------------------------------------------------------------------------------<BR>This
email and any files transmitted with it are confidential and<BR>intended
solely for the use of the individual or entity to whom<BR>they are
addressed.<BR>If you have received this email in error please notify
the<BR>originator of the message. This footer also confirms that this<BR>email
message has been scanned for the presence of computer viruses.<BR><BR>Any
views expressed in this message are those of the individual<BR>sender, except
where the sender specifies and with authority,<BR>states them to be the views
of Tappe Construction Co.<BR><BR>Scanning of this message and addition of this
footer is performed<BR>by SurfControl E-mail Filter software in conjunction
with <BR>virus detection software.<BR></BLOCKQUOTE></BODY></HTML>