Setting criteria in query

[Lars Brownies]

I have a reports form in which users can select the desired report, set some
criteria, and show the report. On the form I have an unbound combo field of
which the value is used as criteria in the report's underlying query.

I recently normalized the query's underlying table so the particular field
on which I normally set the criteria, has changed from a text field to a
numeric field. I've added a new table which holds this numeric ID field and
the text field.

My question is: should I set the query criteria on the ID number or should I
set it on the text field of the new and linked table? If I'd set it on the
ID number field I would have to add the ID number invisibly to my combobox.
What's common practice in this case?

Hopes this makes sense.

Thanks,
Lars

 

[Duane Hookom]

You should ideally have the combo box bound to a value that matches the
detail record in your report. For instance if your combo box has a row source
like:

SELECT EmpID, EmpLastName & ", " & EmpFirstName
FROM tblEmployees
ORDER BY EmpLastName, EmpFirstName;

I would hide the first column of the combo box and display the Employee full
name. Then set the criteria against the EmpID field in the detail of your
report's record source.

 

[Lars Brownies]

Thanks Duane.

You should ideally have the combo box bound to a value that matches the
detail record in your report. For instance if your combo box has a row
source
like:

SELECT EmpID, EmpLastName & ", " & EmpFirstName
FROM tblEmployees
ORDER BY EmpLastName, EmpFirstName;

I would hide the first column of the combo box and display the Employee
full
name. Then set the criteria against the EmpID field in the detail of your
report's record source.