Query Criteria Problem

[Kunal]

I have a select query which has a number of criteria's which referance combo boxs on a form. The following is a cut down version of the SQL I use:

SELECT NewClient.Name, NewClient.[Business Unit]
FROM NewClient
WHERE (((NewClient.[Business Unit])=IIf([Forms]![MIS]![BU]<>
&quot;(All)&quot;,[Forms]![MIS]![BU],[Business Unit])));

It basically checks the combo box to see if 'All' has been selected and if so then use the whole content of the field 'business unit' else use the item selected as the criteria i.e in this case a particular business unit.

Now this seems to work fine when you select an item as it provides the right no of records in the results. However when you select 'All' to show all records it does not provide the right no. of records in this case is provides
2500 records when the table total is 8000 which is the right no. it should provide. Now the reason for this is that not every record has a business unit and so it is only picking up ones that have. So I tried changing the criteria in the above SQL to

Like IIf([Forms]![MIS]![BU]<>&quot;(All)&quot;,[Forms]![MIS]![BU],&quot;*&quot));

However this did not work either it still only picked up ALL records that have a business unit not ALL both with and without a BU.

Now I am not sure as to why this is and would be very greatful for any help and advise on this.

Many thanks,

Kunal

 

[lameid]

Only add the criteria if you need it... Concatenate your SQL together.

Your statement won't work for the all case because when you compare null to anything but null it is false. YOu would need

Where (NewClient.[Business Unit]) is null or (NewClient.[Business Unit]) Like &quot;*&quot;

to get all records.

 

[Kunal]

Thnaks for you suggestion, thing is that I need to be able to set the quiery to look at a combo box value on a form and if it says &quot;(ALL)&quot; then show ALL records whether it has a business unit or not.

Would be greatful if you suggest how to modify your SQL to look at the form.

Many thanks

Kunal