MAX Function

Question

I am trying to get the Max value of a column and this is not working.  Can someone plz help?  The code is as follows:

       Set rstDlrVehiclesFnd = db.OpenRecordset("SELECT MAX(tblDlrVehicles.MsrpNoShipping)  FROM tblDlrVehicles WHERE Model = '" & strModel & "' AND MakeCar = '" & strMake & "' AND YearCar = " & lngYearCar & " ORDER BY MakeCar, YearCar, Model;", dbOpenDynaset).

  I am getting a 3122 error when I use this code.  Yet this code is just fine.

        Set rstDlrVehiclesFnd = db.OpenRecordset("SELECT tblDlrVehicles.MsrpNoShipping  FROM tblDlrVehicles WHERE Model = '" & strModel & "' AND MakeCar = '" & strMake & "' AND YearCar = " & lngYearCar & " ORDER BY MakeCar, YearCar, Model;", dbOpenDynaset)

Any assistance would be great!

Answer 1

Take off the "ORDER BY..." part.  This code should only return a single row anyway.

-Bruce

Answer 2

This fixed me getting the record, yet I am not getting the value.  Looked at several links online and I am not seeing why I am not getting the value.  It is taking the correct path, but the value for intCarCnt is empty!

The code is as follows now:

            

        Set rstDlrVehiclesFnd = db.OpenRecordset("SELECT MAX(tblDlrVehicles.CarCnt) as intCarCnt FROM tblDlrVehicles WHERE Model = '" & strModel & "' AND MakeCar = '" & strMake & "' AND YearCar = " & lngYearCar & ";", dbOpenDynaset)

        If rstDlrVehiclesFnd.BOF And rstDlrVehiclesFnd.EOF Then
            cntVehicle = 1
        Else
            cntVehicle = intCarCnt + 1
        End If

It *.BOF and *.EOF are both false, which is what I expected, but when I look at intCarCnt  it shows as 'empty" when I watch it.  I am not getting what I am doing wrong.  Can someone see it?

Answer 3

In the code you've given, intCarCnt is an implicitly declared variant (a variable you probably did not mean to declare).  What you probably meant to refer to in your code is the value of the field intCarCnt, i.e:

 If rstDlrVehiclesFnd.BOF And rstDlrVehiclesFnd.EOF Then
            cntVehicle = 1
        Else
            cntVehicle = rstDlrVehicles!intCarCnt + 1 ' or alternatively rstDlrVehicles.Fields("intCarCnt")
        End If

To avoid this sort of confusion add "Option Explicit" at the beginning of your code module (look up "Option Explicit Statement" in VBA help)

-Bruce

Answer 4

       Set rstDlrVehiclesFnd = db.OpenRecordset("SELECT MAX(tblDlrVehicles.MsrpNoShipping)  FROM tblDlrVehicles WHERE Model = '" & strModel & "' AND MakeCar = '" & strMake & "' AND YearCar = " & lngYearCar & " ORDER BY MakeCar, YearCar, Model;", dbOpenDynaset).

Hi ballj,

To "see" that MAX-value you could use something like:

Max_value = db.OpenRecordset("SELECT MAX(tblDlrVehicles.MsrpNoShipping) AS Max_shipping FROM tblDlrVehicles WHERE Model = '" & strModel & "' AND MakeCar = '" & strMake & "' AND YearCar= " & lngYearCar, dbOpenDynaset)!Max_shipping

Imb.

Answer 5

Why not just call the DMax function?

Dim strModel  As String
Dim MakeCar  As String
Dim strCriteria As String
Dim lngYearCar As Long
Dim curMaxValue As Currency

' get values of strModel, strMake and lngYearCar from somewhere

strCriteria = "Model = """ & strModel & """ And " & _
    "MakeCar = """ & strMake & """ And " & _
    "YearCar  = " & lngYearCar

curMaxValue  = DMax("MsrpNoShipping", "tblDlrVehicles", strCriteria)

Benchmarking has generally shown that, to return a single value, calling a domain function is more efficient than establishing a recordset object.

---

Ken Sheridan, Stafford, England

Answer 6

Everyone,

  I appreciate your responses and answers to my question.  I testes each of them and I must say, they all work!  I went with the one from Ken, based on his comment at the end, since I am modifying this system and I want it to be as efficient as I can.  Thanx everyone and I have learned how to do things better.

Jerry

Answer 7

It's always been my understanding that the inverse of this is true, i.e. that generating a recordset was generally more efficient than using the domain aggregate functions.  I know this was true in "the old days" of Access 97 where lots of people wrote their own replacements for domain aggregate functions but I think that the gap has narrowed enough for the generic case that using the D* functions is perfectly adequate.  Recent benchmarks I've seen (mostly for DCount()) still indicate that generating a recordset of some sort is still most efficient but not enough to matter.  To be completely sure about specific situations and if performance really matters one should test and compare different methods to see what is most efficient and fits best.  Using domain aggregate functions certainly results in less code and less cleanup.  This in and of itself carries a lot of value and to my mind puts Ken's solution at the top.

-Bruce

Answer 8

Benchmarking has generally shown that, to return a single value, calling a domain function is more efficient than establishing a recordset object.

Hi all,

I was triggered by the above remark, because - as Bruce - I had an opposite opinion.

So I did a little "benchmarking" an a mediate complicated query. In this query the sequencenumber of the children of a father are analysed for the highest number that is a son. The population consist of about 13,000 people.

I compared three different methods:
1. result = DMax(…)
2. result = CurrentDB.OpenRecordset(sql_string)!Max_nr
3, cur_set = CurrentDB.OpenRecordset(sql_string)
    result = cur_set!Max_nr
    cur_set.Close

For this test I used the following code:

Sub Snelheid()
  Dim time1 As Date
  Dim time2 As Date
  Dim time3 As Date
  Dim diff1 As Integer
  Dim diff2 As Integer
  Dim diff3 As Integer
  Dim cur_set As Recordset
  
  Dim aantal As Integer
  Dim x As Integer
  
'glo_stop
  aantal = 2000
  
  time1 = Now
  For x = 1 To aantal
    jan = DMax("Volgnummer_vader", "Persoon_tbl", "Geslacht = 'M'")
  Next
  time2 = Now
  diff1 = 86400 * time2 - 86400 * time1
   
  time1 = Now
  For x = 1 To aantal
    jan = CurrentDb.OpenRecordset("SELECT MAX(Volgnummer_vader) AS Max_nr FROM Persoon_tbl WHERE Geslacht = 'M'")!Max_nr
  Next
  time2 = Now
  diff2 = 86400 * time2 - 86400 * time1
  
  time1 = Now
  For x = 1 To aantal
    Set cur_set = CurrentDb.OpenRecordset("SELECT MAX(Volgnummer_vader) AS Max_nr FROM Persoon_tbl WHERE Geslacht = 'M'")
    jan = cur_set!Max_nr
    cur_set.Close
  Next
  time2 = Now
  diff3 = 86400 * time2 - 86400 * time1
  
  Msg_form1 "X", diff1, diff2, diff3
  
End Sub

The result in seconds is (btw the Max_nr seemed to be 17 in this dataset):

From this result Method 1 is less efficient by about 10%.

Method 3 takes a little additional time for "extra code and extra cleanup".

So - in this example - the most efficient way is method 2.

Imb.

Answer 9

Hello Imb-hb,

This is quite an interesting academic exercise.  I am curious if replacing CurrentDb with DBEngine(0)(0) has a noticeable effect (should be somewhat faster for methods 2 and 3) and if using DBEngine(0)(0).OpenRecordset(sql, dbOpenForwardOnly, dbReadonly) has any effect.

-Bruce

Answer 10

This is quite an interesting academic exercise.  I am curious if replacing CurrentDb with DBEngine(0)(0) has a noticeable effect (should be somewhat faster for methods 2 and 3) and if using DBEngine(0)(0).OpenRecordset(sql, dbOpenForwardOnly, dbReadonly) has any effect.

Hi Bruce,

I added a 4th method, a copy of method 2, where I replaced  CurrenDb  with  DBEngine(0)(0).

Results:

So, using DBEngine(0)(0) is a valid syntax, and runs marginally faster; DMax still uses about 10% more time.

I am still in favour of:    CurrentDb.Openrecordset(sql_string)!My_field. This perfectly fits in my way of working where I generate dynamically the sql_strings to be used in generalized forms.

Imb.

Answer 11

Interesting.  Thanks for taking the time to benchmark this!

-Bruce

Answer 12

Interesting.  Thanks for taking the time to benchmark this!

Hi Bruce,

In a new test I also added method 5 with the parameters    dbOpenForwardOnly, dbReadonly    to the OpenRecordset.

This had hardly any effect on the total time. In fact it increased the time! I think this is an artefact of the measurement. The recordset returns only one record, and it does not make much difference if that one record is readonly or not.

Further, the processing time of all 5 methods takes about 5 minutes. In that time there is, without doubt, an influence of other processes running in the background with respect to the availability of CPU.

Imb.

Answer 13

Hi Bruce,

I ran a few more cycles to get an idea of the reproducability.

For the methods 1 to 5 this is:

65 - 59 - 59 - 59 - 62
65 - 59 - 59 - 59 - 62
65 - 59 - 59 - 58 - 63

The methods 2, 3 and 4 are comparable efficient, the methods 1 and 5 are consistently less efficient.

Imb.