Passing parameters to a pass through query

[Franky Anzoletti]

I am making an Access application that uses a button in a form to run a pass
through query. The user will define a date range - StartDate, EndDate in the
form then press the button to run the query. The problem is: how to pass
these two parameters to the query which has a date range statement as follows:

WHERE N.INIT_DT >= '2004/07/01' (DATE, FORMAT 'YYYY/MM/DD') AND
N.INIT_DT <= '2005/03/25' (DATE, FORMAT 'YYYY/MM/DD')


Thanks in advance.

 

[Tom Wickerath]

Hi Franky,

I think the best thing to do with the ending date is to specify "less than
{entered date} + 1", since Access and SQL Server store both date and time in
a date/time field. The alternative is for the DBA to ensure that a time
component is never entered (ie. always defaults to midnight = 0, as the
decimal portion of the number).

Lets say today's date (26-Mar-2005) is entered with a time of, say 6:00 AM.
The numeric value stored in a JET table will be 38437.25. If one now runs a
query for all records between, say 15-Jan-2005 and 26-Mar-2005, they will not
retrieve the record entered at 6:00 AM today, since the query's criteria will
effectively be:

38367.0 And < 38437.0 or >= 38367.0 And <= 38437.0

depending on the inequality used in creating the criteria. For the less than
or equal to (<=) criteria on the later date, it is generally better to add
one day to the date entered and use less than (<) only without the = sign.
This way, the criteria would effectively become:

= 38367.0 And < 38438.0

which will pick up the example record entered at 6:00 AM (ie. 38437.25).

So, you would use the DateAdd function like this for a form named "frmTest",
with two textboxes named "txtStart" and "txtEnd":

=[Forms]![frmTest]![txtStart] And <DateAdd("d",1,[Forms]![frmTest]![txtEnd])

in the Criteria Cell.


Tom
________________________________________

:

I am making an Access application that uses a button in a form to run a pass
through query. The user will define a date range - StartDate, EndDate in the
form then press the button to run the query. The problem is: how to pass
these two parameters to the query which has a date range statement as follows:

WHERE N.INIT_DT >= '2004/07/01' (DATE, FORMAT 'YYYY/MM/DD') AND
N.INIT_DT <= '2005/03/25' (DATE, FORMAT 'YYYY/MM/DD')


Thanks in advance.

 

[Van T. Dinh]

Since Pass-Through Queries are proccessed by the server end, Access simply
passes the entire SQL String to the server end without pre-processing AFAIK.
This means that the Expression Service won't get involved to resolve
references and you cannot use the reference to the Form Controls as
Parameters.

You need to modify the SQL String (by VBA code) to include the StartDate and
EndDate explicitly before running the Pass-Through Query.

 

[Tom Wickerath]

Hi Van:

You are correct--I blew it big time by focusing on the original criteria
that was shown. Me.bad. Thank You for adding the correction.

Frankie:

Here is an example of modifying the SQL string using VBA code behind a form.
In this example, I have decided to remove the call to the DateAdd function
that I originally included. The only caveat is that you will not pick up
records that match the ending date entered if a time other than a default of
midnight (0) is included with the saved record.

This example uses a search form with two text boxes, which are named
txtStartDate and txtEndDate. It also assumes that you have another form,
bound to a pass through query, which is used to display the results, ie.
"frmDisplayPassThroughQueryResultsForm" is bound to
"qptNameOfYourPassThroughQuery".

A pass through query produces a read only recordset, so you will not be able
to edit the records shown. A command button named cmdRunQuery on the search
form includes the following code for it's click event procedure:

Private Sub cmdRunQuery_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qptNameOfYourPassThroughQuery")

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & "';"

' Note: As an alternative, modify the SELECT * FROM part to select just
' the fields you actually need instead of, if you can get by displaying less
fields.

DoCmd.OpenForm "frmDisplayPassThroughQueryResultsForm"

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc

End Sub


Hope this helps.

Tom
_______________________________________

:

Since Pass-Through Queries are proccessed by the server end, Access simply
passes the entire SQL String to the server end without pre-processing AFAIK.
This means that the Expression Service won't get involved to resolve
references and you cannot use the reference to the Form Controls as
Parameters.

You need to modify the SQL String (by VBA code) to include the StartDate and
EndDate explicitly before running the Pass-Through Query.

 

[Aaron]

I've got this to work for the form that I have, however I need some
assistance on some additional features. I'd like to add additional criteria,
however my syntax is not correct. I've tried multiple versions, with the
following seeming like the most sense:

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"WHERE N.TYPE = '" & Me.typebox.Value & "';"


Where type is another field in table N, and typebox is another combo box on
the form. What's wrong with this.
Also, if possible, how can I add the ability to not select a field from
combo box "type box", yet still display records.

I know how to do this in the design mode: Like "*" & [forms]! .........

Thanks

 

[Sylvain Lafontaine]

Even if the Type is another field, you must use another AND and not a second
WHERE. You can have only one WHERE clause for each Select statement (but
you can have multiple Where clauses in your query if you have multiple
Select statements, for example in an UNION query).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I've got this to work for the form that I have, however I need some
assistance on some additional features. I'd like to add additional
criteria,
however my syntax is not correct. I've tried multiple versions, with the
following seeming like the most sense:

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"WHERE N.TYPE = '" & Me.typebox.Value & "';"


Where type is another field in table N, and typebox is another combo box
on
the form. What's wrong with this.
Also, if possible, how can I add the ability to not select a field from
combo box "type box", yet still display records.

I know how to do this in the design mode: Like "*" & [forms]! .........

Thanks

Hi Van:

You are correct--I blew it big time by focusing on the original criteria
that was shown. Me.bad. Thank You for adding the correction.

Frankie:

Here is an example of modifying the SQL string using VBA code behind a
form.
In this example, I have decided to remove the call to the DateAdd
function
that I originally included. The only caveat is that you will not pick up
records that match the ending date entered if a time other than a default
of
midnight (0) is included with the saved record.

This example uses a search form with two text boxes, which are named
txtStartDate and txtEndDate. It also assumes that you have another form,
bound to a pass through query, which is used to display the results, ie.
"frmDisplayPassThroughQueryResultsForm" is bound to
"qptNameOfYourPassThroughQuery".

A pass through query produces a read only recordset, so you will not be
able
to edit the records shown. A command button named cmdRunQuery on the
search
form includes the following code for it's click event procedure:

Private Sub cmdRunQuery_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qptNameOfYourPassThroughQuery")

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & "';"

' Note: As an alternative, modify the SELECT * FROM part to select just
' the fields you actually need instead of, if you can get by displaying
less
fields.

DoCmd.OpenForm "frmDisplayPassThroughQueryResultsForm"

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc

End Sub


Hope this helps.

Tom
_______________________________________

:

Since Pass-Through Queries are proccessed by the server end, Access
simply
passes the entire SQL String to the server end without pre-processing
AFAIK.
This means that the Expression Service won't get involved to resolve
references and you cannot use the reference to the Form Controls as
Parameters.

You need to modify the SQL String (by VBA code) to include the StartDate
and
EndDate explicitly before running the Pass-Through Query.

 

[Sylvain Lafontaine]

Also, as you are using date, I'm not sure if you must enclose them with
single quote ' or with sharp #.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Even if the Type is another field, you must use another AND and not a
second WHERE. You can have only one WHERE clause for each Select
statement (but you can have multiple Where clauses in your query if you
have multiple Select statements, for example in an UNION query).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I've got this to work for the form that I have, however I need some
assistance on some additional features. I'd like to add additional
criteria,
however my syntax is not correct. I've tried multiple versions, with the
following seeming like the most sense:

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"WHERE N.TYPE = '" & Me.typebox.Value & "';"


Where type is another field in table N, and typebox is another combo box
on
the form. What's wrong with this.
Also, if possible, how can I add the ability to not select a field from
combo box "type box", yet still display records.

I know how to do this in the design mode: Like "*" & [forms]! .........

Thanks

Hi Van:

You are correct--I blew it big time by focusing on the original criteria
that was shown. Me.bad. Thank You for adding the correction.

Frankie:

Here is an example of modifying the SQL string using VBA code behind a
form.
In this example, I have decided to remove the call to the DateAdd
function
that I originally included. The only caveat is that you will not pick up
records that match the ending date entered if a time other than a
default of
midnight (0) is included with the saved record.

This example uses a search form with two text boxes, which are named
txtStartDate and txtEndDate. It also assumes that you have another form,
bound to a pass through query, which is used to display the results, ie.
"frmDisplayPassThroughQueryResultsForm" is bound to
"qptNameOfYourPassThroughQuery".

A pass through query produces a read only recordset, so you will not be
able
to edit the records shown. A command button named cmdRunQuery on the
search
form includes the following code for it's click event procedure:

Private Sub cmdRunQuery_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qptNameOfYourPassThroughQuery")

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & "';"

' Note: As an alternative, modify the SELECT * FROM part to select just
' the fields you actually need instead of, if you can get by displaying
less
fields.

DoCmd.OpenForm "frmDisplayPassThroughQueryResultsForm"

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc

End Sub


Hope this helps.

Tom
_______________________________________

:

Since Pass-Through Queries are proccessed by the server end, Access
simply
passes the entire SQL String to the server end without pre-processing
AFAIK.
This means that the Expression Service won't get involved to resolve
references and you cannot use the reference to the Form Controls as
Parameters.

You need to modify the SQL String (by VBA code) to include the StartDate
and
EndDate explicitly before running the Pass-Through Query.

 

[Aaron]

Thanks for the quick reply.

This is what I have now. There's no more error, however when I run the code,
it asks me to enter in the Type criteria, even though I selected one on the
form.

Any ideas?


qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"' AND N.TYPE = '" & Me.typebox.Value & "';"

Also, as you are using date, I'm not sure if you must enclose them with
single quote ' or with sharp #.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Even if the Type is another field, you must use another AND and not a
second WHERE. You can have only one WHERE clause for each Select
statement (but you can have multiple Where clauses in your query if you
have multiple Select statements, for example in an UNION query).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I've got this to work for the form that I have, however I need some
assistance on some additional features. I'd like to add additional
criteria,
however my syntax is not correct. I've tried multiple versions, with the
following seeming like the most sense:

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"WHERE N.TYPE = '" & Me.typebox.Value & "';"


Where type is another field in table N, and typebox is another combo box
on
the form. What's wrong with this.
Also, if possible, how can I add the ability to not select a field from
combo box "type box", yet still display records.

I know how to do this in the design mode: Like "*" & [forms]! .........

Thanks




:

Hi Van:

You are correct--I blew it big time by focusing on the original criteria
that was shown. Me.bad. Thank You for adding the correction.

Frankie:

Here is an example of modifying the SQL string using VBA code behind a
form.
In this example, I have decided to remove the call to the DateAdd
function
that I originally included. The only caveat is that you will not pick up
records that match the ending date entered if a time other than a
default of
midnight (0) is included with the saved record.

This example uses a search form with two text boxes, which are named
txtStartDate and txtEndDate. It also assumes that you have another form,
bound to a pass through query, which is used to display the results, ie.
"frmDisplayPassThroughQueryResultsForm" is bound to
"qptNameOfYourPassThroughQuery".

A pass through query produces a read only recordset, so you will not be
able
to edit the records shown. A command button named cmdRunQuery on the
search
form includes the following code for it's click event procedure:

Private Sub cmdRunQuery_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qptNameOfYourPassThroughQuery")

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & "';"

' Note: As an alternative, modify the SELECT * FROM part to select just
' the fields you actually need instead of, if you can get by displaying
less
fields.

DoCmd.OpenForm "frmDisplayPassThroughQueryResultsForm"

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc

End Sub


Hope this helps.

Tom
_______________________________________

:

Since Pass-Through Queries are proccessed by the server end, Access
simply
passes the entire SQL String to the server end without pre-processing
AFAIK.
This means that the Expression Service won't get involved to resolve
references and you cannot use the reference to the Form Controls as
Parameters.

You need to modify the SQL String (by VBA code) to include the StartDate
and
EndDate explicitly before running the Pass-Through Query.

 

[Sylvain Lafontaine]

In what exact context are you using this passthrough query to SQL-Server?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thanks for the quick reply.

This is what I have now. There's no more error, however when I run the
code,
it asks me to enter in the Type criteria, even though I selected one on
the
form.

Any ideas?


qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"' AND N.TYPE = '" & Me.typebox.Value & "';"

Also, as you are using date, I'm not sure if you must enclose them with
single quote ' or with sharp #.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Even if the Type is another field, you must use another AND and not a
second WHERE. You can have only one WHERE clause for each Select
statement (but you can have multiple Where clauses in your query if
you
have multiple Select statements, for example in an UNION query).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I've got this to work for the form that I have, however I need some
assistance on some additional features. I'd like to add additional
criteria,
however my syntax is not correct. I've tried multiple versions, with
the
following seeming like the most sense:

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"WHERE N.TYPE = '" & Me.typebox.Value & "';"


Where type is another field in table N, and typebox is another combo
box
on
the form. What's wrong with this.
Also, if possible, how can I add the ability to not select a field
from
combo box "type box", yet still display records.

I know how to do this in the design mode: Like "*" & [forms]!
.........

Thanks




:

Hi Van:

You are correct--I blew it big time by focusing on the original
criteria
that was shown. Me.bad. Thank You for adding the correction.

Frankie:

Here is an example of modifying the SQL string using VBA code behind
a
form.
In this example, I have decided to remove the call to the DateAdd
function
that I originally included. The only caveat is that you will not pick
up
records that match the ending date entered if a time other than a
default of
midnight (0) is included with the saved record.

This example uses a search form with two text boxes, which are named
txtStartDate and txtEndDate. It also assumes that you have another
form,
bound to a pass through query, which is used to display the results,
ie.
"frmDisplayPassThroughQueryResultsForm" is bound to
"qptNameOfYourPassThroughQuery".

A pass through query produces a read only recordset, so you will not
be
able
to edit the records shown. A command button named cmdRunQuery on the
search
form includes the following code for it's click event procedure:

Private Sub cmdRunQuery_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qptNameOfYourPassThroughQuery")

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & "';"

' Note: As an alternative, modify the SELECT * FROM part to select
just
' the fields you actually need instead of, if you can get by
displaying
less
fields.

DoCmd.OpenForm "frmDisplayPassThroughQueryResultsForm"

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc

End Sub


Hope this helps.

Tom
_______________________________________

:

Since Pass-Through Queries are proccessed by the server end, Access
simply
passes the entire SQL String to the server end without pre-processing
AFAIK.
This means that the Expression Service won't get involved to resolve
references and you cannot use the reference to the Form Controls as
Parameters.

You need to modify the SQL String (by VBA code) to include the
StartDate
and
EndDate explicitly before running the Pass-Through Query.

 

[Douglas J. Steele]

Type might be a reserved word. Either change the name of that field, or try

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"' AND N.[TYPE] = '" & Me.typebox.Value & "';"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the quick reply.

This is what I have now. There's no more error, however when I run the
code,
it asks me to enter in the Type criteria, even though I selected one on
the
form.

Any ideas?


qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"' AND N.TYPE = '" & Me.typebox.Value & "';"

Also, as you are using date, I'm not sure if you must enclose them with
single quote ' or with sharp #.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Even if the Type is another field, you must use another AND and not a
second WHERE. You can have only one WHERE clause for each Select
statement (but you can have multiple Where clauses in your query if
you
have multiple Select statements, for example in an UNION query).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I've got this to work for the form that I have, however I need some
assistance on some additional features. I'd like to add additional
criteria,
however my syntax is not correct. I've tried multiple versions, with
the
following seeming like the most sense:

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"WHERE N.TYPE = '" & Me.typebox.Value & "';"


Where type is another field in table N, and typebox is another combo
box
on
the form. What's wrong with this.
Also, if possible, how can I add the ability to not select a field
from
combo box "type box", yet still display records.

I know how to do this in the design mode: Like "*" & [forms]!
.........

Thanks




:

Hi Van:

You are correct--I blew it big time by focusing on the original
criteria
that was shown. Me.bad. Thank You for adding the correction.

Frankie:

Here is an example of modifying the SQL string using VBA code behind
a
form.
In this example, I have decided to remove the call to the DateAdd
function
that I originally included. The only caveat is that you will not pick
up
records that match the ending date entered if a time other than a
default of
midnight (0) is included with the saved record.

This example uses a search form with two text boxes, which are named
txtStartDate and txtEndDate. It also assumes that you have another
form,
bound to a pass through query, which is used to display the results,
ie.
"frmDisplayPassThroughQueryResultsForm" is bound to
"qptNameOfYourPassThroughQuery".

A pass through query produces a read only recordset, so you will not
be
able
to edit the records shown. A command button named cmdRunQuery on the
search
form includes the following code for it's click event procedure:

Private Sub cmdRunQuery_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qptNameOfYourPassThroughQuery")

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & "';"

' Note: As an alternative, modify the SELECT * FROM part to select
just
' the fields you actually need instead of, if you can get by
displaying
less
fields.

DoCmd.OpenForm "frmDisplayPassThroughQueryResultsForm"

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc

End Sub


Hope this helps.

Tom
_______________________________________

:

Since Pass-Through Queries are proccessed by the server end, Access
simply
passes the entire SQL String to the server end without pre-processing
AFAIK.
This means that the Expression Service won't get involved to resolve
references and you cannot use the reference to the Form Controls as
Parameters.

You need to modify the SQL String (by VBA code) to include the
StartDate
and
EndDate explicitly before running the Pass-Through Query.

 

[Aaron]

I'm using it in the same context as the originator of this post. A form that
allows the user to select a range for dates, and it displays a form bound to
a query.
In addition I have some other combo boxes included in the form.

Doug: As far as your suggestion, thanks for the info, but I'm not actually
using Type in my program, that was just what I wrote for the example of my
code.

Type might be a reserved word. Either change the name of that field, or try

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"' AND N.[TYPE] = '" & Me.typebox.Value & "';"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the quick reply.

This is what I have now. There's no more error, however when I run the
code,
it asks me to enter in the Type criteria, even though I selected one on
the
form.

Any ideas?


qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"' AND N.TYPE = '" & Me.typebox.Value & "';"

Also, as you are using date, I'm not sure if you must enclose them with
single quote ' or with sharp #.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message Even if the Type is another field, you must use another AND and not a
second WHERE. You can have only one WHERE clause for each Select
statement (but you can have multiple Where clauses in your query if
you
have multiple Select statements, for example in an UNION query).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I've got this to work for the form that I have, however I need some
assistance on some additional features. I'd like to add additional
criteria,
however my syntax is not correct. I've tried multiple versions, with
the
following seeming like the most sense:

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"WHERE N.TYPE = '" & Me.typebox.Value & "';"


Where type is another field in table N, and typebox is another combo
box
on
the form. What's wrong with this.
Also, if possible, how can I add the ability to not select a field
from
combo box "type box", yet still display records.

I know how to do this in the design mode: Like "*" & [forms]!
.........

Thanks




:

Hi Van:

You are correct--I blew it big time by focusing on the original
criteria
that was shown. Me.bad. Thank You for adding the correction.

Frankie:

Here is an example of modifying the SQL string using VBA code behind
a
form.
In this example, I have decided to remove the call to the DateAdd
function
that I originally included. The only caveat is that you will not pick
up
records that match the ending date entered if a time other than a
default of
midnight (0) is included with the saved record.

This example uses a search form with two text boxes, which are named
txtStartDate and txtEndDate. It also assumes that you have another
form,
bound to a pass through query, which is used to display the results,
ie.
"frmDisplayPassThroughQueryResultsForm" is bound to
"qptNameOfYourPassThroughQuery".

A pass through query produces a read only recordset, so you will not
be
able
to edit the records shown. A command button named cmdRunQuery on the
search
form includes the following code for it's click event procedure:

Private Sub cmdRunQuery_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qptNameOfYourPassThroughQuery")

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & "';"

' Note: As an alternative, modify the SELECT * FROM part to select
just
' the fields you actually need instead of, if you can get by
displaying
less
fields.

DoCmd.OpenForm "frmDisplayPassThroughQueryResultsForm"

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc

End Sub


Hope this helps.

Tom
_______________________________________

:

Since Pass-Through Queries are proccessed by the server end, Access
simply
passes the entire SQL String to the server end without pre-processing
AFAIK.
This means that the Expression Service won't get involved to resolve
references and you cannot use the reference to the Form Controls as
Parameters.

You need to modify the SQL String (by VBA code) to include the
StartDate
and
EndDate explicitly before running the Pass-Through Query.

 

[Sylvain Lafontaine]

When you say that it displays a form bound to a query, are you using
directly the query as the source of the form or if you are first creating a
DAO recordset and then set this recordset as the source of the form?

Are you sure that the parameters for the query have been set before the form
opens?

You should grab the value of the SQL string and try it directly in the Query
Analyser of SQL-Server (and also show it to us).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I'm using it in the same context as the originator of this post. A form
that
allows the user to select a range for dates, and it displays a form bound
to
a query.
In addition I have some other combo boxes included in the form.

Doug: As far as your suggestion, thanks for the info, but I'm not actually
using Type in my program, that was just what I wrote for the example of my
code.

Type might be a reserved word. Either change the name of that field, or
try

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"' AND N.[TYPE] = '" & Me.typebox.Value & "';"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the quick reply.

This is what I have now. There's no more error, however when I run the
code,
it asks me to enter in the Type criteria, even though I selected one on
the
form.

Any ideas?


qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"' AND N.TYPE = '" & Me.typebox.Value & "';"






:

Also, as you are using date, I'm not sure if you must enclose them
with
single quote ' or with sharp #.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)>
wrote in message Even if the Type is another field, you must use another AND and not
a
second WHERE. You can have only one WHERE clause for each Select
statement (but you can have multiple Where clauses in your query if
you
have multiple Select statements, for example in an UNION query).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I've got this to work for the form that I have, however I need some
assistance on some additional features. I'd like to add additional
criteria,
however my syntax is not correct. I've tried multiple versions,
with
the
following seeming like the most sense:

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & _
"WHERE N.TYPE = '" & Me.typebox.Value & "';"


Where type is another field in table N, and typebox is another
combo
box
on
the form. What's wrong with this.
Also, if possible, how can I add the ability to not select a field
from
combo box "type box", yet still display records.

I know how to do this in the design mode: Like "*" & [forms]!
.........

Thanks




:

Hi Van:

You are correct--I blew it big time by focusing on the original
criteria
that was shown. Me.bad. Thank You for adding the correction.

Frankie:

Here is an example of modifying the SQL string using VBA code
behind
a
form.
In this example, I have decided to remove the call to the DateAdd
function
that I originally included. The only caveat is that you will not
pick
up
records that match the ending date entered if a time other than a
default of
midnight (0) is included with the saved record.

This example uses a search form with two text boxes, which are
named
txtStartDate and txtEndDate. It also assumes that you have another
form,
bound to a pass through query, which is used to display the
results,
ie.
"frmDisplayPassThroughQueryResultsForm" is bound to
"qptNameOfYourPassThroughQuery".

A pass through query produces a read only recordset, so you will
not
be
able
to edit the records shown. A command button named cmdRunQuery on
the
search
form includes the following code for it's click event procedure:

Private Sub cmdRunQuery_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qptNameOfYourPassThroughQuery")

qdf.SQL = "SELECT * FROM MyTable N " & _
"WHERE N.INIT_DT >= '" & Me.txtStartDate.Value & _
"' AND N.INIT_DT <= '" & Me.txtEndDate.Value & "';"

' Note: As an alternative, modify the SELECT * FROM part to select
just
' the fields you actually need instead of, if you can get by
displaying
less
fields.

DoCmd.OpenForm "frmDisplayPassThroughQueryResultsForm"

ExitProc: 'Clean up
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdRunQuery_Click..."
Resume ExitProc

End Sub


Hope this helps.

Tom
_______________________________________

:

Since Pass-Through Queries are proccessed by the server end,
Access
simply
passes the entire SQL String to the server end without
pre-processing
AFAIK.
This means that the Expression Service won't get involved to
resolve
references and you cannot use the reference to the Form Controls
as
Parameters.

You need to modify the SQL String (by VBA code) to include the
StartDate
and
EndDate explicitly before running the Pass-Through Query.

 

[wnfisba]

Aaron,

I hope I can contact you regarding this.

I need to create an Access Pass-Through SQL Query which prompts the user for
a beginning and end date. It looks and sounds and reads like you have
mastered this. Could you please reply back if you have?

Thanks.

wnfisba