Thread: Query by Form
11-26-06, 10:27 #1Registered User
- Join Date
- Nov 2006
Unanswered: Query by Form
This is law enforcement database. The search query is based on three tables: tbl01 contains the names of the person, tbl02 contains physical characteristics (gender, height, weight, hair color, eye color, skin color and place of birth etc,) tbl03 contains the date of birth. In the query all are linked by a common identifier.
I created an unbound form that has text boxes for the search criteria. I changed the name to qgender1, qhair1, qeye1 etc. In the query that brings up the form results I placed the folllowing in the criteria part. Like Forms!frm01.[qgender1]... As long as I am dealing in text fields I get perfect results. Dates/Numbers are the problem.
I have two unbound boxes in the search form txtdob1 and txtdob2 for DOB. The same for height and weight. But when I try in the query to put between Forms!frm01.txtdob1 AND forms!frm01.txtdob2...the query doesn't function.
The same is true for height and weight (Intergers in the table). But to narrow down the list of possible "targets" all this information is needed.
All fields in the searh form are set to null (""). A command button in the search form runs the query and opens a new form with the results of the search.
Any idea what I'm doing wrong?
Last edited by quito0819; 11-26-06 at 10:30.
11-26-06, 11:59 #2Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
not enough detail to understand exactly where it is going wrong, and you are using saved queries which is not my thing anyway. Access mangles the syntax a bit in it's saved queries so i'm not able to comment directly, but odds-on you have a #date#, 'text', number problem and/or a non-US/non-ISO date format problem.
in case it helps, here is my take on generating in-line search SQL from a bunch of form fields. in this little demo the SQL feeds a list (.rowsource) but it could equally feed a form (.recordsource)
izycurrently using SS 2008R2
11-28-06, 13:39 #3Registered User
- Join Date
- Mar 2004
can you query the required data and get the right results using a query alone, if so, set your query criteria to the input forms text boxes.
i.e [ight] = [forms]![input_form]![text12] and so-on
then creat a form based on your query with the form wizard and have that form open in a after update event on the last input field on your input form.