help with on-the-fly query as source for recordset (1 Viewer)

[CraigDolphin]

I am writing a module to calculate the geometric mean and percentile for water quality data. I wrote a module using hard-coded queries as the source for a recordset and it all worked fine. However, I want the user to be able to select how many samples are to be included for each calculation and I have no idea how to pass a parameter that would change the number of records displayed in a hardcoded query.

After a lot of reading, I decided to try generating the on-the-fly sql that would allow me to dynamically alter how many records to include, and use that as the source for my recordset.

The following is my best effort so far. I have the DAO reference checked, and I created a query called 'qryDynamic' for this purpose.

My problem is that although the strSQL string seems to be correct (I used the hardcoded query's SQL as a template), the sql behind qryDynamic does not seem to be changed at all after running the function, and the msgbox events I placed to track where things are going wrong do not fire (except for the one showing the value of strSQL). If I uncomment the error handler even the strSQL msgbox does not fire, and no error message appears.

What am I doing wrong? Would greatly appreciate any thoughts.

Public Function GeometricMean(FinishDate As Date) As Variant
'On Error GoTo GeometricMean_err
'GeometricDate = FinishDate

Dim db As DAO.Database
Dim qdef As DAO.QueryDef
Dim rsvalues As DAO.Recordset
Dim prod As Double
Dim Counter As Integer
Dim samples As Integer
Dim strSQL As String


Set db = CurrentDb
Set qdef = db.QueryDefs("qryDynamic")

prod = 1
Counter = 1
samples = 4 ' how many samples to include in geometric mean

strSQL = "SELECT TOP " & samples & " tblSampleRuns.RunDate, tblSiteVisits.SiteID, tblSampleData.ParameterID, tblSampleData.Value, tblSampleData.PracticalDetectionLimit, IIf([Value]<[PracticalDetectionLimit],[PracticalDetectionLimit]-IIf(Len(Int([PracticalDetectionLimit]))=1,0.1,1),[Value]) AS [Input] " & _
        "FROM (tblSampleRuns INNER JOIN tblSiteVisits ON tblSampleRuns.RunID = tblSiteVisits.RunID) INNER JOIN tblSampleData ON tblSiteVisits.SiteVisitID = tblSampleData.SiteVisitID " & _
        "WHERE (((tblSampleRuns.RunDate) <= #" & FinishDate & "#)) And ((tblSiteVisits.SiteID) Like " & [Forms]![fmGeometricMean_Param]![picksite] & ")) And ((tblSampleData.ParameterID) = 8)) " & _
        "ORDER BY tblSampleRuns.RunDate DESC;"

MsgBox strSQL, vbInformation

qdef.SQL = strSQL ' I think that this bit is supposed to change the sql 
                          'underlaying my placeholder query 


Set rsvalues = qdef.OpenRecordset(dbOpenDynaset)

If Not rsvalues.EOF Then rsvalues.MoveFirst

    
Do While Not rsvalues.EOF
    If Counter > samples Then  'The real loop exit condition.
        Exit Do
    End If
    prod = prod * rsvalues![Input]
    MsgBox prod, vbInformation
    Counter = Counter + 1
    rsvalues.MoveNext
Loop

If Counter = samples + 1 Then
    GeometricMean = prod ^ (1 / samples)
Else
    GeometricMean = Null
End If

MsgBox GeometricMean, vbInformation
qdef.Close
db.Close

'GeometricMean_exit:
' Exit Sub
'GeometricMean_err:
'MsgBox "An unexpected error has occurred." & _
      vbCrLf & "Please note of the following details:" & _
      vbCrLf & "Error Number: " & Err.Number & _
      vbCrLf & "Description: " & Err.Description _
      , vbCritical, "Error"
'    Resume GeometricMean_exit

End Function

 

[KeithG]

Does the SQL behind your querydef not get updated?

 

[CraigDolphin]

No. At least, I don't think it does. When I open up the qryDynamic in design view after running the module, nothing has changed.

 

[izyrider]

is it something as banal as a parenthesis error?

tought to diagnose, but here's a go at a count ('scuse the ugly syntax)

strSQL = "SELECT TOP " & samples & " tblSampleRuns.RunDate, tblSiteVisits.SiteID, tblSampleData.ParameterID, tblSampleData.Value, tblSampleData.PracticalDetectionLimit, IIf
( 1
[Value]<[PracticalDetectionLimit],[PracticalDetectionLimit]-IIf
( 2
Len
( 3
Int
( 4
[PracticalDetectionLimit]
)) 2
=1,0.1,1
) 1
,[Value]
) 0
AS [Input] " & _
"FROM
( 1
tblSampleRuns INNER JOIN tblSiteVisits ON tblSampleRuns.RunID = tblSiteVisits.RunID
) 0
INNER JOIN tblSampleData ON tblSiteVisits.SiteVisitID = tblSampleData.SiteVisitID " & _
"WHERE
((( 3
tblSampleRuns.RunDate
) 2
<= #" & FinishDate & "#
)) 0
And
(( 2
tblSiteVisits.SiteID
) 1
Like " & [Forms]![fmGeometricMean_Param]![picksite] & "
)) -1 <<<<<<<<<<< OOOOOOPS <<<<<<<<<<<<<<<<<
And
(( 1
tblSampleData.ParameterID
) 0
= 8
)) -2 <<<<<<<<<<<< OOOOOOPS <<<<<<<<<<<<<<<
" & _
"ORDER BY tblSampleRuns.RunDate DESC;"


izy

 

[CraigDolphin]

Erk, good spotting izy!

Boy do I feel dumb. Thanks so much for taking the time to help!