Median average doesn't change with any parameters

[tezread]

I need to get a better understanding of the recordset function. I don't understand what the code is doing

 

[vbaInet]

The code you have uses a recordset too. The only difference is the logic and implementation. When you're given a solution, it's always best to test them all.

 

[CBrighton]

I need to get a better understanding of the recordset function. I don't understand what the code is doing

Welcome to debugging someone elses code which has no comments to explain it.

I'm not really sure if it's selecting the correct record for the median, however I don't see any blindingly obvious issues with the code.

Given that it goes back roughly half the number of records in the record count I don't see why it is getting to the point where it runs out of records.

As vbaInet says, debuging others code isn't necessarily the easiest route, especially if you have already been given another piece of code which should also work.

 

[tezread]

I think I migth start from scratch and use base the record select on vbainet code

 

[vbaInet]

I've written the code to be more generic:

Public Function GetMedian(strMedianField As String) As Variant
     Dim rs As DAO.Recordset
     Dim firstVal As Double, recCount As Long

     Set rs = CurrentDb.OpenRecordset("[COLOR=Red]SELECT ...[/COLOR]", dbOpenSnapshot)

     recCount = rs.RecordCount

     ' Quickly return the median if record count is < 2
     If recCount < 2 Then
          Select Case recCount
               Case 0
                    GetMedian = Null
               Case 1
                    rs.MoveFirst
                    GetMedian = rs(strMedianField)
          End Select
          Set rs = Nothing
          Exit Function
     End If

     rs.MoveLast
     rs.MoveFirst

     ' Get the median if record count is > 2
     If (recCount Mod 2) = 0 Then
          ' Median for even number of records - require a median between the two middle records
          
          rs.Move (recCount / 2) - 1
          firstVal = rs(strMedianField)
          rs.MoveNext
          GetMedian = (firstVal + rs(strMedianField)) / 2
     Else
          ' Median for odd numbers - just get the middle record
          
          rs.Move Fix(recCount / 2) - 1
          GetMedian = rs(strMedianField)
     End If

     Set rs = Nothing
End Function

To call:

GetMedian("[COLOR=Red]Name of Field to use for Median[/COLOR]")

 

[tezread]

I've written the code to be more generic:

=GetMedian(sqlStr, "[COLOR=red]Name of Field to use for Median[/COLOR]")

Am I calling that in the query window?

 

[tezread]

i get an error message 'the expression you entered as a query parameter oroduced this error [sqlstr]

 

[vbaInet]

I've amended my last post so that you can call it in a query.

Copy the whole function again.

 

[tezread]

Right - that working - just need to add parameters in select statement... here we go.....

 

[vbaInet]

Right - that working - just need to add parameters in select statement... here we go.....

{-fingers crossed-}

 

[tezread]

I used your code vbanet for the parameters:

Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldname & "] " & _
"FROM [" & tName & "] " & _
"WHERE [" & fldname & "] IS NOT NULL " & _
"AND (Date_Referred BETWEEN #" & [Forms]![frmReport]![startdate] & "# AND #" & _
[Forms]![frmReport]![enddate] & "#) " & _
"ORDER BY [" & fldname & "];")

And that brigns up an 'object required error message

I tried declaring variables

Dim fldname
Dim tname As String

but it says the same?

 

[CBrighton]

Do this and check that the correct values are returning:

msgbox "Start date: " & [Forms]![frmReport]![startdate]
msgbox "End date: " & [Forms]![frmReport]![enddate]
msgbox "Table Name: " & tName
msgbox "Field name: " & fldName

:edit:

Comment out the existing code in the function and replace it with this, then call the function as normal.

Or put this code before the rest of the function code and put "Exit sub" after the above code so it doesn't try to run the normal code afterwards.

 

[vbaInet]

tezread, please pay closer attention to the bits you were required to amend. The bits you needed to change were highlighted in red:

     Set rs = CurrentDb.OpenRecordset([COLOR=Red]"SELECT ..."[/COLOR], dbOpenSnapshot)

And you are doing this:

[COLOR=Red]Set ssMedian = MedianDB.OpenRecordset[/COLOR]("SELECT [" & fldname & "] " & _
                      "FROM [" & tName & "] " & _
                      "WHERE [" & fldname & "] IS NOT NULL " & _
                           "AND (Date_Referred  BETWEEN #" & [Forms]![frmReport]![startdate] & "# AND #" & _
                                               [Forms]![frmReport]![enddate] & "#) " & _
                      "ORDER BY [" & fldname & "];")

 

[tezread]

OK

Now have 'syntax error in FROM clause?

     Set rs = CurrentDb.OpenRecordset("SELECT [" & fldname & "] " & _
                      "FROM [" & tname & "] " & _
                      "WHERE [" & fldname & "] IS NOT NULL " & _
                           "AND (Date_Referred  BETWEEN #" & [Forms]![frmReport]![startdate] & "# AND #" & _
                                               [Forms]![frmReport]![enddate] & "#) " & _
                      "ORDER BY [" & fldname & "];", dbOpenSnapshot)

 

[vbaInet]

What do you think is the problem?

 

[tezread]

I think the code is saying it doesn't know what table or fieldname to look at (in lamens terms)

 

[vbaInet]

Good

Replace the bits in red with the right field name and table name:

     Set rs = CurrentDb.OpenRecordset("SELECT [[COLOR=Red]FieldName[/COLOR]] " & _
                      "FROM [[COLOR=Red]TableName[/COLOR]] " & _
                      "WHERE [[COLOR=Red]FieldName[/COLOR]] IS NOT NULL " & _
                           "AND ([COLOR=Red]Date_Referred[/COLOR] BETWEEN #" & [Forms]![frmReport]![startdate] & "# AND #" & _
                                               [Forms]![frmReport]![enddate] & "# " & _
                      "ORDER BY [[COLOR=Red]FieldName[/COLOR]];", dbOpenSnapshot)

 

[tezread]

I tried that before and it didn't work!!- Hmmm- I have a missing operator error message now - error 3075 missing operator in query expression.
Strange as when i run the query in SQL format it works fine

    Set rs = CurrentDb.OpenRecordset("SELECT [ReferralToTreatment] " & _
                      "FROM [qryEpisode] " & _
                      "WHERE [ReferralToTreatment] IS NOT NULL " & _
                           "AND (Date_Referred BETWEEN #" & [Forms]![frmReport]![startdate] & "# AND #" & _
                                               [Forms]![frmReport]![enddate] & "# " & _
                      "ORDER BY [ReferralToTreatment];", dbOpenSnapshot)

 

[vbaInet]

Let me see your new sql.

Edit: my bad. I missed out the closing brace -->

[Forms]![frmReport]![enddate] & "#[COLOR=Red])[/COLOR]

The one in red

 

[vbaInet]

Alrighty, try this one:

    Set rs = CurrentDb.OpenRecordset("SELECT [ReferralToTreatment] " & _
                      "FROM [qryEpisode] " & _
                      "WHERE [ReferralToTreatment] IS NOT NULL " & _
                           "AND (Date_Referred BETWEEN #" & [Forms]![frmReport]![startdate] & "# AND #" & _
                                               [Forms]![frmReport]![enddate] & "#) " & _
                      "ORDER BY [ReferralToTreatment];", dbOpenSnapshot)