Help with a function using recordsets

[Jonathan Snyder via AccessMonster.com]

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

 

[Guest]

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

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

 

[Jonathan Snyder via AccessMonster.com]

Thanks for the help,

I corrected the syntax in the where statement.

I'm not sure I follow what you are saying about the select statement. I am
calling the function from a query which is as follows:

SELECT DISTINCT qryPI1.Sample_ID, qryPI1.Reader, P("Age","Reading_Number",
"tblages","Sample_ID = '" & [Sample_ID] & "' AND Reader = '" & [Reader] & "'")
AS PI
FROM qryPI1;

qryPI1 is as follows:

SELECT DISTINCT tblAges.Sample_ID, tblAges.Reader, tblAges.Reading_Number,
tblAges.Age
FROM tblAges;

In my function code the Table is called tblAges, field names of interest are
Reading_Number and Age. The Select statement should select reading_number
values and Age Values from tblAges where sample_ID and Reader are not unique
and create a recordset.

I then wish to evaluate the age values in each recordset and assign a P value
according to the rules I wrote in the function code.

This works just fine as long as a recordset has three records (I.E. 3 age
values). If a record set has > or < 3 records (I.E. sample has been aged >
or < 3 times) I get an error. I want to avoid the error and set P=0. In
otherwords I dont want to evaluate the age values in the recordset if record
count <> 3.

Thanks for you help, I dont have much experiance with functions.

 

[Guest]

Jonathan,

Ok didn't catch it the first time because I wasn't familiar with the method
but I am fairly sure your proble is in this section of the code

If rcount <> 3 Then
rsPI.NextRecordset
End If

The .NextRecordset method is used to and I quote from the help file,

Gets the next set of records, if any, returned by a multi-part select query
in an OpenRecordset call, and returns a Boolean value indicating whether one
or more additional records are pending (ODBCDirect workspaces only).

So first question, are you using an ODBCDirst workspace?

If you are, and judging from the error I would guess not, then
where is the next part of the SQL statement.

Again qoting from the help file they show the following as an example

SELECT LastName, FirstName FROM Authors
WHERE LastName = 'Smith';
SELECT Title, ISBN FROM Titles
WHERE Pub_ID = 9999

Notice there are two distinct SQL statements (SELECT blah from blah;Select
blah from blah) in your sql statement you only have one part so there is no
next recordset to go to.

This error only comes up when rcount <> 3 since that is the only time the
method is called. Thats why it works for rCount =3, the method is never
called.

Ken

 

[Jonathan Snyder via AccessMonster.com]

Thanks for the help,

Even without the code containing .nextrecordset, the error is generated when
record count is <> 3. I guess I need code to handle recordsets that are <>3,
but I'm not sure that the two part select statement will work here. Any
other thoughts??