Select Query based on a combo box is not returning required values. 1

[Tfost]

I have set up a form in access 2000 which allows you to select criteria in order to run a query. This is done via a series of combo boxes.

I want the query to pick up the criteria from the combo boxes which the user select but ignore the ones the user leaves empty.

In the query i am using the following criteria;

[Forms]![reportselectoractionsnl]![combo39]
(or) Like [Forms]![reportselectoractionsnl]![combo39] Is Null

At present I have two criteria to search on. When I put this expression in only one field in the query it works fine either the records selected via the combo box are displayed or if left blank all records are displayed. However when i add in the second criteria it always displays no records, unless both combo boxes are empty then it displays all records.

Any advice on where I am going wrong would be greatly appreciated.

 

[KenReay]

Hi

This might be difficult to explain,

I assume you are putting both criteria on one 'line' of the query builder grid ?, this causes the two condistions to be "ANDed" ie result is only true if BOTh condistions are satisfied

If you put one experession on (say) line 1 and the other on line 2 then they will be "ORed", ie result is true if EITHER expression is satisfied

do you follow ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Tfost]

The first part of the expression is on the 1st criteria line of the query grid and the second is on the second (or) line of the query grid so it looks like this.

Field: Owner
Table: actionsnolinktbl
Sort:
Show:
Criteria:[Forms]![reportselectoractionsnl]![Combo29]
or: Like [Forms]![reportselectoractionsnl]![Combo29] Is Null

So I think I am already doing what you are suggesting. If this isn't what you meant let me know.

Thanks for your reply.

 

[KenReay]

Hi

It is what I meant and it is not, I knew it would be difficult to explain!

Field: Owner
Table: actionsnolinktbl
Sort:
Show:
Criteria:[Forms]![reportselectoractionsnl]![Combo29]
or: Like [Forms]![reportselectoractionsnl]![Combo29] Is Null

you need the criteria for the other combobox on lines 3 and 4 of Criteria:, not lines 1 and 2

Do you follow

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Tfost]

I wasn't sure if you meant put the expression in criteria lines 3 and 4 of the same field or the other field so I tried both e.g

Field: Owner
Table: actionsnolinktbl
Sort:
Show:
Criteria:[Forms]![reportselectoractionsnl]![Combo29]
or: Like [Forms]![reportselectoractionsnl]![Combo29] Is Null
or:[Forms]![reportselectoractionsnl]![Combo39]
or: Like [Forms]![reportselectoractionsnl]![Combo39] Is Null


Or whether you meant:

Field: Owner
Table: actionsnolinktbl
Sort:
Show:
Criteria:[Forms]![reportselectoractionsnl]![Combo29]
or: Like [Forms]![reportselectoractionsnl]![Combo29] Is Null

Field: Project
Table: actionsnolinktbl
Sort:
Show:
Criteria:
or:
or:[Forms]![reportselectoractionsnl]![Combo39]
or: Like [Forms]![reportselectoractionsnl]![Combo39] Is Null

And these are the results.

In Same Field:
Select One Criteria (leave other blank)= Displays all records (not correct)
Select Two Criteria = Displays all records (not correct)
Select No Criteria = Displays all records (correct)

In Seperate fields:
Select one criteria (leave other blank) = Displays all records (not correct)
Select two criteria = Displays all records (not correct)
Select no criteria = Displays all records (correct)

If I remove the Like expression from the query it will then list all records which match criteria 1 and all records that match criteria 2 but I want it to find records that match BOTH criteria.

I need to be able to tell the query to ignore the criteria if the combo box is empty and therefore just search on the populated combo. But instead it displays all records found by criteria 1 and then lets through all the rest as criteria 2 is blank.

Thanks again for your help.

 

[KenReay]

Hi

I would do this slightly differently, make two 'calculated columns' so:

Field: A:iif(IsNull(Forms!reportselectoracctionsnl!Combo39),True,iif(Forms!reportselectoracctionsnl!combo39=Project,true,false)
Table:
Sort:
Show:
Criteria: True

Field: B:IIF(IsNull([Forms]![reportselectoractionsnl]![Combo29]),true,iif([Forms]![reportselectoractionsnl]![Combo29]=Owner,true,false)
Table:
Sort:
Show:
Criteria:True


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK