Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Where Clause in Access Select (Access 2003)

    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?

  2. #2
    Lounger
    Join Date
    Oct 2001
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Clause in Access Select (Access 2003)

    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.

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Clause in Access Select (Access 2003)

    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!

  4. #4
    Lounger
    Join Date
    Oct 2001
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Clause in Access Select (Access 2003)

    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.

  5. #5
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Clause in Access Select (Access 2003)

    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?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where Clause in Access Select (Access 2003)

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

  7. #7
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Clause in Access Select (Access 2003)

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Where Clause in Access Select (Access 2003)

    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".

  9. #9
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Clause in Access Select (Access 2003)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •