Filtering Recordsets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created an application which is used for assessments of evolutions and
records. There are 2 main tables: tblAssessments and tblDeficiencies
Some of the fields in each of these tables are populated from field in other
tables but my current issue is mainly with these tables. The field
"AssessmentID" is the primary key in tblAssessments and a foreign key in
tblDeficiencies. I need to search through the data and return Assessments
which meet my selected criteria.

I am sure I will have to do this in two steps since the relationship between
tblAssessments and tblDeficiencies is a 1 to Many, respectively.
I have created a form which has a list box for each of the items I wish to
search by.
Items I wish to search by are...
AssessorID,AssessedID,Performance,DepartmentID...etc
Each listbox is coded to return a string in the form of
rstFirstSearchString.Fields("AssessorID") IN (100,125,131)

I thought I could then use this to compare to a record in an ADO recorset.
When I run the code, I get a type mismatch error. The line I am trying to
run is similar to
If rstFirstSearchString.Fields("AssessorID") IN (100,125,131) Then
' Do some function
End If

Can you use the IN function when dealing with recordsets. If not how do I go
about running a filtered search like this?
 
Hi,

IN is SQL syntax. Your 'IF' is in VBA.

I think the simplest solution would be to change your IF;

If rstFirstSearchString.Fields("AssessorID") = 100 OR
rstFirstSearchString.Fields("AssessorID")=125 OR
rstFirstSearchString.Fields("AssessorID")=131 Then

However you can use ADOs Find command on the recordset to find all records &
loop through that way ... then you could use the IN construct (in your Find
Criteria) ... which would be a bit slicker & more extensible in many cases.

Regards,

Chris.
 
Back
Top