Very Simple Question ( about queries ) (1 Viewer)

[kalis09]

Hi all, I am brand new to microsoft access ( access 2002 ) and I have a simple question about a query that I made. I made a queury for a table that has CategoryID,SupplierID,ProductName,UnitPrice,UnitsInStock, and Im trying to sort the table bu the UnitPrice and The CategoryId.
For the unit price ( in the criteria box ) I put >20 for greater than $20 dollars, and it sorted nicely, then i put "Confections" in the criteria box for CategoryId and It gives the error message "Data Type Mismatch in criteria expression". Any help would be greatly apreciated. Thanks

 

[pdx_man]

It looks to me that CategoryID would contain numbers, and you are searching with text. Two different data types, so, Type Mismatch error.

 

[kalis09]

There are no # values in CategoryId , just Grains/Cereals, Beverages, Confections, Produce

 

[pdx_man]

Would you post your query here?

 

[kalis09]

I tried to attatch the file but it didnt work, how do i save the queury to my desktop?

 

[kalis09]

Here is the whole file, my query is called Confections Less Than $20

 

[pdx_man]

In the Design View of the query, go up to View, SQL View.... Copy and paste it here.

 

[kalis09]

SELECT Products.CategoryID, Products.SupplierID, Products.ProductName, Products.UnitPrice, Products.UnitsInStock
FROM Products
WHERE (((Products.CategoryID)="Confections") AND ((Products.UnitPrice)<20));


that gives me the error msg

 

[pdx_man]

Hmmm,
How large is your DB? Can you post the entire DB here? Zip it if necessary.

***EDIT***
There is an option at the bottom when you post to attach a file.

 

[kalis09]

No its too big it says , do u by chance have msn or icq?

 

[pdx_man]

Check your PM.

How big is it?

 

[kalis09]

Its over 2 mb, so it was to large to attach, but if you have msn messanger or icq i could send you the file.

 

[pdx_man]

Send it to the address I sent you. I can receive files up to 3 MB. I don't have msn or icq.

 

[kalis09]

Through my hotmail account, i can only attach files up to 1 mb

 

[pdx_man]

And you have tried to Zip the file? Create a copy of the DB, delete 75% of the records, zip the file, and either post it here, or email it to me.

 

[kalis09]

well my questions has now narrowed down, how to i sort text rows, in the criteria box i simply put in the text that i want to appear in the query?

 

[Pat Hartman]

Look at the table definition for the CategoryID field. Do you have it defined as a combobox or listbox? If so, you are seeing the text value but in reality, the stored value is numeric. I would remove the lookup from the table definition. That way your query will return numeric values that look like numeric values. Since we never show naked queries to the user, this should not be a problem. The user should see only forms and reports.

 

[veminc]

Have you CATEGORY table?

You have (probably) table called CATEGORY with fields CategoryID, CategoryName...
So, your query should be like:
SELECT Products.CategoryID, Category.CategoryName ,Products.SupplierID, Products.ProductName, Products.UnitPrice, Products.UnitsInStock
FROM Products, Category
WHERE (((Products.CategoryName)="Confections") AND ((Products.UnitPrice)<20));