Enter parameter value

[sperriss]

Hi



I'm new to access but I've created a query where I only want some of the columns in the query to show in the final output. The query works fine but when I uncheck the Show button in the design view for the query it asks for a parmater value. It doesn't do this if I leave the Show button checked. Any ideas why this is and is there a solution. The expression I'm using in the query is : Expr1: IIf([Table1]![Abundance] Between 1 And 9,[Table2]![Cat1],0)

Thanks

[ItsMe]

Certain things you need to include in the SELECT statement. An alias is one of those things. I believe another nuance would be Group On.

Why do you need the IIf and what are you trying to do with it?

[sperriss]

Thanks

This relates to a score for a 'biotic index'. Basically the value of the index varies according to the abundance. In this case if the abundance was between 1 and 9, the score would be the value of 'cat 1'- the value of this will depend on the individual species. I'm using IIf because I'm used to using if in Excel but I'm sure there are better ways.

[ItsMe]

Reading the IIf I would think you would want the alias visible and in the SELECT statement. Are you adding criteria to your alias, like WHERE such and such? You can add some criteria and see that the entire IIf will be duplicated in the WHERE Clause. I believe you can include an IIf in the where clause and other places too, without the IIf in the SELECT statement.

[sperriss]

Probably doesn't matter in the end- as long as the query works which is seems to. Perhaps you just can't hide columns that have an Iif statement?

[ItsMe]

It is the Alias you created (Expr1

You created a new field/column in your query.

[June7]

Why would you calculate this value and then not show it? You don't show any filter parameter applied to the constructed field. If the expression is not part of WHERE clause, why would you even calculate it and then not want to show it? If you don't show it how can it be available for use?

I just tested creating a field with IIf and unchecking the Show and no filter parameter. No parameter popup prompt and field does not show. But the field also is not saved with the query.

If I include a filter parameter against the field and uncheck Show then the field is retained but the alias name is dropped.

[sperriss]

This is probably because I'm new to Access and not going about it the right way. The Iif expressions are used to generaste values which are then summed to give the final answer. It's only this final answer that is important, the Iif experssions are only used to help generate this and are not important in themselves.

Not sure what a WHERE clause is or an 'alias' as I am new to Access.

Why would you calculate this value and then not show it? You don't show any filter parameter applied to the constructed field. If the expression is not part of WHERE clause, why would you even calculate it and then not want to show it? If you don't show it how can it be available for use?

I just tested creating a field with IIf and unchecking the Show and no filter parameter. No parameter popup prompt and field does not show. But the field also is not saved with the query.

If I include a filter parameter against the field and uncheck Show then the field is retained but the alias name is dropped.

[June7]

The alias is a fieldname generated for a field created by expression. The WHERE clause is the filter criteria. For instance:

SELECT [qty] * [price] AS ExtAmt FROM Purchases WHERE PurchaseDate > #1/1/2014#;

ExtAmt is alias fieldname for the calculation.

So do not uncheck the Show box for the calculated fields.