Median Query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • gunnarnime@gmail.com
    #1

    Median Query

    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(FieldNa me 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(Crite ria) Then
    strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " &
    FieldName
    Else
    strSQL = strSQL & " ORDER BY " & FieldName
    End If
    ' Debug.Print strSQL
    rs.Open strSQL, conn, adOpenKeyset, adLockOptimisti c

    '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

    Tags: None
    • jimfortune@compumarc.com
      #2
      Re: Median Query

      gunnarnime@gmai l.com wrote:[color=blue]
      > 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(FieldNa me 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(Crite ria) Then
      > strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " &
      > FieldName
      > Else
      > strSQL = strSQL & " ORDER BY " & FieldName
      > End If
      > ' Debug.Print strSQL
      > rs.Open strSQL, conn, adOpenKeyset, adLockOptimisti c
      >
      > '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[/color]

      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:
      429
      http://groups.google.com/group/comp.databases.ms-access/msg/7fb6eb508f22fa9d?hl=en&


        Comment

        • MacDermott
          #3
          Re: Median Query

          Could you post your version with square brackets?

          <gunnarnime@gma il.com> wrote in message
          news:1129818501 .910405.107540@ g43g2000cwa.goo glegroups.com.. .[color=blue]
          > 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(FieldNa me 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(Crite ria) Then
          > strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " &
          > FieldName
          > Else
          > strSQL = strSQL & " ORDER BY " & FieldName
          > End If
          > ' Debug.Print strSQL
          > rs.Open strSQL, conn, adOpenKeyset, adLockOptimisti c
          >
          > '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
          >[/color]


            Comment

            • gunnarnime@gmail.com
              #4
              Re: Median Query

              Finally got the function to work.

              Public Function DMedian(FieldNa me 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(Crite ria) Then
              strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & "[" &
              FieldName & "]"

              Else
              strSQL = strSQL & " ORDER BY " & "[" & FieldName & "]"

              End If


              ' Debug.Print strSQL
              rs.Open strSQL, conn, adOpenKeyset, adLockOptimisti c

              '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:[color=blue]
              > Could you post your version with square brackets?
              >
              > <gunnarnime@gma il.com> wrote in message
              > news:1129818501 .910405.107540@ g43g2000cwa.goo glegroups.com.. .[color=green]
              > > 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(FieldNa me 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(Crite ria) Then
              > > strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " &
              > > FieldName
              > > Else
              > > strSQL = strSQL & " ORDER BY " & FieldName
              > > End If
              > > ' Debug.Print strSQL
              > > rs.Open strSQL, conn, adOpenKeyset, adLockOptimisti c
              > >
              > > '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
              > >[/color][/color]

                Comment

                Previous template Next
                Working...