Filter from a lookup wizard field

[Guest]

How to filter a specific subject in a look-up field in a report, when I try
to filter it is giving the mismatch message.

 

[John W. Vinson]

How to filter a specific subject in a look-up field in a report, when I try
to filter it is giving the mismatch message.

You're another victim of the misdesigned, misleading, infuriating Lookup
Wizard misfeature.

Your table *APPEARS* to contain the name of a subject. It doesn't. It contains
a Long Integer number, a foreign key to the subjects table. Searching for
"Normalization" won't find the value 54 stored in your table!

You can create a Form with an unbound combo box based on the lookup table of
subjects, and use the name of the combo as a criterion in your query:

[Forms]![NameOfTheForm]![NameOfTheCombobox]

I'd recommend basing another form (or report, for printing, or both) on this
Query and opening it using a command button on the form.

John W. Vinson [MVP]

 

[Guest]

Many Thanks MR. John,
Can you please simplify it little more, as i am a new user I don't
understand it fully. I will explain it my problem as below:

My database is for recording correspondence. I want to filter the letter
received from a specific company. The company list is a seperate table. In
the main table the company name is linked to the above list through lookup
wizard & it shows data type as number. I have created report from this table.
In the query I use to type questions mark(?) as criteria in company list
field. When I preview the report it normaly ask for that field and I use to
type the company name. But now it is giving the message that "mismatch data"
Please help me to solve it. Thanks

How to filter a specific subject in a look-up field in a report, when I try
to filter it is giving the mismatch message.

You're another victim of the misdesigned, misleading, infuriating Lookup
Wizard misfeature.

Your table *APPEARS* to contain the name of a subject. It doesn't. It contains
a Long Integer number, a foreign key to the subjects table. Searching for
"Normalization" won't find the value 54 stored in your table!

You can create a Form with an unbound combo box based on the lookup table of
subjects, and use the name of the combo as a criterion in your query:

[Forms]![NameOfTheForm]![NameOfTheCombobox]

I'd recommend basing another form (or report, for printing, or both) on this
Query and opening it using a command button on the form.

John W. Vinson [MVP]

 

[John W. Vinson]

Many Thanks MR. John,
Can you please simplify it little more, as i am a new user I don't
understand it fully. I will explain it my problem as below:

My database is for recording correspondence. I want to filter the letter
received from a specific company. The company list is a seperate table. In
the main table the company name is linked to the above list through lookup
wizard & it shows data type as number. I have created report from this table.
In the query I use to type questions mark(?) as criteria in company list
field. When I preview the report it normaly ask for that field and I use to
type the company name. But now it is giving the message that "mismatch data"
Please help me to solve it. Thanks

First: open the table in design view. Select the Company field; select the
Lookup tab in the field properties and change the Combo Box to Textbox. This
will let you see what is REALLY in your table when you look at the table
datasheet (which, by the way, should be done only for debugging and design
work, NOT for routine interaction with the data).

Base your Report - not on the table - but on a Query joining your table to the
company table. Your table DOES NOT contain the company name; it just *appears*
to contain it, due to the Lookup Wizard hiding the table's actual contents.

You can then put the company name (from the joined Company table) onto your
report, and use search criteria on the company name (rather than on the
numeric ID).

John W. Vinson [MVP]

 

[Guest]

Thank You Very much Mr. John. It was very much helpfull to me. Expecting
such help in the future also.