Search by date query via a form

[Kunal]

I have a query which I want to use to search for records by a date range. The following is a cut down version of the SQL I use.

SELECT NewClient.[Date requested]
FROM NewClient
WHERE ((IIf([Forms]![MIS]![Start]<>&quot;(All)&quot;,(NewClient.[Date requested]) Between [Forms]![MIS]![Start] And [Forms]![MIS]![Finish],[date requested])));

The critria basically looks up a combo box on a form to see if it &quot;(All)&quot; and if so then ignore the criteria and show all dates. I it does not show &quot;(ALL)&quot; then search by date using the date range in the combo (start) & (finish).

Now the problem is that this does not seem to work properly, whenever I I select a date range it does not provide any results. Now I have tested the date range critria seperatly with the IIf statement and it seems to work i.e Between [Forms]![MIS]![Start] And [Forms]![MIS]![Finish]

I am not sure as to wheer I am going wrong and would be very greatful for any help with this.

Many thanks,

Kunal

 

[suntsu]

Hi again Kunal,

SELECT NewClient.[Business Unit], NewClient.[Date Requested]
FROM NewClient
WHERE (((NewClient.[Business Unit])=[forms]![MIS]![BU]) AND ((NewClient.[Date Requested]) Between [forms]![mis]![start] And [forms]![mis]![finish]) AND ((([forms]![MIS]![BU]=&quot;<ALL>&quot)=False)) OR (((NewClient.[Business Unit]) Like &quot;*&quot; Or (NewClient.[Business Unit])=&quot;&quot; Or (NewClient.[Business Unit]) Is Null) AND ((([forms]![MIS]![BU]=&quot;<ALL>&quot)=True));

There you go

Hope this is helpful.

SunTsu

 

[Kunal]

SunTsu,

Firstly I just want to thank you for taking time out to help me with my query problems, I very much appriciate it.

I was wondering if I can pick your brain again.

I just can't seem to get it right, I think perhaps I should have given you the full SQL I want to use as I have a around 7 fields which will basically look up values in 7 different combo boxs. 2 of thses combo boxs are used to query records between a date range (start) and (finish). The user may not select a date range from the form and just pick say a business unit and so want the query to just use the criteria in the business unit field. Now I cant seem to configure the query like you suggested to allow for multiple criteria's to handle if only onlyone combo box is used. This last SQL you sent works nicely for the date range but I had to take off the Business Unit field.

Would be very greatful if you could help advise asto why this is and how I can resolve the problem.

I hope I made sense, actually would it be possible for me to email you a sample mdb to show you what I mean?

Many thanks,

Kunal

 

[suntsu]

No problem Kunal,

Send an email to Tsunami_Hiroshima@hotmail.com (I expect spam to come from this so I am posting my spam magnet address

don't send the database to this address, I will advise you my 'real' email address which you can send the database to.
(I believe the hotmail limit is 2.5MB per mailbox ?)

speak soon,

SunTsu

 

[Kunal]

Hi, I just sent you a mail!

Thanks,

Kunal