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.
kkmcg - 29 Jun 2005 20:47 GMT
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
> Thanks for the help,
>
[quoted text clipped - 29 lines]
>
> Thanks for you help, I dont have much experiance with functions.
Jonathan Snyder - 30 Jun 2005 19:36 GMT
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??