Code for Criteria in a Pass Through Query

[WildlyHarry]

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.

 

[Rick Brandt]

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.

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

 

[WildlyHarry]

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
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.

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

 

[WildlyHarry]

I figured it out thanks

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.

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