Need help applying SQL statement to Access2000 Query

[StarfishJoe]

Need help applying SQL statement to Access2000 Query

I have created a form Named SortFilterForm for a client that filteres a table based on user selected criteria. There are up to 6 fields to select criteria, and one for sort order.

The selection criteria are optional. the Sort order is not.
All of this has been concatenated into a single SQL statement ( "Select [Project List].* from [Project list] ...
(WHERE.....AND.....AND....) (optional)
ORDER BY [(Field Name)]
The SQL query name is strSQLBuild. it is built at run time based on the user's selections on the SortFilterForm.

ASSUMING the SQL is correct;

I am not sure where to go next I was assuming I should copy the SQL statement to a QueryDef object. There is an example in Access97 concerning this, but not in Access 2000. Is this "older technology".
I am supposed to be working in Access 2000 now since the client has just upgraded all machines to 2000.

Where should I place this SQL statement so that it:
1.. creates a temporaray query based on a single Table
2.. calls an existing form (Main Form), displays the resulting recordset ( type is dynamic ) in either Form view or Datasheet view or On a report. ( if I can just make the connection, I can code to switch between form, datasheet or report view on my own.)
3.. allows the user the option to save the resulting query rather than going through the SortFilterForm's selection criteria again or to not save it.
???


StarfishJoe

 

[Graham Mandeno]

Hi Joe

Why not just create a filter string (the WHERE clause without the "WHERE")
and use it when you open your form?

The form (and also your report) can be based on your saved query without a
WHERE clause, but with an ORDER BY clause. You can then open an unbound
form on which to enter all the filter criteria, and have a button to
construct the filter string and then open the main form (or report).

If you want to wave the filters, just create a table in which to store the
values from the text/combo boxes containing the criteria, along with a "Save
as" name.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Need help applying SQL statement to Access2000 Query

I have created a form Named SortFilterForm for a client that filteres a
table based on user selected criteria. There are up to 6 fields to select
criteria, and one for sort order.

The selection criteria are optional. the Sort order is not.
All of this has been concatenated into a single SQL statement ( "Select
[Project List].* from [Project list] ...
(WHERE.....AND.....AND....) (optional)
ORDER BY [(Field Name)]
The SQL query name is strSQLBuild. it is built at run time based on the
user's selections on the SortFilterForm.

ASSUMING the SQL is correct;

I am not sure where to go next I was assuming I should copy the SQL
statement to a QueryDef object. There is an example in Access97 concerning
this, but not in Access 2000. Is this "older technology".
I am supposed to be working in Access 2000 now since the client has just
upgraded all machines to 2000.

Where should I place this SQL statement so that it:
1.. creates a temporaray query based on a single Table
2.. calls an existing form (Main Form), displays the resulting recordset
( type is dynamic ) in either Form view or Datasheet view or On a report.
( if I can just make the connection, I can code to switch between form,
datasheet or report view on my own.)
3.. allows the user the option to save the resulting query rather than
going through the SortFilterForm's selection criteria again or to not save
it.
???


StarfishJoe

 

[StarfishJoe]

Why not just create a filter string (the WHERE clause without the "WHERE")

and use it when you open your form?

The form (and also your report) can be based on your saved query without a
WHERE clause, but with an ORDER BY clause. You can then open an unbound
form on which to enter all the filter criteria, and have a button to
construct the filter string and then open the main form (or report).

I have already done this. I already have a button on my SortFilter Form
(which is unbound) to build the query and open the Main Form or a report

If dropping the "Select ....Where" clause and placing the filter string in
the Filter Property of the form is the best solution, I am all for it. Do I
place the filter in the DoCmd.OpenForm statement??

I am having difficulty putting the SQL expression or filter in the right
place in code.

This is where I placed the filter strFilter in the command button click
event .
---------------------
Private Sub cmdOpenForm_Click()
On Error GoTo Err_cmdOpenForm_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim frm As Form
Dim strFilter As String
stDocName = "Project List Form"
DoCmd.OpenForm stDocName, , strSQLFilter, stLinkCriteria
Set frm = Form![Project List Form]
strFilter = strSQLFilter
frm.Filter = strFilter
frm.FilterOn = True

Exit_cmdOpenForm_Click:
Exit Sub

Err_cmdOpenForm_Click:

Hi Joe

Why not just create a filter string (the WHERE clause without the "WHERE")
and use it when you open your form?

The form (and also your report) can be based on your saved query without a
WHERE clause, but with an ORDER BY clause. You can then open an unbound
form on which to enter all the filter criteria, and have a button to
construct the filter string and then open the main form (or report).

If you want to wave the filters, just create a table in which to store the
values from the text/combo boxes containing the criteria, along with a "Save
as" name.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Need help applying SQL statement to Access2000 Query

I have created a form Named SortFilterForm for a client that filteres a
table based on user selected criteria. There are up to 6 fields to select
criteria, and one for sort order.

The selection criteria are optional. the Sort order is not.
All of this has been concatenated into a single SQL statement ( "Select
[Project List].* from [Project list] ...
(WHERE.....AND.....AND....) (optional)
ORDER BY [(Field Name)]
The SQL query name is strSQLBuild. it is built at run time based on the
user's selections on the SortFilterForm.

ASSUMING the SQL is correct;

I am not sure where to go next I was assuming I should copy the SQL
statement to a QueryDef object. There is an example in Access97 concerning
this, but not in Access 2000. Is this "older technology".
I am supposed to be working in Access 2000 now since the client has just
upgraded all machines to 2000.

Where should I place this SQL statement so that it:
1.. creates a temporaray query based on a single Table
2.. calls an existing form (Main Form), displays the resulting recordset
( type is dynamic ) in either Form view or Datasheet view or On a report.
( if I can just make the connection, I can code to switch between form,
datasheet or report view on my own.)
3.. allows the user the option to save the resulting query rather than
going through the SortFilterForm's selection criteria again or to not save
it.
???


StarfishJoe

 

[Graham Mandeno]

Hi Joe

Your filter string should be passed as the 4th (not 3rd) argument to
DoCmd.OpenForm. This is the one named "WhereCondition" in the intellisense,
not "Filter".

So, your call should look like this:
DoCmd.OpenForm "Project List Form", , , strSQLFilter
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Why not just create a filter string (the WHERE clause without the
"WHERE")
and use it when you open your form?

The form (and also your report) can be based on your saved query without a
WHERE clause, but with an ORDER BY clause. You can then open an unbound
form on which to enter all the filter criteria, and have a button to
construct the filter string and then open the main form (or report).

I have already done this. I already have a button on my SortFilter Form
(which is unbound) to build the query and open the Main Form or a report

If dropping the "Select ....Where" clause and placing the filter string in
the Filter Property of the form is the best solution, I am all for it. Do
I
place the filter in the DoCmd.OpenForm statement??

I am having difficulty putting the SQL expression or filter in the right
place in code.

This is where I placed the filter strFilter in the command button click
event .
---------------------
Private Sub cmdOpenForm_Click()
On Error GoTo Err_cmdOpenForm_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim frm As Form
Dim strFilter As String
stDocName = "Project List Form"
DoCmd.OpenForm stDocName, , strSQLFilter, stLinkCriteria
Set frm = Form![Project List Form]
strFilter = strSQLFilter
frm.Filter = strFilter
frm.FilterOn = True

Exit_cmdOpenForm_Click:
Exit Sub

Err_cmdOpenForm_Click:

Hi Joe

Why not just create a filter string (the WHERE clause without the
"WHERE")
and use it when you open your form?

The form (and also your report) can be based on your saved query without
a
WHERE clause, but with an ORDER BY clause. You can then open an unbound
form on which to enter all the filter criteria, and have a button to
construct the filter string and then open the main form (or report).

If you want to wave the filters, just create a table in which to store
the
values from the text/combo boxes containing the criteria, along with a "Save
as" name.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Need help applying SQL statement to Access2000 Query

I have created a form Named SortFilterForm for a client that filteres a
table based on user selected criteria. There are up to 6 fields to
select
criteria, and one for sort order.

The selection criteria are optional. the Sort order is not.
All of this has been concatenated into a single SQL statement ( "Select
[Project List].* from [Project list] ...
(WHERE.....AND.....AND....) (optional)
ORDER BY [(Field Name)]
The SQL query name is strSQLBuild. it is built at run time based on the
user's selections on the SortFilterForm.

ASSUMING the SQL is correct;

I am not sure where to go next I was assuming I should copy the SQL
statement to a QueryDef object. There is an example in Access97 concerning
this, but not in Access 2000. Is this "older technology".
I am supposed to be working in Access 2000 now since the client has just
upgraded all machines to 2000.

Where should I place this SQL statement so that it:
1.. creates a temporaray query based on a single Table
2.. calls an existing form (Main Form), displays the resulting
recordset
( type is dynamic ) in either Form view or Datasheet view or On a report.
( if I can just make the connection, I can code to switch between form,
datasheet or report view on my own.)
3.. allows the user the option to save the resulting query rather than
going through the SortFilterForm's selection criteria again or to not
save
it.
???


StarfishJoe