By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,396 Members | 2,501 Online
Bytes IT Community
+ 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
Share this Question
Share on Google+
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?

<gu********@gmail.com> 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?

<gu********@gmail.com> 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.