Heenan, Lambert
Lambert.Heenan at chartisinsurance.com
Wed Dec 28 12:42:46 CST 2011
I think you will find that "The second part of the if statement returns 12:00 for any date on the form less than the max date returned by the query" because what the second part of the iif statement is returning is the Boolean expression... Max([qry Monthly Third Party Water]![RecordDate])<[Forms]![frm Daily Report Date Selector]![EndDate] As the form date is know to be less than or equal to the query date (first part of Iif is not true), the above Boolean expression always evaluates to False, which equals zero, which is the date value 12:00. This is because the query date cannot be less than the form date if the form date is already less than the query date. In short you convoluted you way into a bug. What you need is simply... IIf([Forms]![frm Daily Report Date Selector]![EndDate]> Max([qry Monthly Third Party Water]![RecordDate]), Max([qry Monthly Third Party Water]![RecordDate]),[Forms]![frm Daily Report Date Selector]![EndDate]) ... I think. :-) Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester Sent: Wednesday, December 28, 2011 11:25 AM To: Access Developers discussion and problem solving Subject: [AccessD] Iif satement problem in query I have the following if statement in a query. The first part of the query returns the correct date. The second part of the if statement returns 12:00 for any date on the form less than the max date returned by the query. What might I be doing wrong? Test: IIf([Forms]![frm Daily Report Date Selector]![EndDate]> Max([qry Monthly Third Party Water]![RecordDate]), Max([qry Monthly Third Party Water]![RecordDate]), Max([qry Monthly Third Party Water]![RecordDate])<[Forms]![frm Daily Report Date Selector]![EndDate]) Date returned by qry Monthly Third Party Water RecordDate Gas Plant Wagner Cogdell 1/31/2011 35400 2834 2/28/2011 25900 2400 3/31/2011 33452 2500 4/30/2011 46503 2891 5/31/2011 24402 3746 6/30/2011 15324 3557 7/31/2011 14154 3765 8/31/2011 25074 3715 9/30/2011 24041 3456 10/31/2011 24725 3593 11/30/2011 25000 3468 Chester Kaup Engineering Technician Kinder Morgan CO2 Company, LLP Office (432) 688-3797 FAX (432) 688-3799 No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com