Problem with select query criteria 1

[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

 

[suntsu]

Hi Kunal,

I am not too hot with SQL, but try this code:

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

If I understand the question this should work

Thanks,

SunTsu

 

[suntsu]

Sorry, I just reread your initial post.
My SQL will not help

It sounds like it may be a relationship which is restricting your recordset ?

 

[Kunal]

SunTsu,

Thnaks for your suggestions, I tried it and unfortunately it did not work, I got the same results as I did with my SQL. It still seems to only pick up record where the business unit is NOT null, intstead of all records.

I criterias some how needs to say if the combo box says &quot;(ALL)&quot; then disregard the criteria and show all records.

Any other suggestions would be very much apprciated.

Many thanks,

Kunal

 

[Kunal]

Sorry just read your second post.

I don't have any relationships set with this table anywhere in my DB.

Thanks,

Kunl

 

[suntsu]

Hi Kunal,

Thought I ought to make up for earlier, please try this:

SELECT NewClient.[Business Unit]
FROM NewClient
WHERE (((NewClient.[Business Unit])=[forms]![MIS]![MU]) AND ((([forms]![MIS]![MU]=&quot;<ALL>&quot)=False)) OR (((NewClient.[Business Unit]) Like &quot;*&quot AND ((([forms]![MIS]![MU]=&quot;<ALL>&quot)=True)) OR (((NewClient.[Business Unit])=&quot;&quot; Or (NewClient.[Business Unit]) Is Null) AND ((([forms]![MIS]![MU]=&quot;<ALL>&quot)=True));

I have tested it and it works fine.

Thanks,

SunTsu

 

[suntsu]

Doh !

Substitute my [MU] for your [BU].

Jeez, I need some sleep