Results 1 to 5 of 5

Query by Form - Text box returns results, combo box does not

  1. #1
    kagoodwin13 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    99

    Query by Form - Text box returns results, combo box does not

    I've set up a query by form of my database. One of the fields is "Origin City" so that the user can search by the origin point of a shipment.

    In the query, the string is as follows:
    Code:
    Like "*" & [Forms]![Trucking Lane Search]![Origin City] & "*" And Like "*" & [Forms]![Trucking Lane Search]![Origin City] & "*"
    This string allows the user to enter in as much data as they want into the search field, or to leave the field null and search on other fields in the form instead.

    When I use a text box for this field, it works just fine. I type "Cityville," and it returns all results originating from Cityville. I can even type in "City" and it will return all results originating from Cityville.

    However, when I use a combo-box to generate the search field results (so that the user doesn't have to guess that I have saved it as Cityville rather than Townsport, the suburb of Cityville), the query returns blank.

    The combo-box "Origin City" is referenced to the table "Origin", in which "Origin City" is a field. The query is also referencing the same "Origin City" field in the "Origin" table. It is an exact match of the referenced table, so there shouldn't be any confusion on the computer's part.

    Even when I manually type in the exact match to a text box, the desired results are returned. However, an exact match from a combo box doesn't seem to work. I don't see how the computer is delineating between a manually-typed exact match and a combo-box-returned exact match. Any thoughts?
    Last edited by kagoodwin13; 02-22-2012 at 06:10 AM.

  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    27,030
    Is the combobox multi-column? If so, what are the fields of the RowSource? Which column is the combobox bound to? Show the combobox RowSource sql statement.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    kagoodwin13 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    99
    Quote Originally Posted by June7 View Post
    Is the combobox multi-column? If so, what are the fields of the RowSource? Which column is the combobox bound to? Show the combobox RowSource sql statement.
    The combobox is not multi-column. The combobox is bound to the column "Origin City" in the table "Origin."

    Code:
    SELECT [Origin].[Origin], [Origin].[Origin City] FROM [Origin];

  4. #4
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    27,030
    The RowSource SQL retrieves multiple fields then combobox is multi-column. Combobox properties should be:

    ColumnCount: 2
    BoundColumn: 2
    LimitToList: No
    AutoExpand: Yes
    ColumnWidths: 0";2"

    Never used a combobox for wildcard search. Don't really see need for it when the match to data is immediately apparent in the combobox.

    I just noticed that the criteria in the query repeats the Like ... Why?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    kagoodwin13 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    99
    Quote Originally Posted by June7 View Post
    The RowSource SQL retrieves multiple fields then combobox is multi-column. Combobox properties should be:

    ColumnCount: 2
    BoundColumn: 2
    LimitToList: No
    AutoExpand: Yes
    ColumnWidths: 0";2"

    Never used a combobox box for wildcard search. Don't really see need for it when the match to data is immediately apparent in the combobox.

    I just noticed that the criteria in the query repeats the Like ... Why?
    That worked! Thank you very much.

    I'm not sure why the Like is repeated. I got the string from someone else. He said it was necessary to allow any field to be either searched or have a null value.

    When I take away the wildcard on the query string, it breaks the functionality of the form to search with null values. I'd rather have a "search by any criteria" format than a "fill in every single combo-box to get any results" format.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-14-2012, 06:24 AM
  2. Query returns ro results
    By MichealShinn in forum Queries
    Replies: 5
    Last Post: 02-21-2012, 11:24 AM
  3. Replies: 4
    Last Post: 08-16-2011, 04:54 PM
  4. Replies: 1
    Last Post: 03-09-2011, 12:04 AM
  5. Replies: 13
    Last Post: 01-13-2011, 08:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums