Sort Expression (ASC Order)

T

Tom

I use the expression query below:

TotalSum: ([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1). The result is the
following:

0.36
0.22
0.20
0.36
0.17
0.23
0.24

I now select "Ascending" to sort the calculated numbers.

However, with the "Ascending" in place, this query becomes a parameter query
(w/o the ASC, it executes properly).

I'm sure there is a simple answer, but I can't think of it... so my question
is: How can I sort the expression query in ASC order (by "TotalSum").

Thanks,
Tom
 
D

Duane Hookom

Are GetPriorityPoints and GetCPLPoints both fields in the underlying tables?
Could you provide your full SQL View?
 
T

Tom

Duane:

Thanks for the prompt reply... please refer to the SQL below (this is the
actual one which contains 5 calculations vs. 2 listed in the original
thread).

*******************
SELECT tblMatrix.Priority, PriorityPoints([Priority]) AS GetPriorityPoints,
Left([CPLIdentifier],1) AS CPL, CPLPoints([CPL]) AS GetCPLPoints,
tblMatrix.Influence, InfluencePoints([Influence]) AS GetInfluencePoints,
tblMatrix.MilValue, MilValuePoints([MilValue]) AS GetMilValuePoints,
tblMatrix.BHPriority, BHPriorityPoints([BHPriority]) AS GetBHPriorityPoints,
([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1+[GetInfluencePoints]*0.1+[GetMil
ValuePoints]*0.3+[GetBHPriorityPoints]*0.1) AS TotalSum,
([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1) AS Test FROM tblMatrix;
*******************


Also, how do I use the "Format" function so that I always have 3 decimals.

I tried the following but I get an error indicating that it is "invalid date
format".
Test: (Format,([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1),#.###)


Thanks again,
Tom





Duane Hookom said:
Are GetPriorityPoints and GetCPLPoints both fields in the underlying tables?
Could you provide your full SQL View?

--
Duane Hookom
MS Access MVP


Tom said:
I use the expression query below:

TotalSum: ([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1). The result is the
following:

0.36
0.22
0.20
0.36
0.17
0.23
0.24

I now select "Ascending" to sort the calculated numbers.

However, with the "Ascending" in place, this query becomes a parameter query
(w/o the ASC, it executes properly).

I'm sure there is a simple answer, but I can't think of it... so my question
is: How can I sort the expression query in ASC order (by "TotalSum").

Thanks,
Tom
Click to expand...
Click to expand...
 
D

Duane Hookom

If you want to sort by a column, you can't use an aliased column within the
column expression. I never use a column alias in another column expression
in a query. Try something like:
SELECT
tblMatrix.Priority, PriorityPoints([Priority]) AS GetPriorityPoints,
Left([CPLIdentifier],1) AS CPL,
CPLPoints([CPL]) AS GetCPLPoints,
tblMatrix.Influence,
InfluencePoints([Influence]) AS GetInfluencePoints,
tblMatrix.MilValue,
MilValuePoints([MilValue]) AS GetMilValuePoints,
tblMatrix.BHPriority,
BHPriorityPoints([BHPriority]) AS GetBHPriorityPoints,
(PriorityPoints([Priority])*0.4+CPLPoints([CPL])*0.1+
InfluencePoints([Influence])*0.1+MilValuePoints([MilValue])*0.3+
BHPriorityPoints([BHPriority])*0.1) AS TotalSum,
(PriorityPoints([Priority]) *0.4+CPLPoints([CPL])*0.1) AS Test
FROM tblMatrix;


--
Duane Hookom
MS Access MVP
--

Tom said:
Duane:

Thanks for the prompt reply... please refer to the SQL below (this is the
actual one which contains 5 calculations vs. 2 listed in the original
thread).

*******************
SELECT tblMatrix.Priority, PriorityPoints([Priority]) AS GetPriorityPoints,
Left([CPLIdentifier],1) AS CPL, CPLPoints([CPL]) AS GetCPLPoints,
tblMatrix.Influence, InfluencePoints([Influence]) AS GetInfluencePoints,
tblMatrix.MilValue, MilValuePoints([MilValue]) AS GetMilValuePoints,
tblMatrix.BHPriority, BHPriorityPoints([BHPriority]) AS GetBHPriorityPoints,([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1+[GetInfluencePoints]*0.1+[GetMil
ValuePoints]*0.3+[GetBHPriorityPoints]*0.1) AS TotalSum,
([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1) AS Test FROM tblMatrix;
*******************


Also, how do I use the "Format" function so that I always have 3 decimals.

I tried the following but I get an error indicating that it is "invalid date
format".
Test: (Format,([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1),#.###)


Thanks again,
Tom





Duane Hookom said:
Are GetPriorityPoints and GetCPLPoints both fields in the underlying tables?
Could you provide your full SQL View?

--
Duane Hookom
MS Access MVP


Tom said:
I use the expression query below:

TotalSum: ([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1). The result is the
following:

0.36
0.22
0.20
0.36
0.17
0.23
0.24

I now select "Ascending" to sort the calculated numbers.

However, with the "Ascending" in place, this query becomes a parameter query
(w/o the ASC, it executes properly).

I'm sure there is a simple answer, but I can't think of it... so my question
is: How can I sort the expression query in ASC order (by "TotalSum").

Thanks,
Tom
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Duane's answered your first question. The answer to the second question is
that your Format statement is incorrect.

Test: Format([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1,"0.000")

(You could have used "#.###", but I believe that would give you 1.1, not
1.100)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tom said:
Duane:

Thanks for the prompt reply... please refer to the SQL below (this is the
actual one which contains 5 calculations vs. 2 listed in the original
thread).

*******************
SELECT tblMatrix.Priority, PriorityPoints([Priority]) AS GetPriorityPoints,
Left([CPLIdentifier],1) AS CPL, CPLPoints([CPL]) AS GetCPLPoints,
tblMatrix.Influence, InfluencePoints([Influence]) AS GetInfluencePoints,
tblMatrix.MilValue, MilValuePoints([MilValue]) AS GetMilValuePoints,
tblMatrix.BHPriority, BHPriorityPoints([BHPriority]) AS GetBHPriorityPoints,([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1+[GetInfluencePoints]*0.1+[GetMil
ValuePoints]*0.3+[GetBHPriorityPoints]*0.1) AS TotalSum,
([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1) AS Test FROM tblMatrix;
*******************


Also, how do I use the "Format" function so that I always have 3 decimals.

I tried the following but I get an error indicating that it is "invalid date
format".
Test: (Format,([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1),#.###)


Thanks again,
Tom





Duane Hookom said:
Are GetPriorityPoints and GetCPLPoints both fields in the underlying tables?
Could you provide your full SQL View?

--
Duane Hookom
MS Access MVP


Tom said:
I use the expression query below:

TotalSum: ([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1). The result is the
following:

0.36
0.22
0.20
0.36
0.17
0.23
0.24

I now select "Ascending" to sort the calculated numbers.

However, with the "Ascending" in place, this query becomes a parameter query
(w/o the ASC, it executes properly).

I'm sure there is a simple answer, but I can't think of it... so my question
is: How can I sort the expression query in ASC order (by "TotalSum").

Thanks,
Tom
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

Thanks, Doug, it works like a charm.

--
Thanks,
Tom


Douglas J. Steele said:
Duane's answered your first question. The answer to the second question is
that your Format statement is incorrect.

Test: Format([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1,"0.000")

(You could have used "#.###", but I believe that would give you 1.1, not
1.100)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tom said:
Duane:

Thanks for the prompt reply... please refer to the SQL below (this is the
actual one which contains 5 calculations vs. 2 listed in the original
thread).

*******************
SELECT tblMatrix.Priority, PriorityPoints([Priority]) AS GetPriorityPoints,
Left([CPLIdentifier],1) AS CPL, CPLPoints([CPL]) AS GetCPLPoints,
tblMatrix.Influence, InfluencePoints([Influence]) AS GetInfluencePoints,
tblMatrix.MilValue, MilValuePoints([MilValue]) AS GetMilValuePoints,
tblMatrix.BHPriority, BHPriorityPoints([BHPriority]) AS GetBHPriorityPoints,
Click to expand...
([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1+[GetInfluencePoints]*0.1+[GetMil
ValuePoints]*0.3+[GetBHPriorityPoints]*0.1) AS TotalSum,
([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1) AS Test FROM tblMatrix;
*******************


Also, how do I use the "Format" function so that I always have 3 decimals.

I tried the following but I get an error indicating that it is "invalid date
format".
Test: (Format,([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1),#.###)


Thanks again,
Tom





Duane Hookom said:
Are GetPriorityPoints and GetCPLPoints both fields in the underlying tables?
Could you provide your full SQL View?

--
Duane Hookom
MS Access MVP


I use the expression query below:

TotalSum: ([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1). The result
Click to expand...
is
the
following:

0.36
0.22
0.20
0.36
0.17
0.23
0.24

I now select "Ascending" to sort the calculated numbers.

However, with the "Ascending" in place, this query becomes a parameter
query
(w/o the ASC, it executes properly).

I'm sure there is a simple answer, but I can't think of it... so my
question
is: How can I sort the expression query in ASC order (by "TotalSum").

Thanks,
Tom
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

Duane,

I copied the code verbatim into the SQL window. I get the same error when
selecting ASC or DESC.

Is there anything I have overlooked?

--
Thanks,
Tom


Duane Hookom said:
If you want to sort by a column, you can't use an aliased column within the
column expression. I never use a column alias in another column expression
in a query. Try something like:
SELECT
tblMatrix.Priority, PriorityPoints([Priority]) AS GetPriorityPoints,
Left([CPLIdentifier],1) AS CPL,
CPLPoints([CPL]) AS GetCPLPoints,
tblMatrix.Influence,
InfluencePoints([Influence]) AS GetInfluencePoints,
tblMatrix.MilValue,
MilValuePoints([MilValue]) AS GetMilValuePoints,
tblMatrix.BHPriority,
BHPriorityPoints([BHPriority]) AS GetBHPriorityPoints,
(PriorityPoints([Priority])*0.4+CPLPoints([CPL])*0.1+
InfluencePoints([Influence])*0.1+MilValuePoints([MilValue])*0.3+
BHPriorityPoints([BHPriority])*0.1) AS TotalSum,
(PriorityPoints([Priority]) *0.4+CPLPoints([CPL])*0.1) AS Test
FROM tblMatrix;


--
Duane Hookom
MS Access MVP
--

Tom said:
Duane:

Thanks for the prompt reply... please refer to the SQL below (this is the
actual one which contains 5 calculations vs. 2 listed in the original
thread).

*******************
SELECT tblMatrix.Priority, PriorityPoints([Priority]) AS GetPriorityPoints,
Left([CPLIdentifier],1) AS CPL, CPLPoints([CPL]) AS GetCPLPoints,
tblMatrix.Influence, InfluencePoints([Influence]) AS GetInfluencePoints,
tblMatrix.MilValue, MilValuePoints([MilValue]) AS GetMilValuePoints,
tblMatrix.BHPriority, BHPriorityPoints([BHPriority]) AS GetBHPriorityPoints,
Click to expand...
([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1+[GetInfluencePoints]*0.1+[GetMil
ValuePoints]*0.3+[GetBHPriorityPoints]*0.1) AS TotalSum,
([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1) AS Test FROM tblMatrix;
*******************


Also, how do I use the "Format" function so that I always have 3 decimals.

I tried the following but I get an error indicating that it is "invalid date
format".
Test: (Format,([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1),#.###)


Thanks again,
Tom





Duane Hookom said:
Are GetPriorityPoints and GetCPLPoints both fields in the underlying tables?
Could you provide your full SQL View?

--
Duane Hookom
MS Access MVP


I use the expression query below:

TotalSum: ([GetPriorityPoints]*0.4+[GetCPLPoints]*0.1). The result
Click to expand...
is
the
following:

0.36
0.22
0.20
0.36
0.17
0.23
0.24

I now select "Ascending" to sort the calculated numbers.

However, with the "Ascending" in place, this query becomes a parameter
query
(w/o the ASC, it executes properly).

I'm sure there is a simple answer, but I can't think of it... so my
question
is: How can I sort the expression query in ASC order (by "TotalSum").

Thanks,
Tom
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Custom sort order 3
Form won't match query sort order 2
Can't get the code to sort the data properly. 1
Excel 2007 Sort Order not working 1
Date order on Pivot Table 0
Excel 2007 Sort Order Broken 6
Sort Month and Sort Day 1
Sort Expression 2

Top