[AccessD] Iif satement problem in query

The way I would do it (which isn't necessarily the correct way ;) ) would
be to modify the query with left join pulling the previous date:
Use that date in the False section:

If(YourLogic, TrueStuff, [qry Monthly Third Party Water]![PrevMaxDate])


Another thing you could do (If in Access) is use a DMAX in the False
section but this might be slower.

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

> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>