Where Clause in Access Select (Access 2003)

**mrtr** · 2006-02-02 01:22

I have the following statement in a table that has a combo box field for selecting a creator. However, I only want it to display the creators that are of the type "sculpter":

SELECT tblCreator.CreatorID, tblCreator.CreatorName, tblCreator.CreatorType FROM tblCreator ORDER BY tblCreator.CreatorName WHERE tblCreator.CreatorType = "Sculpter";

which I thought would give me the list of creators WHERE the types are Sculpters. I get back blanks. I havent used Where all that much in my statements. Can anyone tell me what is wrong with this syntax? All the text I read seemed to indicate that in order to filter for a text description I needed to use WHERE after the From and put it in quotes. I tried single quotes as well. Help?

**swan** · 2006-02-02 02:09

In your table, open it up to Datasheet View and click on the combo box and see if the selection available is "Sculpter. If it is, then your SQL is correct. However, to display the CreatorType that is associated with Sculpter, then make sure that your bound column setup is correct.

**mrtr** · 2006-02-02 02:42

I think you hit on something.

I have a relationship between a statue table and a creator table. It is one creator to many statues. The Creator table has a CreatorID as the primary key (and it is AutoNumber) and a Name field and a Type field. A combo box in the Statues table sets the data type to Number, apparently keyed from the Primary Key of CreatorID (AutoNumber). When I change the bound and go to test the selection, it tells me that I entered a text (bound) field in a numeric field and wont allow the update.

Unfortunately I am using the Creator ID or several other tables as well- I just didnt need to filter with them- so I wouldnt want to alter the primary key at this point.

So how can I now filter a list based on a text column built off of a primary key of number? And its only number because of the AutoNumber- I never use numbers except for calculations as data types!

**swan** · 2006-02-02 03:06

You would base your WHERE criteria on the Primary Key which is a number. Since this number is unique you would set it to look at the number and not the name of the field.

**mrtr** · 2006-02-02 03:31

My Creator table has a random data list of artists, writers, and sculpters, so the types are spread out all over the place and so are the numbers. I would still need to filter the number based on the Type. Would I "Group" the numbers WHERE the "Types" are sculpters?

**HansV** · 2006-02-02 09:07

What is the data type of the CreatorType field? Text or number?

**mrtr** · 2006-02-02 19:34

tblCreator:
CreatorID: Number (due to auto number)
CreatorName: Text
CreatorType: Text

**HansV** · 2006-02-02 19:44

Oh wait, the order in your SQL statement is incorrect: ORDER BY must always be the last clause:

SELECT CreatorID, CreatorName, CreatorType FROM tblCreator WHERE CreatorType = "Sculpter" ORDER BY CreatorName

Also check carefully that the table really contains "Sculpter" instead of the correct spelling "Sculptor".

**mrtr** · 2006-02-02 19:48

That was it Hans. I am sorry to say the mispelling is a correction I need to make in the database. Thanks again- you are the best!

Thread: Where Clause in Access Select (Access 2003)

Thread Tools