[AccessD] Iif satement problem in query

Heenan, Lambert Lambert.Heenan at chartisinsurance.com
Wed Dec 28 12:42:46 CST 2011


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




More information about the AccessD mailing list