my VBA code to calculate median values is broken. :(

[nacholibre]

Hello,

I've received some help here and used it to try and modify this code to
calculate median values in my table.

I think I'm have a syntax problem.

I'm hoping someone can point to where I've gone wrong..

Thank you!

Here's the code:

Function MedianF(pTable As String, pfield As String, region As String,
informa_fico As String, informa_ltv As String, informa_term As String) As
Single




Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
Dim sglHold As Single

strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0
AND REGION = " & region & " AND INFORMA_FICO = " & informa_fico & " AND
INFORMA_LTV = " & informa_ltv & " AND INFORMA_TERM = " & informa_term &
"Order by " & pfield & ";"



Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.Move -Int(n / 2)

If n Mod 2 = 1 Then 'odd number of elements

MedianF = rs(pfield)

Else 'even number of elements

sglHold = rs(pfield)
rs.MoveNext
sglHold = sglHold + rs(pfield)
MedianF = sglHold / 2

End If

rs.Close
End Function

 

[i_takeuti]

Hello,

I've received some help here and used it to try and modify this code to
calculate median values in my table.

I think I'm have a syntax problem.

I'm hoping someone can point to where I've gone wrong..

Thank you!

Here's the code:

Function MedianF(pTable As String, pfield As String, region As String,
informa_fico As String, informa_ltv As String, informa_term As String) As
Single




Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
Dim sglHold As Single

strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0
AND REGION = " & region & " AND INFORMA_FICO = " & informa_fico & " AND
INFORMA_LTV = " & informa_ltv & " AND INFORMA_TERM = " & informa_term &
"Order by " & pfield & ";"



Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.Move -Int(n / 2)

If n Mod 2 = 1 Then 'odd number of elements

MedianF = rs(pfield)

Else 'even number of elements

sglHold = rs(pfield)
rs.MoveNext
sglHold = sglHold + rs(pfield)
MedianF = sglHold / 2

End If

rs.Close
End Function

 

[Brendan Reynolds]

I can't see any syntax error in the VBA code, it compiles without error.
(After I've fixed the broken line endings which were probably caused by my
newsreader and are probably not in your original code). The SQL statement
would by - as far as I can tell - syntactically correct if all columns are
numeric. If any of those columns are text columns, though, you're missing
the text delimiters. For example, if 'Region' is a text column, then the
reference to 'Region' in your SQL statement needs to look something like
this ...

AND REGION = '" & region & "' AND

That's a single quote followed by a double quote after the "=" and a double
quote followed by a single quote before the "AND".

If there is any possibility that the value in the variable 'region' might
itself contain embedded quotes ... say, just as on off-the-top example it
might contain the value nor'west ... then you need to double them ...

AND REGION = '" & Replace(region, "'", "''") & "' AND

The second and third arguments to the Replace function above are a single
quote between two double quotes, and two single quotes between two double
quotes, respectively.

The idea is that the SQL statement that gets executed ends up looking like
so ...

AND REGION = 'nor''west' AND

That's two single quotes between 'nor' and 'west'.

 

[nacholibre]

Thanks - that was it. I made those changes and I am good to go now.