Solved: Help! Need to search two different fields in one query.

[Dani]

I hope I'm explaining this right.

I have a list of materials, each of which have categories, and some of which have a secondary category. Each material is one line in a table, with a field for category and secondary category.

I have a form (Materials) that is tied to a filter query of this table. I have a list box on a different form that is programmed to open the Materials form filtered to the category in the drop down box. Problem is, I can't figure out how to get it to bring up any of the materials if by secondary category, using the one list box.

I need to know of a way to write the query to check BOTH the "category" field and the "secondary category" field, and return the form filtered on either of those fields, not just the one where I have placed the parameter in the query.

[CreganTur]

I just want to clarify your question- you want your query to be filtered based on a user's selection from a combobox. You are looking for results where the combobox selection shows in either the 'Category' field or the 'Secondary Category' field.

If this is correct, then you can use the 'OR' keyword in your query:
WHERE Category = "value" OR Secondary Category = "Value"

This will return all values where either option is true.

HTH:thumb

[Dani]

Yes but the combobox contains the values that are in the fields "category" and "secondary category" from the query. (I'm attaching a little imagine that'll show you what I mean). I've got the query set to bring up the form filtered to the materials which have the category in the dropdown box as their category.

But, of course, some of the materials have two categories. That's the problem-- the combobox, when selecting a category, doesn't return any materials that have the list item as a "secondary category". Of course, this is because my filter isn't set to run on that, it's set to run on "category". How do I write it so it will bring up the materials which have the item in the combobox listed under either "category" OR "secondary category"?

Would I be able to use the WHERE...OR statement in the query? If so, where?

[Dani]

It would, of course, help if I actually attached the pic!

[CreganTur]

Copy your highlighted section, move right one and down one, and then paste it there. That will setup the Or for you in Query Design view.

WHERE is the actual SQL keyword that you use to set parameters. If you want to see your query's actual SQL, click on the dropdown box where you can select your view, and select SQL view- it shows the letters "SQL".

[OBP]

Randy, I have the feeling that it should actually be an "AND" i.e. on the same line as the original Criteria, also Queries really do not like second Columns of Combos in Criteria.
It would be better to have a text Field set to "Invisible" on the form to hold the Value of the Combo's second Column and use thta in the Criteria Row

[CreganTur]

Randy, I have the feeling that it should actually be an "AND" i.e. on the same line as the original Criteria, also Queries really do not like second Columns of Combos in Criteria.
It would be better to have a text Field set to "Invisible" on the form to hold the Value of the Combo's second Column and use thta in the Criteria Row

An 'AND' would only pull records where the category and secondary category match the value of the combobox. It sounds to me like the OP wants to see all records where category has that value, regardless of secondary category; and all records where secondary category has the value, regardless of category. To me this indicates an 'OR'.

[Dani]

I'm back with another issue-- I was able to do as posted above (Thanks!!) using the OR function of the query (all the while feeling like a dummy for never having noticed that before).

Now, I've got my form. I'm trying to make it so that when I click on one option of a subform of the form, a specific record on the form opens up. I had it set to a filter query, with a macro, but that doesn't seem to work. I'm guessing it's because the value that is being filtered is not a set one, it's simply a field from a table in the subform. Hope that makes sense. Anyway, I want to be able to select a subcategory from the subform and it'll bring up the information in the form.

[CreganTur]

Now, I've got my form. I'm trying to make it so that when I click on one option of a subform of the form, a specific record on the form opens up. I had it set to a filter query, with a macro, but that doesn't seem to work. I'm guessing it's because the value that is being filtered is not a set one, it's simply a field from a table in the subform. Hope that makes sense. Anyway, I want to be able to select a subcategory from the subform and it'll bring up the information in the form.

Can you go into a little more detail on this? Also, how is the user making a selection in the subform?

[Dani]

The Category table is in a one-to-many relationship with the SubCategory table. When the user selects the Category from frmMain, I want frmCategoryManagement to open, blank, ideally, but with a subform frmSubCategory showing the SubCategories tied to that Category. From there, the user will select the appropriate SubCategory (preferrably buy clicking a button beside the txtSubCategory field), and the frmCategoryManagement will populate with the information that is in the table CategoryManagement (also in a one-to-many relationship with tblSubCategory).

[Dani]

I'm getting the error "Syntax Error (missing operator) in query expression '[txtSubCategory]=field'. I'm so new at trying to understand code, I'll copy/paste what code I'm using to try to bring up the new form. I'm just stumped:

Option Compare Database

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmCategoryDetails"

If IsNull(Me!txtSubCategory) Then
stLinkCriteria = ""
Else
stLinkCriteria = "[txtSubCategory]=" & Me![txtSubCategory]
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command6_Click:
Exit Sub
Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click
End Sub

Private Sub Form_Close()
If IsLoaded("frmDCategoryDetails") Then
DoCmd.Close acForm, "frmCategoryDetails"
End If

End Sub

Private Sub Form_Current()
Dim strCond As String
strCond = "txtSubCategory = Forms!frmCategorySubcategory!txtSubCategory"

If IsLoaded("frmCategoryDetails") Then
Forms![frmCategoryDetails].FilterOn = True
Forms![frmCategoryDetails].Filter = strCond
End If
End Sub

[CreganTur]

The WHERE condition must be a string value. Also, when you read the specifications of the parameters in Help, it also says that it must be like a SQL WHERE clause... just without the WHERE keyword.

The problem is that the function isn't detecting your object's value (Me![txtSubCategory]) as a string, because it is missing data qualifiers. In SQL (and SQL similar functions), you have to wrap VBA variables or objects with data qualifiers. These are symbols that tell SQL what data type the information is supposed to be. Strings are wrapped with single quotes (') - see my example code below. Dates are wrapped with pound signs (#), and numeric values do not have a data qualifier.

replace this line in your code with my modified code that includes data qualifiers.
stLinkCriteria = "[txtSubCategory]='" & Me![txtSubCategory] & "'"

Another issue I see is the fact that you are using a bang (!) with your Me keyword, instead of a dot (.). There is an important difference between the two. Mainly, using a Bang turns off intellisense, but a dot is intellisense compatible. If you type "Me.", you should see a list appear that shows many of the available properties, methods, and objects that can be used in conjunction with the Me keyword.

HTH:thumb

[Dani]

That did it!! Yay, thanks! And thanks for clarifying the "!" and the "."... I always wondered about that, and using the "." is much helpful!