Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Problem with Searching a Drop-down list fiel (2003)

    When I build a text based query i.e. find "Bob" in a database and the field I am querying on is a look-up (drop-down list) field I get a data mismatch message.
    All look-up fields default to "number" for format so I understand that I'm asking the query to search for text and the text in that field is defined as a number, but......

    How can I find records with specific text that has been input using a drop-down list?

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

    Re: Query Problem with Searching a Drop-down list fiel (2003)

    Can you provide more information about the table(s) and field(s) involved?

  3. #3
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem with Searching a Drop-down list fiel (2003)

    This is probably not very helpful to your situation (unless it is your database to redesign) but Look-up fields are the spawn of the Devil - do not use them!
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem with Searching a Drop-down list fiel (2003)

    The database consists of customer complaint records. The fields are customer contact information, (name, address, telephone), complaint details, (complaint type--dropdown list--complaint date--comments (memo field) and complaint resolution details (who handled the complaint, what was done, when, customer verification of resolution).
    I am trying to build a report (based on a query) that will list the complaints by complaint type.
    For example Delivered Late is a complaint type. I'd like to be able to create parameters i.e. [Enter Complaint Type] that will bring up all the Delivered Late complaints.
    I have a number of queries operating using the parameter prompt.
    When I enter the text, I get a message indicating that there is a data mismatch between the data type in the field and the data type in the criteria.
    This is where I discovered that drop-down lists are number format.
    I am using text to search the database and of course the query won't work as it doesn't "see" any text in a "number" field.

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem with Searching a Drop-down list fiel (2003)

    Thanks for your comment Steve.
    Unfortunately I have to use drop-down lists to accomplish what I need to accomplish. I have a number of people entering data and I have to force them to choose specific options. If there is a better way to accomplish this--without drop-down lists, please let me know.

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

    Re: Query Problem with Searching a Drop-down list fiel (2003)

    That's still not very specific. If you wish, you can post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  7. #7
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem with Searching a Drop-down list fiel (2003)

    I hope this helps.
    I can't send part of the database as the data is confidential.
    I've attached two screen shots, the design view of the table I am querying and the query with the "data mismatch" message.

  8. #8
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem with Searching a Drop-down list fiel (2003)

    Here's the second screen shot

  9. #9
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem with Searching a Drop-down list fiel (2003)

    I found the answer.
    When querying a field that is created by a look-up table, use the field FROM THE LOOK-UP TABLE.
    Do not use the field from the main table.

    Thanks

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query Problem with Searching a Drop-down list

    There are two things you can do about this.

    * Create the drop downs on the forms you use, but not in the tables, or
    * If you create them at the table level, go back to the properties of the field, and change the column width of the first column to something greater than 0. Then when you look at the table you will see the number that is actually stored. It is OK to hide the number on forms, but at the table level you should see what is really there.
    Regards
    John



Posting Permissions

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