Parameter Query Not Working

[marcfromm]

I created a database with 4 tables called Items, Date, Type, and Titles.
The items Table uses combo boxes to enter the date, type, and titles from the
Date, Type, and Titles tables, since the choices are set. I can

enter records with no problems.

I created a parameter query that asks for the date, which works great to
display the items from the inputed date.
I created a second parameter query that asks for the Type, which does not
work. It displays no records even if I enter a known type.

Date Query:
SELECT Items.itemName, Items.itemType, Items.itemEpisode, Items.itemDate,
Items.itemComment, Items.itemimage
FROM Items
WHERE (((Items.itemDate)=[Enter a date]));

Type Query:
SELECT Items.itemName, Items.itemType, Items.itemEpisode, Items.itemDate,
Items.itemComment, Items.itemImage
FROM Items
WHERE (((Items.itemType)=[Enter an item type]));


Why does the date query work and not the type query?
I have no relationships setup between the tables. All the record information
is in the Items table.

 

[Jeff Boyce]

Is the [Items].[itemType] field defined as a "lookup" datatype in that
table. It could be that the table stores one value (the ID) but displays
another (the Type).

By the way, the word "Date", among others, is a reserved word in Access. If
you use this word as a name for a table or a field, both you and Access can
end up confused about which "date" you are talking about.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[marcfromm via AccessMonster.com]

Hi Jeff

I will change the table name from Date to YearDate, since I am only dealing
with years.
The “Item.itemType†datatype is “Text,†but under the “Lookup†tab I created
a "combo box" with "row source type" as Table/Query and "row source" is from
the Type table I made.

I just figured it out, my combo box was bound to the ID field (column 2).
This confuses me though, since the ID field is Autonumber thus it only has
numbers, while the Type field (column 1) has the actual terms in it. The
combo box displays the actual terms in my form and in the datasheet view. Why
did the combo box display the terms when bound to the ID column that only has
numbers?

By changing the bound column to 1 (the Type field) my query works.

Thanks for your help

Is the [Items].[itemType] field defined as a "lookup" datatype in that
table. It could be that the table stores one value (the ID) but displays
another (the Type).

By the way, the word "Date", among others, is a reserved word in Access. If
you use this word as a name for a table or a field, both you and Access can
end up confused about which "date" you are talking about.

I created a database with 4 tables called Items, Date, Type, and Titles.
The items Table uses combo boxes to enter the date, type, and titles from the

[quoted text clipped - 22 lines]

I have no relationships setup between the tables. All the record information
is in the Items table.

 

[John W. Vinson]

Why
did the combo box display the terms when bound to the ID column that only has
numbers?

Because the developers at Microsoft (erroneously) thought it would be a good
idea to have table datasheets be more "user friendly" by showing meaningful
data.

They ended up making the program much more user hostile because the actual
contents of the table are concealed from view, causing exactly the kind of
problem you're experiencing!

It was just an unwise design decision, in the opinions of a great many serious
Access developers.

John W. Vinson [MVP]

 

[Guest]

Did you create a lookup in the type field in the table? In that case try
entering your ID value instead of the text (i'm assuming you have something
like a typID).

hth