Median module run-time error

[jnixon]

Hello all, I found this module for coming up with a median value somehow through this forum, although I can't find the thread, and someone might have actually sent me a db file with the module in it. Anyway, I need to add something in here that will prevent a run-time error when there's no records to calculate median value. I'm not good with visual basic, so I was hoping someone could help me here. Here's the code:

Public Function PercVData(TblName, FldName, Perc)
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim arrayNum
    
   Set db = CurrentDb
   Set rs = db.OpenRecordset("SELECT [" & FldName & "]" & _
            " FROM [" & TblName & "]" & _
            " WHERE [" & FldName & "] IS NOT NULL")
   
   rs.MoveLast
   rs.MoveFirst
   arrayNum = rs.GetRows(rs.RecordCount)
     
   PercVData = WorksheetFunction.Percentile(arrayNum, Perc)
   
   Set db = Nothing
   Set rs = Nothing
    
End Function

So...What I'm using this for...I have a big table of houses with their last sale dates and prices, and latitude/longitude for each house. We put in one of the addresses, and it looks up the lat/long, pulls up to 5 houses within half a mile of the subject (calculates the distance on lat/long), and returns the median value. Each subject we put it, it does this a few different ways with different calculations, so it actually takes two passes with the median and comparable properties. But, say for instance there are no comparable properties within half a mile, then the median has nothing to calculate, and instead of returning a zero value, I get a run-time error. This is a big headache, because I could be running batches of 100 to 5,000 properties through this database and storing the results, and the error stops the whole batch process. Please help me! Thank you so much in advance.

 

[RuralGuy]

Try this:

Public Function PercVData(TblName, FldName, Perc)
   Dim rs As DAO.Recordset
   Dim arrayNum
    
   Set rs = CurrentDb.OpenRecordset("SELECT [" & FldName & "]" & _
            " FROM [" & TblName & "]" & _
            " WHERE [" & FldName & "] IS NOT NULL")
   With rs
      If .RecordCount > 0 Then
         .MoveLast
         .MoveFirst
         arrayNum = .GetRows(.RecordCount)
           
         PercVData = WorksheetFunction.Percentile(arrayNum, Perc)
      Else
         MsgBox "No records to process"
      End If
      .Close
   End With
   
   Set rs = Nothing
End Function

 

[jnixon]

RG, you always seem to be there when I need ya. Thanks. That worked, sorta. After the "No records to process" message box, it said "Data type mismatch in criteria expression", and then stops and doesn't continue on to the second part of the process.

Another thing is that I don't want a message box or anything to pop up. If I'm running through 5,000 records through a calculation with comparables, I want to just set it up and walk away. So, it needs to return a zero, or a null value, or something like that because it's just going to append the record to a table called 'Results', and then move on to the next subject address. Any ideas?

 

[RuralGuy]

Just change this line:
MsgBox "No records to process"
to:
PercVData = 0

 

[jnixon]

Great minds think alike, RG. I realized that before lunch, and it actually helped me find the null value returned that was causing my data type mismatch error. Thank you so much for your help!

 

[RuralGuy]

Glad I could help.