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

 

[jfischer]

The TRUE action that you show for your IIf statement is actually a second conditional statement. Try recoding the IIf to use nested IIfs as follows:

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

I don't know how this fits with the rest of your query design, but it should point you in the right direction.

 

[UncleJack]

Make sure the value input is actually a date by running it through the DateValue function, e.g.

DateValue([Start]) and DateValue([End])

Uncle Jack