Access VBA - print last record from SQL Query

Question

Hey

I have the following SQL Query I'm executing and I'm trying to find why it returns the error 'overflow' when running the query.

Now I want to print the last record that it computes before going into overflow, is this possible using MS Access VBA?

Private Sub Command0_Click()
Dim sql As String
Dim rs As DAO.Recordset
Dim db As DAO.Database

    Set db = CurrentDb()

    sql = "SELECT DatumNaarWeeknummer([tbl_ArtikelVerwijderdUitZaaglijst]![RegistratieDatum]) AS WeeknummerGezaagdeOmzet, " _
    & "Sum([TotaalPrijs]/([tbl_ArtikelsPerOrder]![Aantal]*[Totaal])*[tbl_ArtikelVerwijderdUitZaaglijst]![Aantal]) AS GezaagdeOmzet " _
    & "FROM (((tbl_ArtikelsPerOrder LEFT JOIN qry_Actieve_Orders ON tbl_ArtikelsPerOrder.OrderID = qry_Actieve_Orders.OrderID) LEFT JOIN qry_ArtikelPerOrderID_EenheidsPrijsBijFranco ON tbl_ArtikelsPerOrder.ArtikelsPerOrderID = qry_ArtikelPerOrderID_EenheidsPrijsBijFranco.ArtikelsPerOrderID) " _
    & "LEFT JOIN qry_AantalArtikelTypesPerArtikelPerOrder ON tbl_ArtikelsPerOrder.ArtikelsPerOrderID = qry_AantalArtikelTypesPerArtikelPerOrder.ArtikelsPerOrderID) " _
    & "RIGHT JOIN tbl_ArtikelVerwijderdUitZaaglijst ON tbl_ArtikelsPerOrder.ArtikelsPerOrderID = tbl_ArtikelVerwijderdUitZaaglijst.ArtikelsPerOrderID " _
    & "GROUP BY DatumNaarWeeknummer([tbl_ArtikelVerwijderdUitZaaglijst]![RegistratieDatum]);"

    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    End Sub

Answer 1

My guess is that this expression:

Sum([TotaalPrijs]/([tbl_ArtikelsPerOrder]![Aantal]*[Totaal])*[tbl_ArtikelVerwijderdUitZaaglijst]![Aantal])

produces an numeric overflow for some records. No knowing what data types your columns use, I can only recommend trying to convert them to a "bigger" data type during the calculation.

Answer 2

(Edit: rearranged to focus on likely culprit)

No, you can't get the last record easily. You can try Select Top 5000 . . . etc., raise the value if it works, and lower the value if it doesn't, and zero in on it that way. But, it is unlikely that a particular is record is causing the problem. I don't think there's bad data somewhere. It's the query.

Focus on the Sum in the select query. Take that out, and you'll likely have the query work. It could well be that the sum overwhelms the numeric type that sql is using to add your values. Actually, the more I think about it, this is probably it. Yeah. If it is, you'll need to force that to a type that can handle larger numbers, like this:

SELECT blah blah, SUM(CAST([TotaalPrijs]/([tbl_ArtikelsPerOrder]![Aantal]*[Totaal])*[tbl_ArtikelVerwijderdUitZaaglijst]![Aantal] AS DECIMAL)) AS GezaagdeOmzet

The syntax might be slightly different for MSAccess, but it will be something like that. Being Access, the default might be int, in which case you might be able to specify Long. Otherwise, specify Decimal. Try to avoid the Real numbers if you can (single, etc.) and they can mess you up if you're not careful.

Though less likely, here are some other possible culprits:

• Are you sure this query is logically correct? This query can be caused by too large a result set being returned. Use the Select Top 1000 etc. syntax, and analyze the results to make sure your joins are working as you wish, and aren't mistakenly causing cartesian results, for example.
• If your query is returning legitimate results, then might it be that the legitimate result are too massive? If you really should be getting a billion results, and this is too much, then you'll have to change your whole strategy, or reduce the columns being returned, etc.

Answer 3

Is it possible that the part of the query

([tbl_ArtikelsPerOrder]![Aantal]*[Totaal])*[tbl_ArtikelVerwijderdUitZaaglijst]![Aantal])

Is returning 0? If so that would cause an error, so it might not be large data that is at fault but data that is too small or non-existent