[AccessD] Iif satement problem in query

Kaup, Chester Chester_Kaup at kindermorgan.com
Wed Dec 28 16:54:30 CST 2011


I ended up doing it in VBA. Much easier. Thanks for the assistance.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Wednesday, December 28, 2011 2:44 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Iif satement problem in query


Ok...I see now...you are trying to change the scope of the query in the iif.
 
can you change the criteria on the query itself... to always be limited to the max(date) < Forms]![frm Daily Report Date Selector]![EndDate]...
 
Or...maybe use a dlookup to get the date instead of an IIF?
 
Mark A. Matte

 

> From: Chester_Kaup at kindermorgan.com
> To: accessd at databaseadvisors.com
> Date: Wed, 28 Dec 2011 13:21:19 -0600
> Subject: Re: [AccessD] Iif satement problem in query
> 
> 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
> 
> 
> -- 
> 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





More information about the AccessD mailing list