By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,967 Members | 1,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,967 IT Pros & Developers. It's quick & easy.

Modifying VBA

P: 78
I have this code I use for median:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.  
  5.  
  6. Function DMedian(tName As String, fldName As String) As Single
  7.   Dim MedianDB As DAO.Database
  8.   Dim ssMedian As DAO.Recordset
  9.   Dim RCount As Integer, i As Integer, x As Double, y As Double, _
  10.       OffSet As Integer
  11.   Set MedianDB = CurrentDb()
  12.   Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
  13.             "] FROM [" & tName & "] WHERE [" & fldName & _
  14.             "] IS NOT NULL ORDER BY [" & fldName & "];")
  15.   'NOTE: To include nulls when calculating the median value, omit
  16.   'WHERE [" & fldName & "] IS NOT NULL from the example.
  17.   ssMedian.MoveLast
  18.   RCount% = ssMedian.RecordCount
  19.   x = RCount Mod 2
  20.   If x <> 0 Then
  21.      OffSet = ((RCount + 1) / 2) - 2
  22.      For i% = 0 To OffSet
  23.         ssMedian.MovePrevious
  24.      Next i
  25.      DMedian = ssMedian(fldName)
  26.   Else
  27.      OffSet = (RCount / 2) - 2
  28.      For i = 0 To OffSet
  29.         ssMedian.MovePrevious
  30.      Next i
  31.      x = ssMedian(fldName)
  32.      ssMedian.MovePrevious
  33.      y = ssMedian(fldName)
  34.      DMedian = (x + y) / 2
  35.   End If
  36.   If Not ssMedian Is Nothing Then
  37.      ssMedian.Close
  38.      Set ssMedian = Nothing
  39.   End If
  40.   Set MedianDB = Nothing
  41. End Function
  42.  
  43.  
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.
Dec 22 '11 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,421
Check the record count before continuing with the code.
Dec 22 '11 #2

P: 78
Good Idea.

My debug stops at this: line 17
Expand|Select|Wrap|Line Numbers
  1.  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?

Expand|Select|Wrap|Line Numbers
  1. IIf(RecordCount > 0, rest of code, "NONE") 
Am I close?
Dec 22 '11 #3

NeoPa
Expert Mod 15k+
P: 31,709
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.
Dec 22 '11 #4

ADezii
Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb
  5. Set rst = MyDB.OpenRecordset("tblTest", dbOpenDynaset)
  6.  
  7. With rst
  8.   If (.BOF And .EOF) Then
  9.     MsgBox "No Records to analyze"
  10.       Exit Sub
  11.   Else
  12.     Do Until .EOF
  13.       Debug.Print ![lastname]
  14.         .MoveNext
  15.     Loop
  16.   End If
  17. End With
  18.  
  19. rst.Close
  20. Set rst = Nothing
Dec 23 '11 #5

P: 78
So I should add this above line 17?

Expand|Select|Wrap|Line Numbers
  1. With ssMedian 
  2.   If (.BOF And .EOF) Then 
  3.     Set DMedian to "NONE" 
  4.       Exit Sub 
  5.   Else 
  6.     Do Until .EOF 
  7.               .MoveNext 
  8.     Loop 
  9.   End If 
  10. End With 
  11.  
Dec 23 '11 #6

ADezii
Expert 5K+
P: 8,679
It is simply another option for you to use, should you take this approach, that is logically where the Code Segment should be placed.
Dec 23 '11 #7

P: 78
I added this above line 17 from post 1.

Expand|Select|Wrap|Line Numbers
  1.  With ssMedian
  2.   If (.BOF And .EOF) Then
  3.     DMedian = "NONE"
  4.         End Function
  5.   Else
  6.     Do Until .EOF
  7.               .MoveNext
  8.     Loop
  9.   End If
  10. End With
I get an error of: "Block If without End If"

Any ideas? It looks like the statement is closed.
Dec 27 '11 #8

Expert Mod 2.5K+
P: 2,545
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
Dec 27 '11 #9

P: 78
Thanks, Stewart! That solved the issue. Thanks also for the data type concern in returning a string. I have decided to go with "0".
Dec 27 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.