Sorry, I saw that the line at the ampersand was changed to "&".
"IIf(conditionA,resultA,[else] IIf(conditionB,resultB)) &
IIF(conditionC,resultC)"
We don't know how the ampersand between the second and third iif statements
is affecting things - I hadn't seen it used this way before. With that in
mind, we're not sure what is causing access to end so ungraciously.
Personally, I haven't been able to refer to expressions by name in another
expression before either, so I feel to be on some uncharted ground here, but
then I'm only going on a couple of year's experience in Access.
Here is the entire query, I hope the posting doesn't mess with it too much.
SELECT dbo_component.componentID,
Sum(Year(Now())-Year(dbo_component.begin_dt)) AS Age,
dbo_d_component.lifetime, [Age]-dbo_d_component!lifetime AS [Age Difference],
dbo_inspection.d_conditionID, dbo_d_component.description,
dbo_building.buildingID, dbo_building.title, dbo_d_component.units,
dbo_d_component.unitcost, dbo_component.qty, dbo_building.stories,
dbo_building.grossarea, Year(Now())-[Age Difference] AS [Year Due],
dbo_building.d_buildingID,
Round(IIf([dbo_component]![qty]>1,([dbo_component]![qty]*[dbo_d_component]![unitcost]),(IIf(([dbo_building]![d_buildingID]
In
(1,3,4,10,11,12,13,14,15,21,22)),(([dbo_building]![grossarea]/500)*[dbo_d_component]![unitcost])))),2)
& Round((IIf(([dbo_building]![d_buildingID] In
(2,6,7,9,19)),(([dbo_building]![grossarea]/100)*[dbo_d_component]![unitcost]))),2) AS [Replacement Cost]
FROM (((dbo_component INNER JOIN dbo_d_component ON
dbo_component.d_componentID = dbo_d_component.d_componentID) INNER JOIN
dbo_building ON dbo_component.buildingID = dbo_building.buildingID) INNER
JOIN dbo_inspection ON dbo_component.componentID =
dbo_inspection.componentID) INNER JOIN dbo_d_inspector ON
dbo_inspection.d_inspectorID = dbo_d_inspector.d_inspectorID
GROUP BY dbo_component.componentID, dbo_d_component.lifetime,
dbo_inspection.d_conditionID, dbo_d_component.description,
dbo_building.buildingID, dbo_building.title, dbo_d_component.units,
dbo_d_component.unitcost, dbo_component.qty, dbo_building.stories,
dbo_building.grossarea, dbo_building.d_buildingID,
Round(IIf([dbo_component]![qty]>1,([dbo_component]![qty]*[dbo_d_component]![unitcost]),(IIf(([dbo_building]![d_buildingID]
In
(1,3,4,10,11,12,13,14,15,21,22)),(([dbo_building]![grossarea]/500)*[dbo_d_component]![unitcost])))),2)
& Round((IIf(([dbo_building]![d_buildingID] In
(2,6,7,9,19)),(([dbo_building]![grossarea]/100)*[dbo_d_component]![unitcost]))),2)
HAVING (((dbo_d_component.description) Like "*windo*"));
--
Harry E Vermillion
IT2
Division of Wildlife
State of Colorado
Harry V said:
Thanks for the reply, Fred. We used the GUI to compose the query, so I
condensed it. You are correct, and that is what I meant by "mutually
exclusive".
The ampersand's use in this way is not documented. at least we can't find
anything outside of its use to concatenate two strings.
We can't figure out why its crashing Access in such a way that we're not
even asked to send a report to Microsoft.
Previous attempts at this expression using 3 nested Iif statements caused
the crash as well, but we're hard pressed to find the correct documentation
(gee, I'm sure we're not alone here) on nesting.
Do you really want to see the whole query?
--
Harry E Vermillion
IT2
Division of Wildlife
State of Colorado
fredg said:
We're using Access 2002 with links to tables in a server-based msd.
We've come across a problem dealing with an expression within a query. The
syntax for the expression boils down to:
IIf(conditionA,resultA,[else] IIf(conditionB,resultB)) &
IIF(conditionC,resultC)
Each condition/result is mutually exclusive. (We happened on the '&' and it
works forthe query.)
This will run, but when we try to save any modification to the query as a
whole, Access ends, without ANY message. error, caution or warning.
When this query is imported to a new mdb that only contains links to the
required tables and the query itself, Access will allow one save, after that
the progam ends without saving the change in the query.
I can post the query and some sample data, but we've go it down to the
expression, we're wodering what is so complex about this expression that it
abruptly ends Access?
The expression's syntax leaves a bit to be desired. :-(
Are there always only 3 conditions (A, or B, or C) ?
If it isn't A or B then it must be C.
NewColumn: IIf(conditionA,resultA,IIf(conditionB,resultB,resultC))
Never a none of the above ( ... or D, or E, etc_)?
If there can be none of the A or B or C, then use:
NewColumn: IIf(conditionA,resultA,IIf(conditionB,resultB,
IIf(ConditionC,resultC,"")))
If it isn't A or B or C then show nothing ("").
