Access Query: query thinks column name is a parameter. Help please

Question

Hi Access Gurus:

I am in need of some help.  I have a query created in QBE and have a the following column name:

CleanSize: IIf(UCase(Left([SortingGroupDesc],5))="CLEAN",IIf(Len(Trim(Right([SortingGroupDesc],3)))=2,"0" & Trim(Right([SortingGroupDesc],3)),Right([SortingGroupDesc],3)),IIf(UCase(Left([SortingGroupDesc],4))="CULL","Cull","Oth"))

That determines the "Clean Size" of the product being graded.

I have used the column name in other columns as part of an expression to determine the " Kg"of each clean size.  The expression looks like:

054: IIf([CleanSize]="054",[CleanKg],0)

This query has worked sine 2013 but today I needed to make a slight modification to it and no matter what I do when I run the query it prompts me (as if CleanSize) was a parameter and wants me to enter a value.  I have even deleted all columns from the query and re-added one field and ran the query and it still prompts for the "CleanSize" value.  I have closed and reopened the database and even restarted my computer.....all to no avail.

Any help is greatly appreciated.

Thanks.

Can someone please help me.....PLEASE.

Answer 1

Try changing places of 'CleanSize' and '054.'

EDIT - I generally find that Access processes a query from the bottom up (as viewed in SQL view) and your 054 will not see 'CleanSize' as it will not have been processed when Access tries to work on 054.

Answer 2

I always use the full expression rather than the alias column name to prevent errors like this.

Answer 3

Another solution to this issue is to use interim queries. I will often create one query that does my calculations created aliased columns. I will then use that query as the source of another query referencing those aliases.

As Karl indicated, there is an order of processing for a query. So the reason you are getting prompted is that Access has not evaluated the aliased column at the time its trying to evaluate the calculation based on it. Hence it assumes you want to enter the value at run time.