Problem with multi criteria select query

[Kunal]

I have a select query which has a number of crietia'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 I am not sure as to why this is and would be very greatful for any help and advise on this.

Many thanks,

Kunal

 

[Psychpt]

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

HTH

PsychPt@Hotmail.com