Query gives right answer but asks for parameter that isnt needed?

[offie]

So I might be committing a no no but I want a query with 2 numbers and the percent of those 2, I have:

SELECT
Sum(COAll.[Change Order Sum]) AS [SumOfChange Order Sum],
Sum(COAll.[Contract Amount]) AS [SumOfContract Amount],
[SumOfChange Order Sum]/[SumOfContract Amount]*100 AS [Percent]


FROM COAll;

So I took fields that are being found and used them in the same query, I wasn't sure if it would work but when I ran it it asked for the parameters of [SumOf Change ORde Sum] and [SumOfContract Amout] but I just hit enter and it gives the right numbers.

How can I make the parameter boxes not show up? Do I have to make 2 queries?

Thanks!

[RuralGuy]

Don't use the alias name. Use the Sum(...) statement again.

[offie]

Ah that was so simple, thanks!

[efmbman]

Sorry if I take this on a tangent... but I have a similar problem when using nested IIF statements. I have the statement checking for a null value, then adding 6 months to a given date if true, if false another nested IIF directs to another check, and so on 11 times. For each check for null, I am prompted for a parameter.

I don't understand the term "alias" as used in post # 2

Thanks in advance!

[RuralGuy]

AS [SumOfChange Order Sum] assigns an alias to the Sum(COAll.[Change Order Sum]) calculation.

[efmbman]

AS [SumOfChange Order Sum] assigns an alias to the Sum(COAll.[Change Order Sum]) calculation.

Thanks, but that is over my head. I think I may have picked a forum too advanced for my understanding.

[RuralGuy]

An alias is simply another name by which you can call an object.

[efmbman]

I think I should start over...

This is what I am placing right into the query design grid:

Follow Up Date: IIf([FormData]![StaffFU11] Is Not Null,DateAdd("m",72,[INITSTFF]),
IIf([FormData]![StaffFU10] Is Not Null,DateAdd("m",66,[INITSTFF]),
IIf([FormData]![StaffFU09] Is Not Null,DateAdd("m",60,[INITSTFF]),
IIf([FormData]![StaffFU08] Is Not Null,DateAdd("m",54,[INITSTFF]),
IIf([FormData]![StaffFU07] Is Not Null,DateAdd("m",48,[INITSTFF]),
IIf([FormData]![StaffFU06] Is Not Null,DateAdd("m",42,[INITSTFF]),
IIf([FormData]![StaffFU05] Is Not Null,DateAdd("m",36,[INITSTFF]),
IIf([FormData]![StaffFU04] Is Not Null,DateAdd("m",30,[INITSTFF]),
IIf([FormData]![StaffFU03] Is Not Null,DateAdd("m",24,[INITSTFF]),
IIf([FormData]![StaffFU02] Is Not Null,DateAdd("m",18,[INITSTFF]),
IIf([FormData]![StaffFU01] Is Not Null,DateAdd("m",12,[INITSTFF]),
IIf([FormData]![INITSTFF] Is Not Null,DateAdd("m",6,[INITSTFF])))))))))))))

For clarity, I added "returns" to show each nested IIF in a separate line. When I run the query, I am prompted for a parameter for each one... StaffFU11, StaffFU10, etc.

I don't understand why I am being prompted for a parameter.

[offie]

Me and my limited knowledge, if FormDate a table/query? Then the fields are [FormData].[StaffFU10] (with a period)

Edit: is FormData*

[efmbman]

FormData is a table, yes. So replace "!" with "." is what you are saying?

[offie]

Yup, also I noticed the your IIF statements don't have a closing ), not sure if that is on purpose.

Hope that works

Edit: nevermind about the () I see what you are trying to do

[efmbman]

I replaced each "!" with a "." Same result

[RuralGuy]

I think you can only nest IIF's 7 levels, but I may be wrong there. I usually use a Select Case function because they are much easier to read and maintain.