Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

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

    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

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    How about this:

    datMaximum = DMax("ExDay","qryA")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    rvWoody (2013-04-01)

  4. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    @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.

  5. #4
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    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.

  6. The Following User Says Thank You to Cronk For This Useful Post:

    rvWoody (2013-04-01)

  7. #5
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Thumbs up

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •