Problem w/ select statement in open recordset

[Jonathan Snyder via AccessMonster.com]

I have code to get a median value which works on the entire recordset, but
when I try to put a select statement, the function no longer works.

Here is my code

Option Explicit
Function Median(tblAges As String, Age As String, Sample_Id As String,
Reader As String) As Single
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, OffSet As
Integer
Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & Age & "] FROM [" &
tblAges & _
"] WHERE [" & Sample_Id & "] = [forms]![frmageheader]![Sample_id] AND ["
_
& Reader & "] = [forms]![frmAgeHeader]!FrmReaderAgeSbfrm]![Reader] AND["
_
& Age & "] IS NOT NULL ORDER BY [" & Age & "];")


Please help!
Thanks
Jonathan

 

[Guest]

Try this

Set ssMedian = MedianDB.OpenRecordset("SELECT [" & Age & "] FROM [" &
tblAges & "] WHERE [" & Sample_Id & "] = " &
forms![frmageheader]![Sample_id] & " AND [" & Reader & "] = " &
forms![frmAgeHeader]!FrmReaderAgeSbfrm]![Reader] & " AND [" & Age & "] IS NOT
NULL ORDER BY [" & Age & "];")

That incase and the values are numbers, but if they are string then you
should add a single quote before and after
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & Age & "] FROM [" &
tblAges & "] WHERE [" & Sample_Id & "] = '" &
forms![frmageheader]![Sample_id] & "' AND [" & Reader & "] = '" &
forms![frmAgeHeader]!FrmReaderAgeSbfrm]![Reader] & "' AND [" & Age & "] IS
NOT NULL ORDER BY [" & Age & "];")

 

[Jonathan Snyder via AccessMonster.com]

Thanks, I really appreciate it!