Results 1 to 4 of 4
  1. #1
    johnmerlino is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    97

    multiple like statements in criteria field not working

    Hey all,

    I'm using Access 2010. I have a query that should at least return 70 records, but it doesn't return anything. I need to match first and last name of name1 field of contacts table with first, last, and middle initial fields of temp_table or first and last name of name2 field of contacts table with first, last, and middle initial fields of temp_table.
    However, the contacts table contains a lot of extra characters that the temp_table won't have. Hence, I need to use LIKE. Also the last name of temp_table can be in either the name1 field or name2 field of contacts table and that's why I said "or" above to check the first, last, and middle initial in both name1 and name2 fields.
    So I came up with the below query but it didn't work:
    contacts table


    name1: AYBAR,ROBIN F &
    name2: % MARGARET FARMER
    temp_table
    last_name: MARGARET
    first_name: ROBIN
    middle_initial: F
    (note though there might not be a middle initial for a record in temp table!)
    This is query:
    Code:
    Field: name_1, name_2, address, city, us_state, zip_codes
    Table: contacts, contacts, contacts, contacts, contacts, contacts 
    Sort:
    Show:
    Criteria: (in name_1 column): Like "*[temp_table].[last_name]*" And Like "*[temp_table].[first_name]*" And Like "*temp_table.[middle_initial]*"
    Or: (in name_2 column) Like "*[temp_table].[last_name]*" And Like "*[temp_table].[first_name]*" And Like "*temp_table.[middle_initial]*"
    Thanks for any response.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    1 syntax is not correct
    2 logic is not correct

    Code:
    Field:   name_1,  name_2,  address,   city,        us_state, zip_codes
    Table: contacts, contacts, contacts, contacts, contacts, contacts 
    Sort:
    Show:
    Criteria: (in name_1 column): Like "*" & [temp_table].[last_name]  & "*"  (in name_2 column) like "*" & [temp_table].[first_name] & "*"
    Or:       (in name_1 column): Like "*" & [temp_table].[first_name] & "*"  (in name_2 column) Like "*" & [temp_table].[last_name]  & "*"

  3. #3
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    wait, it's not right

    Here it is:
    Code:
    Field:   name_1,  name_2,  address,   city,    us_state, zip_codes, first_name, last_name
    Table: contacts, contacts, contacts, contacts, contacts, contacts , temp_table, temp_table
    Sort:
    Show:     x          x       x           x       x           x        don't show don't show
    Criteria: (in first_name column): Like "*" & [contracts].[name1] & "*"  (in last_name column) like "*" & [[contracts].[name2] & "*"
    Or:       (in first_name column): Like "*" & [contracts].[name2] & "*"  (in last_name column) Like "*" & [[contracts].[name1]  & "*"

  4. #4
    johnmerlino is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    97
    It appears this has worked:
    Code:
    SELECT contacts.names_1, contacts.names_2, contacts.addresses, contacts.cities, contacts.us_states_and_canada, contacts.zip_codes
    FROM contacts, temp_query
    WHERE 
    (
    contacts.names_1  Like "*" & temp_query.first_name    & "*" 
    and 
    contacts.names_1  Like "*" & temp_query.last_name    & "*" 
    
    )
    OR
    (
    contacts.names_2  Like "*" & temp_query.first_name    & "*" 
    and 
    contacts.names_2  Like "*" & temp_query.last_name    & "*" 
    )
    ;
    Last edited by johnmerlino; 10-13-2010 at 07:40 PM.

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

Similar Threads

  1. If statements criteria
    By BED in forum Forms
    Replies: 4
    Last Post: 07-23-2010, 12:52 PM
  2. Replies: 0
    Last Post: 04-08-2010, 12:22 PM
  3. Replies: 17
    Last Post: 03-29-2010, 10:55 AM
  4. Query multiple field & criteria
    By fua in forum Access
    Replies: 2
    Last Post: 11-04-2009, 08:22 PM
  5. Multiple criteria query
    By DJ-Specter in forum Queries
    Replies: 1
    Last Post: 09-23-2009, 04:47 AM

Tags for this Thread

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