435,396 Members | 2,501 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,396 IT Pros & Developers. It's quick & easy.

Median Query

 P: n/a Can I get the following function to work with Fieldnames that have spaces in it? I've tried spqaure brackets to no avail. Any help would be appreciated. Public Function DMedian(FieldName As String, _ TableName As String, _ Optional Criteria As Variant) As Double On Error GoTo Err_DMedian 'Returns the median of a given field in a given table. 'Returns -1 if no recordset is created Dim conn As Connection Dim rs As New ADODB.Recordset Dim strSQL As String Dim RowCount As Long Dim LowMedian As Double, HighMedian As Double 'Open a recordset on the table. Set conn = CurrentProject.Connection strSQL = "SELECT " & FieldName & " FROM " & TableName If Not IsMissing(Criteria) Then strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & FieldName Else strSQL = strSQL & " ORDER BY " & FieldName End If ' Debug.Print strSQL rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic 'Find the number of rows in the table. rs.MoveLast RowCount = rs.RecordCount rs.MoveFirst If RowCount Mod 2 = 0 Then 'There is an even number of records. Determine the low and high 'values in the middle and average them. rs.Move Int(RowCount / 2) - 1 LowMedian = rs(FieldName) rs.Move 1 HighMedian = rs(FieldName) DMedian = (LowMedian + HighMedian) / 2 Else 'There is an odd number of records. Return the value exactly in 'the middle. rs.Move Int(RowCount / 2) DMedian = rs(FieldName) End If Exit_DMedian: Exit Function Err_DMedian: If Err.Number = 3075 Then DMedian = 0 Resume Exit_DMedian ElseIf Err.Number = 3021 Then 'EOF or BOF ie no recordset created DMedian = -1 Resume Exit_DMedian Else MsgBox Err.Description Resume Exit_DMedian End If End Function Nov 13 '05 #1
3 Replies

 P: n/a gu********@gmail.com wrote: Can I get the following function to work with Fieldnames that have spaces in it? I've tried spqaure brackets to no avail. Any help would be appreciated. Public Function DMedian(FieldName As String, _ TableName As String, _ Optional Criteria As Variant) As Double On Error GoTo Err_DMedian 'Returns the median of a given field in a given table. 'Returns -1 if no recordset is created Dim conn As Connection Dim rs As New ADODB.Recordset Dim strSQL As String Dim RowCount As Long Dim LowMedian As Double, HighMedian As Double 'Open a recordset on the table. Set conn = CurrentProject.Connection strSQL = "SELECT " & FieldName & " FROM " & TableName If Not IsMissing(Criteria) Then strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & FieldName Else strSQL = strSQL & " ORDER BY " & FieldName End If ' Debug.Print strSQL rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic 'Find the number of rows in the table. rs.MoveLast RowCount = rs.RecordCount rs.MoveFirst If RowCount Mod 2 = 0 Then 'There is an even number of records. Determine the low and high 'values in the middle and average them. rs.Move Int(RowCount / 2) - 1 LowMedian = rs(FieldName) rs.Move 1 HighMedian = rs(FieldName) DMedian = (LowMedian + HighMedian) / 2 Else 'There is an odd number of records. Return the value exactly in 'the middle. rs.Move Int(RowCount / 2) DMedian = rs(FieldName) End If Exit_DMedian: Exit Function Err_DMedian: If Err.Number = 3075 Then DMedian = 0 Resume Exit_DMedian ElseIf Err.Number = 3021 Then 'EOF or BOF ie no recordset created DMedian = -1 Resume Exit_DMedian Else MsgBox Err.Description Resume Exit_DMedian End If End Function Square brackets are the answer to fieldnames containing spaces. Can we see your code that attempts to use them? James A. Fortune Median of Group By values using SQL only: http://groups.google.com/group/comp....f22fa9d?hl=en& Nov 13 '05 #2

 P: n/a Could you post your version with square brackets? wrote in message news:11**********************@g43g2000cwa.googlegr oups.com... Can I get the following function to work with Fieldnames that have spaces in it? I've tried spqaure brackets to no avail. Any help would be appreciated. Public Function DMedian(FieldName As String, _ TableName As String, _ Optional Criteria As Variant) As Double On Error GoTo Err_DMedian 'Returns the median of a given field in a given table. 'Returns -1 if no recordset is created Dim conn As Connection Dim rs As New ADODB.Recordset Dim strSQL As String Dim RowCount As Long Dim LowMedian As Double, HighMedian As Double 'Open a recordset on the table. Set conn = CurrentProject.Connection strSQL = "SELECT " & FieldName & " FROM " & TableName If Not IsMissing(Criteria) Then strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & FieldName Else strSQL = strSQL & " ORDER BY " & FieldName End If ' Debug.Print strSQL rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic 'Find the number of rows in the table. rs.MoveLast RowCount = rs.RecordCount rs.MoveFirst If RowCount Mod 2 = 0 Then 'There is an even number of records. Determine the low and high 'values in the middle and average them. rs.Move Int(RowCount / 2) - 1 LowMedian = rs(FieldName) rs.Move 1 HighMedian = rs(FieldName) DMedian = (LowMedian + HighMedian) / 2 Else 'There is an odd number of records. Return the value exactly in 'the middle. rs.Move Int(RowCount / 2) DMedian = rs(FieldName) End If Exit_DMedian: Exit Function Err_DMedian: If Err.Number = 3075 Then DMedian = 0 Resume Exit_DMedian ElseIf Err.Number = 3021 Then 'EOF or BOF ie no recordset created DMedian = -1 Resume Exit_DMedian Else MsgBox Err.Description Resume Exit_DMedian End If End Function Nov 13 '05 #3

 P: n/a Finally got the function to work. Public Function DMedian(FieldName As String, _ TableName As String, _ Optional Criteria As Variant) As Double On Error GoTo Err_DMedian 'Returns the median of a given field in a given table. 'Returns -1 if no recordset is created Dim conn As Connection Dim rs As New ADODB.Recordset Dim strSQL As String Dim RowCount As Long Dim LowMedian As Double, HighMedian As Double 'Open a recordset on the table. Set conn = CurrentProject.Connection strSQL = "SELECT " & "[" & FieldName & "]" & " FROM " & TableName If Not IsMissing(Criteria) Then strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & "[" & FieldName & "]" Else strSQL = strSQL & " ORDER BY " & "[" & FieldName & "]" End If ' Debug.Print strSQL rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic 'Find the number of rows in the table. rs.MoveLast RowCount = rs.RecordCount rs.MoveFirst If RowCount Mod 2 = 0 Then 'There is an even number of records. Determine the low and high 'values in the middle and average them. rs.Move Int(RowCount / 2) - 1 LowMedian = rs(FieldName) rs.Move 1 HighMedian = rs(FieldName) DMedian = (LowMedian + HighMedian) / 2 Else 'There is an odd number of records. Return the value exactly in 'the middle. rs.Move Int(RowCount / 2) DMedian = rs(FieldName) End If Exit_DMedian: Exit Function Err_DMedian: If Err.Number = 3075 Then DMedian = 0 Resume Exit_DMedian ElseIf Err.Number = 3021 Then 'EOF or BOF ie no recordset created DMedian = -1 Resume Exit_DMedian Else MsgBox Err.Description Resume Exit_DMedian End If End Function MacDermott wrote: Could you post your version with square brackets? wrote in message news:11**********************@g43g2000cwa.googlegr oups.com... Can I get the following function to work with Fieldnames that have spaces in it? I've tried spqaure brackets to no avail. Any help would be appreciated. Public Function DMedian(FieldName As String, _ TableName As String, _ Optional Criteria As Variant) As Double On Error GoTo Err_DMedian 'Returns the median of a given field in a given table. 'Returns -1 if no recordset is created Dim conn As Connection Dim rs As New ADODB.Recordset Dim strSQL As String Dim RowCount As Long Dim LowMedian As Double, HighMedian As Double 'Open a recordset on the table. Set conn = CurrentProject.Connection strSQL = "SELECT " & FieldName & " FROM " & TableName If Not IsMissing(Criteria) Then strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & FieldName Else strSQL = strSQL & " ORDER BY " & FieldName End If ' Debug.Print strSQL rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic 'Find the number of rows in the table. rs.MoveLast RowCount = rs.RecordCount rs.MoveFirst If RowCount Mod 2 = 0 Then 'There is an even number of records. Determine the low and high 'values in the middle and average them. rs.Move Int(RowCount / 2) - 1 LowMedian = rs(FieldName) rs.Move 1 HighMedian = rs(FieldName) DMedian = (LowMedian + HighMedian) / 2 Else 'There is an odd number of records. Return the value exactly in 'the middle. rs.Move Int(RowCount / 2) DMedian = rs(FieldName) End If Exit_DMedian: Exit Function Err_DMedian: If Err.Number = 3075 Then DMedian = 0 Resume Exit_DMedian ElseIf Err.Number = 3021 Then 'EOF or BOF ie no recordset created DMedian = -1 Resume Exit_DMedian Else MsgBox Err.Description Resume Exit_DMedian End If End Function Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion. 