Form parameter for query

[extractor]

I've been trying to get this date range qury to work for some time now.

SELECT tblTrackingInfo.Date

FROM tblTrackingInfo

WHERE (((tblTrackingInfo.Date)=IIf(nz([Forms]![frmSearch]![txtFromDate].[Value],"&quot="",[tblTrackingInfo]![Date],([tblTrackingInfo]![Date] In (SELECT tblTrackingInfo.Date FROM tblTrackingInfo WHERE (tblTrackingInfo.Date) Between [Forms]![frmSearch]![txtFromDate] And [Forms]![frmSearch]![txtToDate])))));

It just keeps returning no results if the form fields are populated.

Help!!!!!!

 

[scriverb]

Try this:

WHERE (((tblTrackingInfo.Date)=IIf(nz([Forms]![frmSearch]![txtFromDate].[Value],"&quot="",[tblTrackingInfo]![Date],([tblTrackingInfo]![Date] In (SELECT tblTrackingInfo.Date FROM tblTrackingInfo WHERE (tblTrackingInfo.Date) Between #[Forms]![frmSearch]![txtFromDate]# And #[Forms]![frmSearch]![txtToDate]#)))));

Let me know if this works for you.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[extractor]

I'm sorry, I tried inserting the "#" signs but to no avail. I am getting an invalid syntax error. I am using office XP or office 2000 as the application is supposed to be cross platform. I was wondering if this will make a difference??

PS Thanks for the fast response.

 

[scriverb]

The problem isn't with the date designation itself it has to do with your initial IIF statement. On the False parameter you are creating a complete date Between statement but this value will just be placed on the right side of the original equation and come up no records:
(tblTrackingInfo.Date)= ([tblTrackingInfo]![Date] In (SELECT tblTrackingInfo.Date FROM tblTrackingInfo WHERE (tblTrackingInfo.Date) Between #[Forms]![frmSearch]![txtFromDate]# And #[Forms]![frmSearch]![txtToDate]#));

You see what I mean. That comparison will never be True.

Let's try this.

WHERE (tblTrackingInfo.Date = IIF(IsNull([Forms]![frmSearch]![txtFromDate]) or IsNull([Forms]![frmSearch]![txtFromDate]),[tblTrackingInfo]![Date], Null)
OR ([tblTrackingInfo]![Date] In (SELECT tblTrackingInfo.Date FROM tblTrackingInfo WHERE (tblTrackingInfo.Date) Between #[Forms]![frmSearch]![txtFromDate]# And #[Forms]![frmSearch]![txtToDate]#));

Post back with the results.




Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[extractor]

I see it makes perfect sense. The (tblTrackingInfo.Date)= ([tblTrackingInfo]![Date] was the problem all along. Thanks for the insight, saved a lot of head banging!!!

The alternate solution worked great but are the "#" signs just markers??

Thanks again.