How do you sort Queries?

  • Thread starter Thread starter MenloP3
  • Start date Start date
M

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.
 
On Tue, 14 Oct 2008 14:38:08 -0700, MenloP3

What is "Query.MaterialSearch"?

-Tom.
Microsoft Access MVP
 
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 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

John Spencer said:
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.
Click to expand...
Click to expand...
 
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
'====================================================
 
Back
Top