Expression not Sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in my database that i built the expression to divide by 2
other fields to give me my answer. The only problem i am having is that it
will NOT sort by that field in descending order. WHY??? Is it because there
is an expression with a result? Is there a way around to get this to sort.
Please help.

Thanks!
 
Where is this field with the expression: table, query, form, or report?

What is the expression?

Will not sort or isn't sorting correctly?

If in a report, you can not depend on sorting from the table or query that
is the record source for the report. You must use sorting and grouping
options.

If the problem is not sorting properly and the data is doing something like
below, it's possible that your expression is turning the numbers into
text/strings and it is sorting correctly, for text that is.

3
29
2
11
1
 
Sandi said:
I have a field in my database that i built the expression to divide by 2
other fields to give me my answer. The only problem i am having is that it
will NOT sort by that field in descending order. WHY??? Is it because there
is an expression with a result? Is there a way around to get this to sort.
Click to expand...


You need to use the calculated expression in the query's
ORDER BY clause.

That probably isn't much help, so post a Copy/Paste of your
query's SQL view.
 
The field is on a report. The expression is: =[Line Haul[/[Miles]

I can not get the sort to work based on the answer in the expression on the
report. Is there a way to get the expression into a table or query?

The sorting and grouping option is on to sort this field in Decending order.
 
Sandi said:
The field is on a report. The expression is: =[Line Haul[/[Miles]

I can not get the sort to work based on the answer in the expression on the
report. Is there a way to get the expression into a table or query?

The sorting and grouping option is on to sort this field in Decending order.
Click to expand...


In a report, instead of using a control reference, use the
same expression in the report's Sorting and Grouping (View
menu).
 
I don't have a query built. I would need to create a query and then what?
What is the SQL View?
 
Sandi said:
I don't have a query built. I would need to create a query and then what?
What is the SQL View?
Click to expand...


Sorry, since this is the Queries newsgroup, I didn't think
your question was about a report.

I replied earlier, once I figured out the report aspect,
that you need to use the ecpression in the report's Sorting
and Grouping.
 
Ok - I put the expression in the Sorting and Grouping option and then when I
tried to view my report - it gave me an error saying Overflow. I'm not sure
of what that is?
 
Sandi said:
Ok - I put the expression in the Sorting and Grouping option and then when I
tried to view my report - it gave me an error saying Overflow. I'm not sure
of what that is?
Click to expand...


That means the result of the expression exceeds the size of
the datatype (in this case it should be a Double). You said
earlier that the expression is:
=[Line Haul] / [Miles]

If Miles is a very small number and LineHaul is pretty
large, then it's possible to get an overflow.

Normally, if Miles were 0, you would get a Division by Zero
error, so I don't think that's the problem.
 
I changed my data types so that they reflect Double and still receive the
Overflow. I must be missing something or doing something wrong.
--
Sandi Gardner


Marshall Barton said:
Sandi said:
Ok - I put the expression in the Sorting and Grouping option and then when I
tried to view my report - it gave me an error saying Overflow. I'm not sure
of what that is?
Click to expand...


That means the result of the expression exceeds the size of
the datatype (in this case it should be a Double). You said
earlier that the expression is:
=[Line Haul] / [Miles]

If Miles is a very small number and LineHaul is pretty
large, then it's possible to get an overflow.

Normally, if Miles were 0, you would get a Division by Zero
error, so I don't think that's the problem.
Click to expand...
 
Sandi said:
I changed my data types so that they reflect Double and still receive the
Overflow. I must be missing something or doing something wrong.
Click to expand...


That's odd, it's not easy to generate an OverFlow.

Can you isolate a record that causes the error and report
back the value in the two fields?
 
IF I recall correctly, you can sometimes get the overflow error when you
divide by zero. You should get a divide by zero error, but sometimes
you get this error instead.

Check for division by zero.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Now that is interesting, John. Of course I never get either
one so had no idea that could happen ;-)
 
That's not working either - i think i am actually going a little crazy ;-) I
guess i can export it to excel create a macro to run the formula and import
it back in as a last resort!
 
Sandi said:
That's not working either - i think i am actually going a little crazy ;-) I
guess i can export it to excel create a macro to run the formula and import
it back in as a last resort!
Click to expand...


OK, let's forget the sorting and grouping until we figure
out what's going on. If you don't already have them, add
three text boxes to the detail section.

Bind the first one to the Line Haul field.
Bind the second one to the Miles field.
Set the third one's control source to
=[Line Haul] / [Miles]

Then post back with the results
 
Back
Top