Overflow caused by 'Order By'

[Matt Greatorex]

I've found one thread on this forum which appeared to cover the problem I'm having, but it was from a few years ago and stopped rather abruptly.

I have a combo box with five fields. The row source for the box is shown below and includes a value that I obtain from dividing one figure by another. All is well until I try to order the box's contents by this value, at which time I'm getting an 'Overflow' message.

The full query is as follows, with the problem area in itallics.

str_Source6 = "SELECT TOP " & li_Top & " FormatPercent((rogerreport.mntasts/rogerreport.allasts),2), " & _
"rogerreport.ria, " & _
"tbl_iainfo.IAFirstName, " & _
"tbl_iainfo.IALastname, " & _
"tbl_iainfo.NesbittBr " & _
"FROM rogerreport INNER JOIN tbl_iainfo " & _
"ON rogerreport.ria = tbl_iainfo.IACode " & _
"WHERE ([Period] >= #" & CDate(cbo_startdate) & "# " & _
"and [Period] <= #" & CDate(cbo_enddate) & "#) " & _
"ORDER BY (rogerreport.mntasts/rogerreport.allasts) DESC;"
[Forms]![Top50]![cbo_Mntasts-Allasts].RowSource = str_Source6
[Forms]![Top50]![cbo_Mntasts-Allasts].Requery

If anyone has any suggestions on what to alter to order the results correctly I would be very appreciative.

 

[namliam]

I think the problem is not related to your order by but rather by the length of the total SQL.

Try making a default query out of it (fixed within the database) and use that as your recordsource, see if that works....

 

[kleky]

I'd break each part of the SQL down and run them separately to see what kind of numbers I get. Any #Errors from the division can cause the overflow, as logically how can you order an error?