Query wont show records

[Bob H]

I have a table with almost 3000 records of 11 different tool types.
I have created a table called tblToolID listing the 11 different types, and
in the tool table I have the toolID as a table/query lookup to tblToolID.
The tooltype is then selected from a combo box.

When I created a new query and put in "electrical" as tooltype as a
criteria, only 8 of the 400 electrical items are returned by the query.
When I put in anther type, say lifting, no records are returned from over
1800 records!
Here is a the SQL as an example:
SELECT tblTools.ToolType, tblTools.ManufacturerID, tblTools.ProductID,
tblTools.Size
FROM tblTools
WHERE (((tblTools.ToolType)="lifting"));

If I put in the criteria box under another feild say "name of product"
under Product ID, all the records are returned.
So why are no records returned from a criteria of tooltype, "Lifting"

Thanks

 

[Jeff Boyce]

Bob

If I'm understanding your description, you have a table in which you have a
"lookup" data type. A scan through this (and the tablesdbdesign) newsgroup
will reveal a strong consensus AGAINST using this data type. For one thing,
it "hides" what's actually being stored. For another, it encourages the use
of the table directly for data entry/edit, rather than the more appropriate
object, the "form". And I seem to recall seeing folks posting with problems
related to querying.

Reconsider using the lookup data type. If you change the data type to that
of the underlying ID which points to the looked-up value, you can use a
combobox in a form to display the looked up value.

Otherwise, when you query and try to find the value you see in the table,
you get nothing (... oh wait, that's what's happening, right?!).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob H]

Jeff,
I'll try to make it a bit clearer for you.
I have 1 main table for all the tools called tblTools, in which there is
a field called ToolTypeID, and yes it looks up to another table called
tblToolID by or through ther use of a combo box, which lists the many
different types of tools.The main reason for me using a combo box is to
make it easier for me to set the tool type in the tooltypeId
fields,rather than manually type each one for 3000 records.
Then my idea was to create queries on specified tooltypes, but I do take
your point about using a form to to this.

Thanks

 

[John W. Vinson]

I have a table with almost 3000 records of 11 different tool types.
I have created a table called tblToolID listing the 11 different types, and
in the tool table I have the toolID as a table/query lookup to tblToolID.
The tooltype is then selected from a combo box.

When I created a new query and put in "electrical" as tooltype as a
criteria, only 8 of the 400 electrical items are returned by the query.
When I put in anther type, say lifting, no records are returned from over
1800 records!
Here is a the SQL as an example:
SELECT tblTools.ToolType, tblTools.ManufacturerID, tblTools.ProductID,
tblTools.Size
FROM tblTools
WHERE (((tblTools.ToolType)="lifting"));

If I put in the criteria box under another feild say "name of product"
under Product ID, all the records are returned.
So why are no records returned from a criteria of tooltype, "Lifting"

Thanks

Exactly.

Your table *does not contain the word Lifting*.

It APPEARS to, but it actually contains a concealed numeric ID, because of
Microsoft's misdesigned Lookup Field type.

 

[Jeff Boyce]

Bob

I think I understand "why", I'm just saying it isn't a good idea (it causes
the kind of confusion you have run into).

Regards

Jeff Boyce
Microsoft Office/Access MVP