Overflow error

[rowardHoark]

The error is caused by a function which displays the value in a text box.

I have tried to change data types, CLng() and Round(..,1) with no success.

Also, the error occurs only in some records (displayed as reports).

The code of the function:

Public Function temperaturePrediction(Report_number As Integer)
 
    'Creating the variables to be used
    Dim STLT As Integer 'sum(Temperature*ln(Time)
    Dim SLT As Integer 'sum(ln(Time))
    Dim ST As Integer 'sum(Time)
    Dim SLT2 As Integer 'sum((ln(Time)^2))
    Dim n As Integer 'the number of data points
 
    'Pupulating all the variables with values from queTeArrheniusPlot2 query
    STLT = DLookup("[sumOfTemperatureTimesLogOfTime]", "queTeArrheniusPlot2", "[Report_number] = " & Report_number)
    SLT = DLookup("[sumOfLogOfTime]", "queTeArrheniusPlot2", "[Report_number] = " & Report_number)
    ST = DLookup("[sumOfTime]", "queTeArrheniusPlot2", "[Report_number] = " & Report_number)
    SLT2 = DLookup("[sumOfLogOfTimePowerOf2]", "queTeArrheniusPlot2", "[Report_number] = " & Report_number)
    n = DLookup("[CountOfReport_number]", "queTeArrheniusPlot2", "[Report_number] = " & Report_number)
 
    'Rounding up the values from the query
    STLT = Round(STLT, 0)
    SLT = Round(SLT, 0)
    ST = Round(ST, 0)
    SLT2 = Round(SLT2, 0)
 
    'This bit performs the actual linear regression and prediction
 
    'y=a+b*ln(x), where y is temperature and x is time
 
    Dim a As Integer
    Dim b As Integer
 
    b = (n * STLT - ST * SLT) / (n * SLT2 - SLT2)
    a = (ST - b * SLT) / n
 
    temperaturePrediction = Round(a + b * Log(20000), 0)
 
End Function

 

[DCrake]

You cluld improve the efficiency by changing

This

    STLT = DLookup("[sumOfTemperatureTimesLogOfTime]", "queTeArrheniusPlot2", "[Report_number] = " & Report_number)
    SLT = DLookup("[sumOfLogOfTime]", "queTeArrheniusPlot2", "[Report_number] = " & Report_number)
    ST = DLookup("[sumOfTime]", "queTeArrheniusPlot2", "[Report_number] = " & Report_number)
    SLT2 = DLookup("[sumOfLogOfTimePowerOf2]", "queTeArrheniusPlot2", "[Report_number] = " & Report_number)
    n = DLookup("[CountOfReport_number]", "queTeArrheniusPlot2", "[Report_number] = " & Report_number)

To This

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Select *  From queTeArrheniusPlot2 Where [Report_number] = " & Report_number)

   STLT Rs("sumOfTemperatureTimesLogOfTime")
   SLT = Rs("sumOfLogOfTime")
   ST = Rs("sumOfLogOfTimePowerOf2")
   STLT2 = Rs("sumOfLogOfTimePowerOf2")
   N = Rs("CountOfReport_number")
Rs.Close
Set Rs = Nothing

 

[DJkarl]

You are declaring your variables as Integers which have a range of -32,768 to 32,767 (16-bit number). If you use CLng() (32-bit) I'm not surprised you are getting Overflow errors, try using longs or doubles for your variables.