Enter Parameter Value despite no Parameter in query

[reklaw]

Hello, I think I have an issue that should be able to be solved in seconds but I cant find anything online or in this forum to help me. I have a query made using the Query Designer. In Field: CardsWithFuses: IIf([Device Definition].[DeviceType]="RTU",(-Int([IOwithfuses]/(-16))),(-Int([IOwithfuses]/(-32)))) Total: Group By The rest of the fields are blank. Just trying to achive a column heading of CardsWithFuses but Access comes up with a prompt to enter a value for CardsWithFuses. What am I doing wrong? Thanks in advance!

 

[pbaldy]

Because of the order the clauses are evaluated in, you can't use the alias in the group by clause. You can try switching to SQL view and change

GROUP BY CardsWithFuses

to

GROUP BY IIf([Device Definition].[DeviceType]="RTU",(-Int([IOwithfuses]/(-16))),(-Int([IOwithfuses]/(-32))))

 

[reklaw]

I think I am doing the naming correctly but the CardsWithFuses column is used in a calculation in another column in the query builder. What order does the query execute? i.e. can the value for CardsWithFuses be populated before it is used elsewhere?

 

[reklaw]

Because of the order the clauses are evaluated in, you can't use the alias in the group by clause. You can try switching to SQL view and change

GROUP BY CardsWithFuses

to

GROUP BY IIf([Device Definition].[DeviceType]="RTU",(-Int([IOwithfuses]/(-16))),(-Int([IOwithfuses]/(-32))))

THanks for your quick response. The query designer has got it like the following: SELECT ... , IIf([Device Definition].[DeviceType]="RTU",(-Int([IO with fuses]/(-16))),(-Int([IO with fuses]/(-32)))) AS CardsWithFuses GROUP BY... , IIf([Device Definition].[DeviceType]="RTU",(-Int([IO with fuses]/(-16))),(-Int([IO with fuses]/(-32)))); So already under both select and group by

 

[pbaldy]

I haven't noticed a pattern, but sometimes you can use the alias in subsequent calculations and sometimes you can't. One workaround is to create a base query with that calculation in it and then base another query on that one and do the grouping and calculating there.

 

[reklaw]

I haven't noticed a pattern, but sometimes you can use the alias in subsequent calculations and sometimes you can't. One workaround is to create a base query with that calculation in it and then base another query on that one and do the grouping and calculating there.

Thanks for your help, seems as though if the column alias is to the left of where it is referenced in the query builder it doesnt pop-up with the parameter box. will confirm this after getting everything in the right order!