How to convert a single field recordset into a VBA variable?

Topic

New Reply

What is the most efficient way to convert a recordset that contains only one field into a VBA variable. In the following example I ‘m looking for the highest expiration date in an access table. This obviously returns a single value. But the subsequent code I use is rather elaborate to assign that valu to a VBA variable. Can it be done in another way?

Dim datMaximum As Date
Dim cmdADO As ADODB.Command
Set cmdADO = New ADODB.Command

With cmdADO
.ActiveConnection = CurrentProject.Connection
.CommandText = “SELECT TOP 1 qryA.ExpDate as ExDay FROM qryA ” & _
“ORDER BY qryA.ExpDate DESC”
End With

Dim rsVD As ADODB.Recordset
Set rsVD = New ADODB.Recordset
rsVD.Open cmdADO, , adOpenKeyset, adLockOptimistic

With rsVD
Select Case Not (.EOF And .BOF)
Case True
.MoveFirst
datMaximum = .Fields(“ExDay”)
.Close
Set rsVD = Nothing
Case False
‘no records found
Exit Sub
End Select
End With

Reply | Quote

Replies

How about this:

datMaximum = DMax(“ExDay”,”qryA”)

Reply | Quote

@MarkLiquourman: I’m not sure your suggestions works. As far as I understand, the DMAX function requires three arguments, one of which is an Excel range that represents the “database”. Also, your method would be specific to finding a Maximum value and I would prefer a method that is generally applicable to cases where only one field is returned.

Further investigation into the problem led me to following simplification: (only relevant code is shown: the command object stays as it was)

Dim rsVD As ADODB.Recordset
Set rsVD = New ADODB.Recordset
With rsVD
.Open cmdADO, , adOpenKeyset, adLockOptimistic
datMaximum = CDate(.GetString)
.Close
Set rsVD = Nothing
End With

The Cdate function is not really necessary in this case, but added for generallity.

Reply | Quote

I’m sure it will work.
datMaximum = DMax(“ExDay”,”qryA”)
After executing this, datMax will contain the maximum value in the field ExDay in the data table or query qryA. Doesn’t matter if there is one field or many.

Reply | Quote

:cool:My apologies to MarkLiquorman, Cronk: Yes, indeed, it did work (however unexpected to me). I was totally unaware of Access’ DMAX function.
Correct syntax was: datMaximum=DMax(“ExpDate”,”qryA”)

The beauty is that if the query itself returns only one value, then by definition it’s the maximum value. In which case I could also us DMin. :D:

Reply | Quote