Overflow caused by 'Order By'

[Matt Greatorex]

This one has also been posted in the Queries forum, but I thought I might have more luck in this one.

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.

 

[selenau837]

This one has also been posted in the Queries forum, but I thought I might have more luck in this one.

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.

I had that same problem, and the only fix I could do was to recreate that form. Some how that form was corrupted and wouldnt' work at all. Until I recreated it from scratch.

 

[Matt Greatorex]

So, when you recreated the form, did your original 'Order By' statement work? Or did you need to change that, whilst recreating the rest of the form?

The form it's a part of is rather convoluted and recreating it would be a pain. If I can avoid that, by changing some syntax or something, I'd rather do that.

 

[selenau837]

So, when you recreated the form, did your original 'Order By' statement work? Or did you need to change that, whilst recreating the rest of the form?

The form it's a part of is rather convoluted and recreating it would be a pain. If I can avoid that, by changing some syntax or something, I'd rather do that.

Let me look at see deary.

Give me sec.

 

[selenau837]

So, when you recreated the form, did your original 'Order By' statement work? Or did you need to change that, whilst recreating the rest of the form?

The form it's a part of is rather convoluted and recreating it would be a pain. If I can avoid that, by changing some syntax or something, I'd rather do that.

Never mind, differnt problem. Sorry honey.

As for the overflow problem, have you watched the code run? It dies right there on the order by?

I can't see how changing the order to either Asc or Desc would affect it.


I would suggest posting a message box right here

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;"
[COLOR="SeaGreen"]msgBox (str_Source6) [/COLOR]
[Forms]![Top50]![cbo_Mntasts-Allasts].RowSource = str_Source6
[Forms]![Top50]![cbo_Mntasts-Allasts].Requery

So that you can see if the SQL is working properly.

Also, alot of times if I am getting an overflow, I change my datatype from Int to long. That helps me alot of times.

Hope I'm helping and not hendering...

 

[Matt Greatorex]

Found it!

Okay, I've now got a different problem to work with, but the cause of the issue was the fact that one of the fields on one of the records was zero. This caused an error when trying to use it in a division.

Thanks for the suggestions.

 

[selenau837]

Found it!

Okay, I've now got a different problem to work with, but the cause of the issue was the fact that one of the fields on one of the records was zero. This caused an error when trying to use it in a division.

Thanks for the suggestions.

Yup division by zero will get you every time.