Code for Criteria in a Pass Through Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

WildlyHarry - 26 Oct 2007 14:43 GMT

I have read all of the posts on this forum about Pass Through queries and I
just do not get it. Does anyone have any sample code of how they used data
on a form as criteria in a Pass Through query. Particularly for date ranges.
I have a Pass Through query that looks like this.

select [detail].[sd_term_cntry], [detail].[sd_datekey],
[detail].[md_tran_amt1], [detail].[sd_key], [detail].[sd_resp_cde],
[detail].[sd_mbr_num]

from [detail] (nolock)

where ((([detail].[sd_term_cntry]) <> '999')
and (([detail].[sd_term_cntry]) <> ' ')
and (([detail].[sd_mbr_num])='cus'))
and (([detail].[sd_datekey]) between '20071001 00:00:00.000' and '20071001
23:59:59.999' );

I want the user to be able to enter a date range on a form that will be the
criteria in for [detail].[sd_datekey] and I cannot for the life of me figure
out how to do this. Why would access prevent you from entering criteria this
way in a Pass Through query, seems like it should be SOP? Thanks in advance
for you help.

Reply to this Message

Rick Brandt - 27 Oct 2007 00:51 GMT

> I have read all of the posts on this forum about Pass Through queries
> and I just do not get it. Does anyone have any sample code of how
[quoted text clipped - 19 lines]
> from entering criteria this way in a Pass Through query, seems like
> it should be SOP? Thanks in advance for you help.

Passthrough means that the SQL (completely unchanged) is sent to the server for
processing. Your server query engine has no idea about your form or your local
client application at all. As far as it is concerned that SQL could have come
from a Java program, a VB application, a web application, etc.. All it knows to
do is to run the SQL and send the results back on the same connection.

To do what you want you have to re-write the entire SQL of the passthrough query
substituting the *values* on the form for the form references that you would use
locally. Here is an example...

Dim SQLString as String

SQLString = "select [detail].[sd_term_cntry], " & _
"[detail].[sd_datekey], [detail].[md_tran_amt1], " & _
"[detail].[sd_key], [detail].[sd_resp_cde], " & _
"[detail].[sd_mbr_num] " & _
"from [detail] (nolock) " & _
"where ((([detail].[sd_term_cntry]) <> '999') " & _
"and (([detail].[sd_term_cntry]) <> ' ') " & _
"and (([detail].[sd_mbr_num])='cus')) " & _
"and (([detail].[sd_datekey]) between '" & _
Forms!FormName!ControlName1 & "' and '" & _
Forms!FormName!ControlName2 & "'"

CurrentDB.QueryDefs("QueryName").SQL = SQLString

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

WildlyHarry - 31 Oct 2007 14:24 GMT

Thanks for the response sorry it took me so long to get back to you. I took
the code you had below and created a new module (?) with the correct form
names. However, when I compile the code I get a Compile Error: Invalid
outside procedure on the first line

SQLString = "select [detail].[sd_term_cntry], " & _

Any suggestions?

> > I have read all of the posts on this forum about Pass Through queries
> > and I just do not get it. Does anyone have any sample code of how
[quoted text clipped - 45 lines]
>
> CurrentDB.QueryDefs("QueryName").SQL = SQLString

Reply to this Message

WildlyHarry - 31 Oct 2007 19:07 GMT

I figured it out thanks

Reply to this Message