Kaup, Chester
Chester_Kaup at kindermorgan.com
Wed Dec 28 16:55:09 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 David McAfee
Sent: Wednesday, December 28, 2011 1:53 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Iif satement problem in query
What happens if you put parenthesis around the false part?
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]))
I still believe you need a separate select for the false part.
I just did something on a test table over here:
SELECT * FROM tblRentalAction WHERE RentalID = 37913
Results:
RentalActionID RentalID LocKeyID TranDate
110235 37913 1 2011-02-15 00:02:00.000
110276 37913 3 2011-02-28 00:00:00.000
114347 37913 8 2011-07-11 00:00:00.000
SELECT
MaxDate,
RentalID,
Max(PrevDate) AS NextDate
FROM
(
SELECT A.*, B.PrevDate
FROM
(
SELECT
Max(TranDate) AS MaxDate,
RentalID
FROM tblRentalAction
WHERE RentalID = 37913 GROUP BY RentalID
) A
LEFT JOIN
(
SELECT TOP 3(TranDate) AS PrevDate, RentalID
FROM tblRentalAction
WHERE RentalID = 37913
) B
ON A.RentalID = B.RentalID AND A.MaxDate <> B.PrevDate
) C GROUP BY MaxDate, RentalID
Results:
MaxDate RentalID NextDate
2011-07-11 00:00:00.000 37913 2011-02-28 00:00:00.000
You could then use your IIF(Logic, TruePart, FalsePart) on that query.
Not sure if it helps or further confuses you. :)
David
On Wed, Dec 28, 2011 at 11:21 AM, Kaup, Chester <
Chester_Kaup at kindermorgan.com> wrote:
> 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