Alias in expression

[smbrr]

Hello,

I have a column called [Sales Qty] and recently need to sum it so it becomes [SumOfSales Qty]. That simply won't do because the result is used in macros and such afterwards, so I've done the rather silly but efficient method of aliasing my column with its own name: "Sales Qty: Sales Qty". It works, and my column is now named [Sales Qty].

However, I've got another column that uses [Sales Qty] in an expression:

Stk Fin P: Sum(IIf([Période Mensuelle]=[P_FIN_PERIODE];[Sales Qty];0))

And that one is causing me an error "subquery cannot be used in expression ...", but ONLY if I'm trying to run the query on its own. Indeed, the query is only subquery among others to another query and when I run that one final query, it works just fine. I'd like to be able to run that query by itself though, if only to chase inconsistencies in my reports.

So, I figure I could have another subquery in-between just to have my expression, but I have a bunch of these already and I don't want to make a mess.

Is there a solution to my problem? For now I delete that column when I need to run that query to check some stuff and cancel the changes when I'm done but that error is tingling me.

Thanks

 

[Ranman256]

if [Période Mensuelle] is a query, you don't use an IIF, you make a query that sums the data,
then add THAT query into the main query and set criteria there.

 

[smbrr]

[Période Mensuelle] is a field, [P_FIN_PERIODE] is a user input parameter and [Sales Qty] is a field

 

[smbrr]

I fixed it, it was just a matter of writing it

.[field] so [Retail Info].[Sales Qty] so access would know I want the base field and not the alias of itself.

Makes sense really.