Making a search whit different criterias

[Larshg]

I hae a form where there are some combo's and a command button the combos should be different search citeria's for a table that should contain the result of this search.

I was thinking of making a query where the criterias are the combo's but the combo are not alwayes filled and then it should disregard the criteria

 

[hennep]

You can do this using a query:

select * from table1 where field1 = forms.form1.combo1.column(0) or forms.form1.combo1.column(0) is null

 

[scking]

Just a Sample.

Steve King

Dim strSQL As String

strSQL = "SELECT * WHERE"
If Len(Me.cboCombo1)>0 Then
strSQL = strSQL & " field1 = " & Me.cboCombo1
End If
If Len(Me.cboCombo1)>0
If Len(Me.cboCombo2)>0 Then
strSQL = strSQL & " " & Me.cboCombo2
End If
End If
strSQL = strSQL & ";"


Professional growth follows a healthy professional curiosity

 

[Larshg]

Well I tryed the frist responce and that works fine however the problem comes when ther are more then one criteria the I have to select all the combo's this is because my SQL Where sentence does not tage in to considerations all the possibilities, are there any sql commend that does?

the sql sentence for to diferent posibilityes is this
WHERE (((Report.ID)=[Forms]![View/Modify]![IDcombo]) AND ((Report.AddedBy)=[Forms]![View/Modify]![SupporterCB])) OR ((([Forms]![View/Modify]![SupporterCB]) Is Null) AND (([Forms]![View/Modify]![IDcombo]) Is Null));

The problem whit this is that as you see there are only two different possibilities where I need four. And If I make thies my SQL sentence will become incredible large. Besides I have i all about 15 different search criterias.

 

[magicrat]

Unless I'm missing something (quite possible), I think you just need to switch your AND's/OR's around:


WHERE (((Report.ID)=[Forms]![View/Modify]![IDcombo] OR ([Forms]![View/Modify]![IDcombo]) Is Null) AND ((Report.AddedBy)=[Forms]![View/Modify]![SupporterCB] OR ([Forms]![View/Modify]![SupporterCB]) Is Null) AND
...repeat for all 15 fields

I am assuming that values are entered in more than 1 field, you want the selection criteria AND'ed together.
Hope it helps

 

[Larshg]

Well it help but now I can't seem to entermore then one criteria. I would like to use some thing like or/and but I cant seem to find any sql statements that work like that

 

[magicrat]

I believe that Access has a like operator, but I'm not certain of the syntax. In any case I assume you are saying you can't enter more than 1 criteria per field. If so, progrma your combo boxes to return a string of values such as [IDcombo] = 'abc,def,xyz' where abc, def and xyz are the criteria you want to search for.
Then change the query language above to:
((Report.ID)In([Forms]![View/Modify]![IDcombo]) OR ([Forms]![View/Modify]![IDcombo]) Is Null)

This should evaluate to:
((Report.ID)In(abc,def,xyz)

The syntax of building the criteria will be a little tricky for Char fields because you have to surround each value with quotes, but there are lots of threads out here to help with that.
Good Luck

 

[Larshg]

No I don't want to have different criteria i one field, It is different criterias i different fields. But that is not possible either. because of the or command witch I want to be a and/or command.

ex.
WHERE (((Report.ID)=[Forms]![View/Modify]![IDcombo] OR/AND ([Forms]![View/Modify]![IDcombo]) Is Null) AND ((Report.AddedBy)=[Forms]![View/Modify]![SupporterCB] OR/AND ([Forms]![View/Modify]![SupporterCB]) Is Null) AND
...repeat for all 15 fields


This ofcource does not work.

 

[magicrat]

If you want all AND's or all OR's between fields then you could put a radio button control on your form for the user to select AND or OR. Then you build the query both ways, one with AND's the other with OR's and call the appropriate query based on the status of the radio button.
If you want to mix AND's/OR's between fields, it's going to get incredibly complex for the user interface as you would have to allow for placing of parentheses around multiple OR statements.
A or B AND C <> (A or B) AND C

 

[ghubbell]

Hi Larshg,
Different criteria in different fields? Each field gets its own combobox? Type something like this in the criteria field:

Like (IIf(IsNull([Forms]![YourFormName]![YourComboName]),&quot;*&quot;,[Forms]![YourFormName]![YourComboName]))

&quot;Repeat as required&quot; for each field with criteria. Give it a try! Gord
ghubbell@total.net