Results 1 to 5 of 5
  1. #1
    kagoodwin13 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    181

    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 08:10 AM.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    kagoodwin13 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    181
    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 VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    kagoodwin13 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    181
    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, 07:24 AM
  2. Query returns ro results
    By MichealShinn in forum Queries
    Replies: 5
    Last Post: 02-21-2012, 01:24 PM
  3. Replies: 4
    Last Post: 08-16-2011, 05:54 PM
  4. Replies: 1
    Last Post: 03-09-2011, 02:04 AM
  5. Replies: 13
    Last Post: 01-13-2011, 10: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
  •  
Other Forums: Microsoft Office Forums