Modifying VBA

[wisni1rr]

I have this code I use for median:

Option Compare Database
Option Explicit
 
 
 
Function DMedian(tName As String, fldName As String) As Single
  Dim MedianDB As DAO.Database
  Dim ssMedian As DAO.Recordset
  Dim RCount As Integer, i As Integer, x As Double, y As Double, _
      OffSet As Integer
  Set MedianDB = CurrentDb()
  Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
            "] FROM [" & tName & "] WHERE [" & fldName & _
            "] IS NOT NULL ORDER BY [" & fldName & "];")
  'NOTE: To include nulls when calculating the median value, omit
  'WHERE [" & fldName & "] IS NOT NULL from the example.
  ssMedian.MoveLast
  RCount% = ssMedian.RecordCount
  x = RCount Mod 2
  If x <> 0 Then
     OffSet = ((RCount + 1) / 2) - 2
     For i% = 0 To OffSet
        ssMedian.MovePrevious
     Next i
     DMedian = ssMedian(fldName)
  Else
     OffSet = (RCount / 2) - 2
     For i = 0 To OffSet
        ssMedian.MovePrevious
     Next i
     x = ssMedian(fldName)
     ssMedian.MovePrevious
     y = ssMedian(fldName)
     DMedian = (x + y) / 2
  End If
  If Not ssMedian Is Nothing Then
     ssMedian.Close
     Set ssMedian = Nothing
  End If
  Set MedianDB = Nothing
End Function
 
 

I was wondering how I could add a statement that will check to see if there are records to compute and if not simply return the string "NONE". Currently the code will have runtime errors is there are no values to calculate.

[Rabbit]

Check the record count before continuing with the code.

[wisni1rr]

Good Idea.

My debug stops at this: line 17

 ssMedian.MoveLast

I'm by no means a pro at programming. My guess is that I will insert an IIf statement above that line of code. Would it be like this?

IIf(RecordCount > 0, rest of code, "NONE") 

Am I close?

[NeoPa]

Wisni1rr:
Am I close?

Very.

Unfortunately, there are situations where RecordCount is not set correctly immediately after the Recordset is opened, so test using EOF instead.

[ADezii]

As a side note, you do not need RecordCount, and you do not need to traverse a Recordset to get an accurate count. A more suble way is to test and see if the EOF and BOF a Recordset both evaluate to True, in which case the Recordset will definitely contain Records. A simple example will illustrate this point:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblTest", dbOpenDynaset)
 
With rst
  If (.BOF And .EOF) Then
    MsgBox "No Records to analyze"
      Exit Sub
  Else
    Do Until .EOF
      Debug.Print ![lastname]
        .MoveNext
    Loop
  End If
End With
 
rst.Close
Set rst = Nothing

[wisni1rr]

So I should add this above line 17?

With ssMedian 
  If (.BOF And .EOF) Then 
    Set DMedian to "NONE" 
      Exit Sub 
  Else 
    Do Until .EOF 
              .MoveNext 
    Loop 
  End If 
End With 
 

[ADezii]

It is simply another option for you to use, should you take this approach, that is logically where the Code Segment should be placed.

[wisni1rr]

I added this above line 17 from post 1.

 With ssMedian
  If (.BOF And .EOF) Then
    DMedian = "NONE"
        End Function
  Else
    Do Until .EOF
              .MoveNext
    Loop
  End If
End With

I get an error of: "Block If without End If"

Any ideas? It looks like the statement is closed.

[Stewart Ross]

Line 4 above should read

Exit Function

not

End Function

Please note also that in line 3 you cannot return a text value - the function is defined as returning a single-precision number. You would need to return a 0 or some such 'safe' value consistent with the function's defined type.

-Stewart

[wisni1rr]

Thanks, Stewart! That solved the issue. Thanks also for the data type concern in returning a string. I have decided to go with "0".