How do you sort Queries?

[MenloP3]

Hello,

I have a combo box that contains two material categories, Metal and Polymer.
Based on either selection, I want Query.MaterialSearch to update itself
after the user clicks on a button. However, I seem to keep getting errors
with trying to address "Query.MaterialSearch.RecordSource = strNewSQL". When
I run the filter, it seems like Access just skips the Select Case portion and
goes to the bottom.

*************
Const cstrOldSQL = "SELECT MaterialName, MaterialCategory, Supplier FROM
Query.MaterialSearch"

Dim strNewSQL As String ' variable to hold the new SQL string for the
RecordSource

comboMaterial.SetFocus

Select Case comboMaterial!optSortBy

Case Is = 1
' Filter for 'Metal' returns 1
strNewSQL = cstrOldSQL & " WHERE MaterialCategory = '1';"
Case Is = 2
' Filter for 'Polymer' returns 2
strNewSQL = cstrOldSQL & " WHERE MaterialCategory = '2';"
Case Else
' Handle the instance where an unexpected value is returned
strNewSQL = cstrOldSQL & ";"
End Select

Query.MaterialSearch.RecordSource = strNewSQL
Query.MaterialSearch.Requery
*************

Would anyone happen to have any pointers for me to correct this problem?
Any help would be grately appreciated.

Thank you for your time.

 

[Tom van Stiphout]

On Tue, 14 Oct 2008 14:38:08 -0700, MenloP3

What is "Query.MaterialSearch"?

-Tom.
Microsoft Access MVP

 

[John Spencer]

Const cstrOldSQL = "SELECT MaterialName, MaterialCategory, Supplier FROM
Query.MaterialSearch"

That line looks suspicious. I would expect something more like the following
where the name of a table or query is MaterialSearch.

Const cstrOldSQL = "SELECT MaterialName, MaterialCategory, Supplier FROM
MaterialSearch"

Also What is Query.MaterialSearch?

What is ComboMaterial!optSortBy? Are you trying to refer to the combobox?

Normally that would be either
Me.ComboMaterial
or
Forms![Your Form Name]!ComboMaterial


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[MenloP3]

Hello John,

Const cstrOldSQL = "SELECT MaterialName, MaterialCategory, Supplier FROM
Query.MaterialSearch"

'This statement stores 3 fields of information from the query called
"MaterialSearch" into a string. MaterialSearch is a query formed by
searching through a materials database that has a number of properties
associated with each material.


*********************
ComboMaterial!optSortBy

'ComboMaterial is a combo box that contains either "Metal" or "Plastic" as a
selection. Once a user selects one option, I would like it to filter the
MaterialSearch query further. I cannot use Me.ComboMaterial because the
combo box is in a form

***********************

I can explain more if you'd like. Any help would be greatly appreciated.

Thank you,
Menlo

Const cstrOldSQL = "SELECT MaterialName, MaterialCategory, Supplier FROM
Query.MaterialSearch"

That line looks suspicious. I would expect something more like the following
where the name of a table or query is MaterialSearch.

Const cstrOldSQL = "SELECT MaterialName, MaterialCategory, Supplier FROM
MaterialSearch"

Also What is Query.MaterialSearch?

What is ComboMaterial!optSortBy? Are you trying to refer to the combobox?

Normally that would be either
Me.ComboMaterial
or
Forms![Your Form Name]!ComboMaterial


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hello,

I have a combo box that contains two material categories, Metal and Polymer.
Based on either selection, I want Query.MaterialSearch to update itself
after the user clicks on a button. However, I seem to keep getting errors
with trying to address "Query.MaterialSearch.RecordSource = strNewSQL". When
I run the filter, it seems like Access just skips the Select Case portion and
goes to the bottom.

*************
Const cstrOldSQL = "SELECT MaterialName, MaterialCategory, Supplier FROM
Query.MaterialSearch"

Dim strNewSQL As String ' variable to hold the new SQL string for the
RecordSource

comboMaterial.SetFocus

Select Case comboMaterial!optSortBy

Case Is = 1
' Filter for 'Metal' returns 1
strNewSQL = cstrOldSQL & " WHERE MaterialCategory = '1';"
Case Is = 2
' Filter for 'Polymer' returns 2
strNewSQL = cstrOldSQL & " WHERE MaterialCategory = '2';"
Case Else
' Handle the instance where an unexpected value is returned
strNewSQL = cstrOldSQL & ";"
End Select

Query.MaterialSearch.RecordSource = strNewSQL
Query.MaterialSearch.Requery
*************

Would anyone happen to have any pointers for me to correct this problem?
Any help would be grately appreciated.

Thank you for your time.

 

[John Spencer]

So you have a query named MaterialSearch. IF that is so, your Const
cstrOldSQL should be defined as

Const cstrOldSQL = "SELECT MaterialName, MaterialCategory, Supplier FROM
MaterialSearch"

Note that "Query." has been dropped

If combomaterial is a combobox that contains Metal or Plastic then why
are you testing for 1 or 2 in your Case statement

It should read more like the following

'Assumption MaterialCategory is a
'TEXT field that contains number characters
'If it is a number field then drop the '
'from the where clause
Select Case Forms!NameOfForm!ComboMaterial
Case "Metal"
strNewSQL = " WHERE MaterialCategory = '1'"
Case "Plastic"
strNewSQL = " WHERE MaterialCategory = '2'"
Case Else
strNewSQL = ""
End Select

Perhaps instead of telling us what your code is, you can tell us what
you are attempting to do and why. There may be a better solution.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================