RE: Help with a function using recordsets

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hey Jonathan,

I see a couple of things that might be causing problems.

1) Syntax error in SQL statement:
strsql = strsql & "WHERE[" & Age & "] IS NOT NULL "
--- Seems minor but there needs to be a space between 'Where' and '['
--- Should be
strsql = strsql & "WHERE [" & Age & "] IS NOT NULL "

2) This may actually be correct based on your fields in the tables but
it looks like you are passing in the names of the fields i.e.
strsql = "select [" & Age & "],[" & Reading_Number & "] FROM [" & tblAges
& "]
it looks like the value contained in Age is the name of a field in a table
whose name is contained in the variable tblAges, same with Reading_number.
If the name of the field is Age and you are actually trying to determine if
the value in the field Age matches the value in the variable age then you
need to change your SQL statement

If this doesn't help post back and we'll go from there

Ken

"Jonathan Snyder via AccessMonster.com" wrote:

> I have written a function that I am calling from a query to calculate what I
> call a "precision index" (PI). I have recordsets of different sizes, but I
> only want to calculate a PI on recordsets with a record count of 3.
>
> I have tried inserting code to the effect of:
> if Recordcount <> 3 then
> P=0
> ..nextrecordset
>
> But I get the error message "operation is not supported for this type of
> object"
>
> My function is as follows:
>
> 'function to calculate precision index on tooth ages by sample_ID and Reader
>
> Function P(Age As String, Reading_Number As String, tblAges As String,
> Optional WhereClause As String = "" _
> ) As Single
>
> Dim dbPI As DAO.Database
> Dim rsPI As DAO.Recordset
> Dim strsql As String
> Dim rcount As String
> Dim firstage As String
> Dim secondage As String
> Dim thirdage As String
> Dim PI As Double
>
> Set dbPI = CurrentDb()
> strsql = "select [" & Age & "],[" & Reading_Number & "] FROM [" & tblAges & "]
> "
> strsql = strsql & "WHERE[" & Age & "] IS NOT NULL "
> If Len(WhereClause) > 0 Then
> strsql = strsql & "AND (" & WhereClause & ") "
> End If
> strsql = strsql & "ORDER BY [" & Reading_Number & "]"
>
> Set rsPI = dbPI.OpenRecordset(strsql)
> If rsPI.EOF = False Then
> rsPI.MoveLast
> End If
> rcount = rsPI.RecordCount
>
> If rcount <> 3 Then
> rsPI.NextRecordset
> End If
>
> rsPI.FindFirst (Reading_Number = "1")
> firstage = rsPI("age")
> rsPI.MoveNext
> secondage = rsPI("age")
> rsPI.MoveNext
> thirdage = rsPI("age")
>
> If firstage = secondage And secondage = thirdage Then
> P = 1
> ElseIf firstage = secondage And secondage <> thirdage Or firstage <>
> secondage _
> And secondage = thirdage Or firstage = thirdage Then
> P = 2
> ElseIf firstage <> secondage And secondage <> thirdage Then
> P = 3
> End If
>
> End Function
>
> If the record count is <> 3 I would like to set P = 0 and move to the next
> recordset. If the record count = 3 I would like to calculate a PI based on
> the rules defined.
>
> Any suggestions would be appreciated.
> Any suggestions
>
> --
> Message posted via http://www.accessmonster.com
>
.

Relevant Pages

  • Re: Trying to create dynamic VBA code (or wasting my time?)
    ... how to manipulate recordsets, having done so before. ... possible to have a string variable that contains a string of what is ... actually a valid VBA statement, and then somehow to run the VBA code ... Dim stringBuff As String ...
    (microsoft.public.access.modulesdaovba)
  • Help with a function using recordsets
    ... I have recordsets of different sizes, ... Function P(Age As String, Reading_Number As String, tblAges As String, ... Dim rsPI As DAO.Recordset ... Dim thirdage As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Trying to create dynamic VBA code (or wasting my time?)
    ... how to manipulate recordsets, having done so before. ... possible to have a string variable that contains a string of what is ... actually a valid VBA statement, and then somehow to run the VBA code ... Dim stringBuff As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Trying to create dynamic VBA code (or wasting my time?)
    ... VBA isn't designed to handle self-modifying code. ... Operator As String) As Variant ... how to manipulate recordsets, having done so before. ... Dim stringBuff As String ...
    (microsoft.public.access.modulesdaovba)
  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)