[AccessD] Iif satement problem in query

What I want return when the end date is less than the max date from the query is the max date that is less than the end date entered on the form. For example if the end date entered on the form is 6/13/2011 the if statement should return 5/31/2011.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Wednesday, December 28, 2011 12:03 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Iif satement problem in query

What is it that you want to return when the EndDate is not greater than
RecordDate?

this is what is in the else portion:

Max([qry Monthly Third Party Water]![RecordDate])<[Forms]![frm Daily Report
Date Selector]![EndDate]


Think of an IIF as an IF Then Else:


IF [Forms]![frm Daily Report Date Selector]![EndDate]>Max([qry Monthly
Third Party Water]![RecordDate])
THEN Max([qry Monthly Third Party Water]![RecordDate])
Else Max([qry Monthly Third Party Water]![RecordDate])<[Forms]![frm Daily
Report Date Selector]![EndDate]


On Wed, Dec 28, 2011 at 8:24 AM, Kaup, Chester <
Chester_Kaup at kindermorgan.com> wrote:

> 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