Search by date

[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

 

[lakers8175]

Try this.....


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