"Enter Parameter Value" when sorting

Hi,

I'm a bit of an access newbie, so sorry if this question is a bit daft
but I couldn't find the answer in any FAQs.

When I have a query like this:

SELECT [fdTestThis] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis
FROM tbMyTable
ORDER BY [fdTestThis];

it prompts me to enter a parameter value for fdTestThis, but when I
remove the sort:

SELECT [fdTestThis] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis
FROM tbMyTable;

it doesn't.


In this simple example, I could replace the first line in the first
example with
SELECT [fdMonth] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis (and
also in the ORDER BY as well)
to fix the prompt. However, in the "real" example, the
"tbMyTable.fdMonth AS fdTestThis" is actually "a long calculation AS
fdTestThis" which means I would then have to change the first line to:
SELECT a long calculation AS fdSortThis, "a long calculation AS
fdTestThis

OK this would work, but then I have my long calculation in two places
in the query which seems a bit inefficient, which is why I was trying
to sort by the bit after the "AS". Can anyone help please?

I hope that makes sense: I'm not very good at describing the problem,
sorry! :-)

There is a work-around that may be available and that is to refer to the
column number. Otherwise, you do have to repeat the calculation in the
order by clause. Even then, it is possible that Access Query Analyzer just
copies the referenced column to the Order By clause and you end up with no
actual savings in the execution of the query.

So you might try something like the following:

SELECT [fdTestThis] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis
FROM tbMyTable
ORDER BY 2



wrote in message
oups.com...
Hi,

I'm a bit of an access newbie, so sorry if this question is a bit daft
but I couldn't find the answer in any FAQs.

When I have a query like this:

SELECT [fdTestThis] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis
FROM tbMyTable
ORDER BY [fdTestThis];

it prompts me to enter a parameter value for fdTestThis, but when I
remove the sort:

SELECT [fdTestThis] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis
FROM tbMyTable;

it doesn't.


In this simple example, I could replace the first line in the first
example with
SELECT [fdMonth] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis (and
also in the ORDER BY as well)
to fix the prompt. However, in the "real" example, the
"tbMyTable.fdMonth AS fdTestThis" is actually "a long calculation AS
fdTestThis" which means I would then have to change the first line to:
SELECT a long calculation AS fdSortThis, "a long calculation AS
fdTestThis

OK this would work, but then I have my long calculation in two places
in the query which seems a bit inefficient, which is why I was trying
to sort by the bit after the "AS". Can anyone help please?

I hope that makes sense: I'm not very good at describing the problem,
sorry! :-)

You simply can't sort by an alias in an Access query. Sort on the
expression.

--
Duane Hookom
MS Access MVP

wrote in message
oups.com...
Hi,

I'm a bit of an access newbie, so sorry if this question is a bit daft
but I couldn't find the answer in any FAQs.

When I have a query like this:

SELECT [fdTestThis] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis
FROM tbMyTable
ORDER BY [fdTestThis];

it prompts me to enter a parameter value for fdTestThis, but when I
remove the sort:

SELECT [fdTestThis] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis
FROM tbMyTable;

it doesn't.


In this simple example, I could replace the first line in the first
example with
SELECT [fdMonth] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis (and
also in the ORDER BY as well)
to fix the prompt. However, in the "real" example, the
"tbMyTable.fdMonth AS fdTestThis" is actually "a long calculation AS
fdTestThis" which means I would then have to change the first line to:
SELECT a long calculation AS fdSortThis, "a long calculation AS
fdTestThis

OK this would work, but then I have my long calculation in two places
in the query which seems a bit inefficient, which is why I was trying
to sort by the bit after the "AS". Can anyone help please?

I hope that makes sense: I'm not very good at describing the problem,
sorry! :-)

Maybe I am reading it wrong but it appears that you are creating an alias
with the same name as one of your fields --

SELECT [fdTestThis] AS fdSortThis,
tbMyTable.fdMonth AS fdTestThis

"John Spencer" wrote:

There is a work-around that may be available and that is to refer to the
column number. Otherwise, you do have to repeat the calculation in the
order by clause. Even then, it is possible that Access Query Analyzer just
copies the referenced column to the Order By clause and you end up with no
actual savings in the execution of the query.

So you might try something like the following:

SELECT [fdTestThis] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis
FROM tbMyTable
ORDER BY 2



wrote in message
oups.com...
Hi,

I'm a bit of an access newbie, so sorry if this question is a bit daft
but I couldn't find the answer in any FAQs.

When I have a query like this:

SELECT [fdTestThis] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis
FROM tbMyTable
ORDER BY [fdTestThis];

it prompts me to enter a parameter value for fdTestThis, but when I
remove the sort:

SELECT [fdTestThis] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis
FROM tbMyTable;

it doesn't.


In this simple example, I could replace the first line in the first
example with
SELECT [fdMonth] AS fdSortThis, tbMyTable.fdMonth AS fdTestThis (and
also in the ORDER BY as well)
to fix the prompt. However, in the "real" example, the
"tbMyTable.fdMonth AS fdTestThis" is actually "a long calculation AS
fdTestThis" which means I would then have to change the first line to:
SELECT a long calculation AS fdSortThis, "a long calculation AS
fdTestThis

OK this would work, but then I have my long calculation in two places
in the query which seems a bit inefficient, which is why I was trying
to sort by the bit after the "AS". Can anyone help please?

I hope that makes sense: I'm not very good at describing the problem,
sorry! :-)

John Spencer wrote:
There is a work-around that may be available and that is to refer to the
column number. Otherwise, you do have to repeat the calculation in the
order by clause. Even then, it is possible that Access Query Analyzer just
copies the referenced column to the Order By clause and you end up with no
actual savings in the execution of the query.

Understood and thanks for the idea. TBH, I didn't try referencing the
column number because I should have explained that it was
maintainability rather than speed that I was after and I only wanted
the "complex calculation" in one place. What I've done is pop the code
in a function instead as this seems to do the trick.

Cheers everyone.
M