IIf dilemma

  • Thread starter Thread starter BCS
  • Start date Start date
B

BCS

I have a query but unable to get this IIf to work. Does anyone see the
problem and what I am doing wrong with the following:

......IIf (State = Delivery, ClassCost:
([Cost]+[CostAdjust])*[CurrentEnrollment]/2)
 
Jerry, Thank you for your response! In my original post, I did NOT enter the
complete expression. Does this make a difference and do you see a syntax
error:

ClassCost:IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment],ClassCost:([Cost]+[CostAdjust])*[CurrentEnrollment]/2),0)

Jerry Whittle said:
ClassCost:IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment]/2),0)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


BCS said:
I have a query but unable to get this IIf to work. Does anyone see the
problem and what I am doing wrong with the following:

.....IIf (State = Delivery, ClassCost:
([Cost]+[CostAdjust])*[CurrentEnrollment]/2)
Click to expand...
Click to expand...
 
ClassCost is the name of the calculated field. It should only appear at
the beginning of the text in the box..

ClassCost:IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment]
,([Cost]+[CostAdjust])*[CurrentEnrollment]/2)

I think the above should work for you. It will return the results of
one calculation if State = "Delivery" and the other calculation if State
is not equal to "Delivery"

:

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Jerry, Thank you for your response! In my original post, I did NOT enter the
complete expression. Does this make a difference and do you see a syntax
error:

ClassCost:IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment],ClassCost:([Cost]+[CostAdjust])*[CurrentEnrollment]/2),0)

Jerry Whittle said:
ClassCost:IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment]/2),0)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


BCS said:
I have a query but unable to get this IIf to work. Does anyone see the
problem and what I am doing wrong with the following:

.....IIf (State = Delivery, ClassCost:
([Cost]+[CostAdjust])*[CurrentEnrollment]/2)
Click to expand...
Click to expand...
Click to expand...
 
John - Thank you so much...I did not receive an error message w/this syntax!!
However, I may have another problem. When I tried to run the report, I
received an error message that essentially said my query 'did not include the
specified expression.' Basically, I'm trying to execute a query that
provides $ for each instructor that 'delivers' (teaches) and sometimes there
are 2 instructors for each 'delivery.' The report ran fine until I tried to
include this portion (w/the IIf) that includes 2 instructors for each
'delivery.' (Hope that makes sense)

Any insight would be much appreciated!

John Spencer said:
ClassCost is the name of the calculated field. It should only appear at
the beginning of the text in the box..

ClassCost:IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment]
,([Cost]+[CostAdjust])*[CurrentEnrollment]/2)

I think the above should work for you. It will return the results of
one calculation if State = "Delivery" and the other calculation if State
is not equal to "Delivery"

:

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Jerry, Thank you for your response! In my original post, I did NOT enter the
complete expression. Does this make a difference and do you see a syntax
error:

ClassCost:IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment],ClassCost:([Cost]+[CostAdjust])*[CurrentEnrollment]/2),0)

Jerry Whittle said:
ClassCost:IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment]/2),0)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a query but unable to get this IIf to work. Does anyone see the
problem and what I am doing wrong with the following:

.....IIf (State = Delivery, ClassCost:
([Cost]+[CostAdjust])*[CurrentEnrollment]/2)
Click to expand...
Click to expand...
Click to expand...
 
POST the SQL of the query you are having a problem with AND the text of
the error message.

(Menu: VIEW: SQL when in design view)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
John, Here is the SQL.....

SELECT [Eval Data - Instructor].LastName, [Eval Data - Instructor].Course,
[Eval Data - Instructor].Section, [Eval Data - Instructor].StartDate,
Class.Cost, Class.CostAdjust, Class.CurrentEnrollment,
IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment],([Cost]+[CostAdjust])*[CurrentEnrollment]/2) AS [Class Cost]
FROM Class INNER JOIN [Eval Data - Instructor] ON Class.Section = [Eval Data
- Instructor].Section
GROUP BY [Eval Data - Instructor].LastName, [Eval Data - Instructor].Course,
[Eval Data - Instructor].Section, [Eval Data - Instructor].StartDate,
Class.Cost, Class.CostAdjust, Class.CurrentEnrollment
ORDER BY [Eval Data - Instructor].LastName;

The error message I received is......"You tried to execute a query that did
not include the specified expression
'IIf([State]="Delivery",([Cost]+[CostAdjust])+[CurrentEnrollment],([Cost]+[CostAdjust])*[CurrentEnrollment]/2)' as part of an aggregate function"

Thank you for any assistance you can give me!!!
 
Well, you need to GROUP BY the expression also.

SELECT [Eval Data - Instructor].LastName
, [Eval Data - Instructor].Course
, [Eval Data - Instructor].Section
, [Eval Data - Instructor].StartDate
, Class.Cost, Class.CostAdjust
, Class.CurrentEnrollment
, IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment]
,([Cost]+[CostAdjust])*[CurrentEnrollment]/2) AS [Class Cost]
FROM Class INNER JOIN [Eval Data - Instructor]
ON Class.Section = [Eval Data - Instructor].Section
GROUP BY [Eval Data - Instructor].LastName
, [Eval Data - Instructor].Course
, [Eval Data - Instructor].Section
, [Eval Data - Instructor].StartDate
, Class.Cost, Class.CostAdjust
, Class.CurrentEnrollment
, , IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment]
,([Cost]+[CostAdjust])*[CurrentEnrollment]/2)
ORDER BY [Eval Data - Instructor].LastName;

HOWEVER since you are not using any of the aggregate functions such as
Sum, Count, Avg, etc. you can simplify the query by using the DISTINCT
operation. In Design view, use the query's unique values property and
set it to Yes. (In Design view, Right-Click in any empty field box and
select properties to get to the query's properties)

SELECT DISTINCT [Eval Data - Instructor].LastName
, [Eval Data - Instructor].Course
, [Eval Data - Instructor].Section
, [Eval Data - Instructor].StartDate
, Class.Cost, Class.CostAdjust
, Class.CurrentEnrollment
, IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment]
,([Cost]+[CostAdjust])*[CurrentEnrollment]/2) AS [Class Cost]
FROM Class INNER JOIN [Eval Data - Instructor]
ON Class.Section = [Eval Data - Instructor].Section
ORDER BY [Eval Data - Instructor].LastName;

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John, Here is the SQL.....

SELECT [Eval Data - Instructor].LastName, [Eval Data - Instructor].Course,
[Eval Data - Instructor].Section, [Eval Data - Instructor].StartDate,
Class.Cost, Class.CostAdjust, Class.CurrentEnrollment,
IIf([State]="Delivery",([Cost]+[CostAdjust])*[CurrentEnrollment],([Cost]+[CostAdjust])*[CurrentEnrollment]/2) AS [Class Cost]
FROM Class INNER JOIN [Eval Data - Instructor] ON Class.Section = [Eval Data
- Instructor].Section
GROUP BY [Eval Data - Instructor].LastName, [Eval Data - Instructor].Course,
[Eval Data - Instructor].Section, [Eval Data - Instructor].StartDate,
Class.Cost, Class.CostAdjust, Class.CurrentEnrollment
ORDER BY [Eval Data - Instructor].LastName;

The error message I received is......"You tried to execute a query that did
not include the specified expression
'IIf([State]="Delivery",([Cost]+[CostAdjust])+[CurrentEnrollment],([Cost]+[CostAdjust])*[CurrentEnrollment]/2)' as part of an aggregate function"

Thank you for any assistance you can give me!!!

John Spencer said:
POST the SQL of the query you are having a problem with AND the text of
the error message.

(Menu: VIEW: SQL when in design view)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Click to expand...
Click to expand...
 
Back
Top