SQL statement in VBA code

[timbert62]

I am very new to VBA, so please bear with me as I try to describe what is probably a VERY simple situation.

I need to calculate the MEDIAN value of the [AGE] field in each year, from a table that has records with AGE & YEAR for many different years.

I found a VBA function MEDIAN(tablename,fieldname) that can give me the MEDIAN of all the ages in the table, but i need to have it provide the MEDIAN for a particular year.

So I added a paramter to the function - VarYear, and modified the code to this:

Function Median(tName$, fldName$, VarYear$) As String

Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
Dim Year, EvalYear As Integer

Set MedianDB = CurrentDb()
EvalYear = VarYear$
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName$ & "] FROM [" & tName$ & "] WHERE (([" & fldName$ & "]>0) AND ([Year]= EvalYear )) ORDER BY [" & fldName$ & "]&quot

ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x% = RCount% Mod 2
If x% <> 0 Then
OffSet% = ((RCount% + 1) / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName$)
Else
OffSet% = (RCount% / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
x% = ssMedian(fldName$)
ssMedian.MovePrevious
y% = ssMedian(fldName$)
Median = (x% + y%) / 2
End If
ssMedian.Close
MedianDB.Close
End Function

The code always gets hung-up on the SELECT statement - with 'runtime error # 3061 -- too few parameters, expected 1'

What confuses me is that if instead of using the variable &quot;EvalYear&quot; in the WHERE criteria, if I just type in a year ( ie &quot;2001&quot it works fine ... I get the MEDIAN AGE for the records from with YEAR = 2001. So it seems like the logic of my modifications to the code works OK. The problem is my ignorance as to how to use the variable value into the SELECT statement.

I'm sure it is something VERY simple, I just don't know what it is

THANKS

 

[jebry]

Hi!

Try this:

Set ssMedian = MedianDB.OpenRecordset(&quot;SELECT [&quot; & fldName$ & &quot;] FROM [&quot; & tName$ & &quot;] WHERE (([&quot; & fldName$ & &quot;]>0) AND ([Year]= &quot; & EvalYear & &quot) ORDER BY [&quot; & fldName$ & &quot;]&quot

hth
Jeff Bridgham
bridgham@purdue.edu

 

[timbert62]

that WORKED !!

i was sure i had tried that (but obviously, i hadn't)

thanks

tim