How to query using form field as search criteria?

[Buakaw]

Hi,
I have a table that stores attendance record of students. It has 3 fields:
1) Autonumber: Primary key (identifies each record in attendance field)
2) Number:fkStudentID (key that links it to another table which stores the students' particulars like name, address, etc.)
3) DateAndTime: SessionDate (stores the date of students' attendance record)
4) Number: SessionID (stores the session that student attendant on the SessionDate)

I will like to create a form that allows the user to view attendance records based on 3 criteria:
1) Student's name (which I'll convert to fkStudentID)
2) Date (SessionDate)
3) Session (SessionID)

E.g. the user can choose to search for all students that attended a particular session on a particular day. He'll leave the name field blank, and enter the date and sessionID and the result should display all students in that class.

Or he could query all sessions attended by a particular student. He'll enter the name field, and leave the date and session blank.

So in summary the user can choose to use any or all of the search criteria, and any combination in between.

On my form, the user can enter the criteria on 3 visible text boxes. Once he hits the search button I will use VBA to process the data in the visible text boxes into a format suitable for the query to use. This will be stored on 3 invisible text boxes which are actually linked to the query's Criteria field.

The results will be displayed in a subform which takes its data source from the query.



My questions:
1) What should I enter into the Criteria field of the query for each of the fields (fkStudentID, SessionID, SessionDate)? Should I just point it to the hidden search criteria fields on the form, or use something like this:

Like "*" & Form.StudentIDCriteria & "*" (<-- I realize this will also show up results I might not want. E.g. 302 will also display 3021 or 1302.)

So I don't know what to use.

2) Secondly, can some kind soul give me some examples of data I should enter into the query criteria and the search criteria boxes on the form so this will work? I tried different combinations with no success. I'm guessing I need to format the data in the form fields somehow.

Thanks!

[orange]

There is a free video tutorial at this site that may give you some ideas.
http://www.datapigtechnologies.com/f...earchform.html

[Buakaw]

Thank you!

Btw I also found a very good search form example on Allen Browne's site and am sharing it for anyone who's interested.

http://allenbrowne.com/ser-62.html