Kaup, Chester
Chester_Kaup at kindermorgan.com
Wed Dec 28 13:21:19 CST 2011
If there is a way to convoluted your way into a bug I can find it. Unfortunately your if statement does not quite get it either. If the ending date input on the form is greater than the last date returned by the query it works correctly but in the case where it is less than the last date returned by the query the result is not correct. What I need in that case is the largest date returned by the query that is less than the ending date entered on the form For example if 4/15/2011 is entered as the ending date the value returned by the query should be 3/31/2011. Thanks for the help. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: Wednesday, December 28, 2011 12:43 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Iif satement problem in query 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com