Enter Parameter Value despite no Parameter in query (1 Viewer)

reklaw

New member
Joined
Sep 15, 2011
Messages
4
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

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,617
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

New member
Joined
Sep 15, 2011
Messages
4
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

New member
Joined
Sep 15, 2011
Messages
4
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

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,617
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

New member
Joined
Sep 15, 2011
Messages
4
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!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom