how to filter one field with 2 combo box's in a query

[sspreyer]

hi ,
all

I have 2 unbound combo box's on a form 1 called cboclient1
and the another called cboclient2. would like to be able to filter field
"client name" twice by cboclient1 and cboclient2 so if I put client name in cboclient1 and in cboclient2 it will filter my query with these client name's also would like to be able to filter by one client name to e.g. if I leave cboclient2 empty and fill cboclient1 show only client names in query if I leave both empty show all clients here what I have but my knowledge is not great to very little

=[Forms]![report gen]![cboclient1] or [Forms]![report gen]![cboclient1] is null

this work perfectly until I try and add cboclient2

=[Forms]![report gen]![cboclient1] or [Forms]![report gen]![cboclient1] is null AND [Forms]![report gen]![cboclient2] or [Forms]![report gen]![cboclient2]

This doesn't work a tall

here my sql view if this help

WHERE (((Assets.Client)=[Forms]![report gen]![Cboclient1] Or [Forms]![report gen]![cboclient1] Is Null));

any help much appreciated



shane

[ItsMe]

Maybe some VBA in a form will work best.

[sspreyer]

Maybe some VBA in a form will work best.

Hi
itme

could you give me of sample of code doing this operation
as my VBA knowledge is little

thanks again !!

shane

[orange]

What exactly are you trying to do --plain English?

Are you trying to determine which Report to open?

[ItsMe]

THis is untested, but it is very similar to what I sometimes do. Normally, I would not be using combos on a bound form. I would have a subform dependent on the combo and the combo placed on an unbound form.



I would declare a couple of string variables in the header of the form.

Dim strSQL as string
Dim strWhere as string
Dim strCombo1 as string
Dim strCombo2 as string

Placing this at the very top of the module will cause the variables to be available everywhere in the form.


In the forms load event I would place the following code. This represents the primary key field name for a table "FieldPK > 0. The idea is to start your strWhere with something that will always return all records.

strCombo1 = ""
strCombo2 = ""
strWhere = "FieldPK > 0"
strSQL = "SELECT MyQry.* " & _
             "FROM MyQry " & _
             "WHERE " & strWhere & _
             "ORDER BY [FieldPK];"
Me.RecordSource = strSQL

With this we can add to the strWhere using afterupdate events for the combos. and reasign the form's recordset.

In one combo afterupdate you could use something like this

'Reset strWhere
strWhere = "FieldPK > 0"
 
strCombo1 = "[SomeField] = " & Me.Combo1
strWhere = (strWhere & " AND " & strCombo2 & " AND ") & strCombo1
strSQL = "SELECT MyQry.* " & _
             "FROM MyQry " & _
             "WHERE " & strWhere & _
             "ORDER BY [FieldPK];"
Me.RecordSource = strSQL

Then in the other combo afterupdate you want to switch the order of the variables for the combos.

'Reset strWhere 
strWhere = "FieldPK > 0"
 
'strCombo2 = "[SomeField] = '" & Me.Combo2 & "'" Text version

strCombo2 = "[SomeField] = " & Me.Combo2 'Number version

Me.Combo2
strWhere = (strWhere & " AND " & strCombo1 & " AND ") & strCombo2
strSQL = "SELECT MyQry.* " & _
             "FROM MyQry " & _
             "WHERE " & strWhere & _
             "ORDER BY [FieldPK];"
Me.RecordSource = strSQL

If your combos are text value then the code would have to change a little. We would have to concatenate StrCombo a little differently. If the combo values are numeric we could use maybe a long integer data type variable instead of string.

[sspreyer]

hi sorry orange if I haven't explain it very clearly

I want to update query but not open a report

I have 2 combo box's one called cboclient1 and other called cboclient2 on a form call query gen and my query is call query gen. I want to be able to filter the same field twice Field called "client" which is actually client name

e.g. 1) if cboclient1 is filled with a client name and cboclient2 is filled with a client name update query to show records of these client names

2) If cboclient1 is filled with a client name and cboclient2 is not filled update query showing record of client name from cboclient1 only

3) if cboclient1 is empty and cboclient2 is empty show all records on update query


hope this explain a little better
thanks for you time and patient

shane

[June7]

If you want to search 1 field with multiple criteria, need to use VBA to build a criteria string. That criteria string can use OR operator.

"[fieldname]='" & Me.cboClient1 & "' OR [fieldname]='" & Me.cboClient2 & "'"

Review

http://allenbrowne.com/ser-50.html

http://allenbrowne.com/ser-62code.html

[ItsMe]

Definitely the OR operator is applicable. I was trying to incorporate some code from a search form in an inappropriate way.

I am also curious what the end goal is. Create a query to open a report? Filter a form?

[orange]

hi sorry orange if I haven't explain it very clearly

I want to update query but not open a report

I have 2 combo box's one called cboclient1 and other called cboclient2 on a form call query gen and my query is call query gen. I want to be able to filter the same field twice Field called "client" which is actually client name

e.g. 1) if cboclient1 is filled with a client name and cboclient2 is filled with a client name update query to show records of these client names

2) If cboclient1 is filled with a client name and cboclient2 is not filled update query showing record of client name from cboclient1 only

3) if cboclient1 is empty and cboclient2 is empty show all records on update query


hope this explain a little better
thanks for you time and patient

shane

I do not understand WHAT you are trying to do. When you say update query that has a specific meaning in database terms.

You mention client name in combo1 and client name in combo2??? Is this the same client name?
Please try to tell us about your business -- what is the data base for?