problem with criteria for query

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

Guest

I have 3 tables - Resin, Criteria, and Application. Criteria and Application
only have an ID number and a list of criteria or applications. These tables
are linked to Resin. Inside of the resin table Criteria and Application are
available through a pulldown list. With the query, I am asking for criteria
where the user enters the Criteria and Application and for any matching
combinations, all data is displayed. The query works fine with no criteria
or if I use criteria only from the resins table. however, everytime I try to
use Criteria or Application (and I've made sure to match the fields exactly),
nothing shows up. Any ideas would be great!

Thanks!
 
Rawley,

It looks like you have fallen into the trap of using Lookup fields in
your table. This is never a good idea. For more information on this,
see http://www.mvps.org/access/lookupfields.htm . One of the problems
is that the data that you see is not the data in the field. In other
words, I would strongly suspect that waht you are entering in the
criteria of your query are the desired Criteria or Application values,
whereas the actual data in the Resin table is the ID for the respective
Criteria or Application. Therefore, your query will work if you put the
ID numbers in the query criteria instead.
 
I understand. I know I've done this before, however. is there some way to
make this happen so that people can actually enter the data versus the number?

Thanks.
 
Rawley,

If you want the "data versus the number" in the Resin table, then the ID
fields in the Criteria and Application tables is redundant and you
should remove them.
 
I understand. I know I've done this before, however. is there some way to
make this happen so that people can actually enter the data versus the number?
Click to expand...

What you can do is create an unbound Form (let's call it frmCrit) with
two unbound combo boxes for the criteria. Use the ID as the bound
column of the combo, and set its width to zero so the user sees the
text.

In your Query use

=[Forms]![frmCrit]![nameofcombo]

as your criterion. It's convenient to base a Form and/or Report on the
query, and put a command button on frmCrit to launch it.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top