Select query prompting me for a parameter (1 Viewer)

[dcx693]

Hope you guys can help me out here.

I have a select query based on a single table with a few calculated fields. One of the columns takes two previous columns (one of which is calculated, one of which is taken directly from the source table) and subtracts one from the other. Basically the formula is something like:
Result:[ColumnB]-[ColumnA]

The query runs just fine, until I either try to sort the Result column, or if I try to specify a criteria, like >0. Any ideas why? I've checked the syntax and it all seems correct since the query runs fine until I try to sort it specify criteria, then it prompts me for [ColumnA] as if I've tried to specify a parameter (or like what happens when you misspell a field name).

 

[RV]

>the query runs fine until I try to sort it specify criteria<

In that case, do you by any chance use an alias for your calculated field and use this alias in your extended query?

RV

 

[dcx693]

Yes, the calculated field is actually called TotalCalcNetIncLAE and the field from the table is called NetIncurredLAE, so the formula I use for the Result column is:
Result:[TotalCalcNetIncLAE]-[NetIncurredLAE]

 

[Fizzio]

Can you not move the calculation or criteria further up the query chain - it seems like you can if you are only subtracting a table value.

 

[dcx693]

Not sure what you mean Fizzio by "move the calculation or criteria further up the query chain".

The calculated field [TotalCalcNetIncLAE] is itself the sum of three other calculated fields. The table field [NetIncurredLAE] is provided by an outside source and the subtraction is done to compare the two values.

To more exactly answer RV's question: The [TotalCalcNetIncLAE] name is an alias for a calculation. The formula is:
TotalCalcNetIncLAE:[CalcNetOS]+[CalcNetPaid]+[CalcNetLAE]

 

[Fizzio]

sorry dcx, I misunderstood. I mistakenly thought that the query was supplied by a previous query. Apologies.

 

[Rich]

Filter the query results on your form

 

[dcx693]

The plot thickens

This is very weird. I still can't get the query to run when I try to sort or apply criteria to that result column. However, after reading Rich's and Fizzio's last replies, I got an idea.

I'm not presenting the results on a form. But I thought about making this non-functioning query a source for another query. So I created a brand-new query, made this query the source table, placed all the same columns on it, including the result column, and was able to sort and apply criteria to it! Unreal!

I've attached a very simplified version of the database that exhibits all the above behaviors. I'd really appreciate it if you guys could look at it, just to make sure I'm not doing something wrong or going nuts. Thanks.

 

[EMP]

As Access treats any aliases in the criteria and the sort order of a query as parameters, there is nothing wrong using a second query. In fact you have found a good solution to the problem.