Results 1 to 5 of 5
  1. #1
    jwilliams is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Location
    New Hampshire
    Posts
    2

    Using an "IIf" criteria to choose between two different "Like" selects

    I am setting up an Access database for a small school.


    The database contains a record per person. When we get a duplicate lastname field we set a family_id field to lastname followed by a number.
    So for example the first Smith family needs to family_id. But the second Smith family gets a family_id of smith1.
    To select an ordered list of Families I need a query that will use the family_id if it is set and if not use the the lastname to collect family members.

    My query uses a presorted table, CurStuByGrdTbl , of current students to produce the families directory by grade and alphabetized.
    The WellSchoolCommunityAll table is the entire database. So if the student entry has a valid family_id, matching pattern "*#" then
    I perform a Like with family.id otherwise I perform a Like with lastname.

    This query gets me nothing, no records.

    IIf(([CurStuByGrdTbl].[family_id]="*#"),[CurStuByGrdTbl].[family_id] Like [WellSchoolCommunityAll].[family_id],[CurStuByGrdTbl].[lastname] Like [WellSchoolCommunityAll].[lastname])

    got any clues?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,817
    I don't really understand why you need this. Why don't you just sort and group records on the family_ID?

    If you need to filter to a specific family, why use LIKE operator? Why not just filter on the family_ID?
    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
    jwilliams is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Location
    New Hampshire
    Posts
    2
    Quote Originally Posted by jwilliams View Post
    I am setting up an Access database for a small school.
    The database contains a record per person. When we get a duplicate lastname field we set a family_id field to lastname followed by a number.
    So for example the first Smith family needs to family_id. But the second Smith family gets a family_id of smith1.
    To select an ordered list of Families I need a query that will use the family_id if it is set and if not use the the lastname to collect family members.

    My query uses a presorted table, CurStuByGrdTbl , of current students to produce the families directory by grade and alphabetized.
    The WellSchoolCommunityAll table is the entire database. So if the student entry has a valid family_id, matching pattern "*#" then
    I perform a Like with family.id otherwise I perform a Like with lastname.

    This query gets me nothing, no records.

    IIf(([CurStuByGrdTbl].[family_id]="*#"),[CurStuByGrdTbl].[family_id] Like [WellSchoolCommunityAll].[family_id],[CurStuByGrdTbl].[lastname] Like [WellSchoolCommunityAll].[lastname])

    got any clues?

    I was hoping that I would not have to assign family_id values since a lastname without a family_id value would be unique.
    But if Access cannot do this then having every entry have a family id is the only solution.
    Thanks

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sure would be easier to have family_ID as an Autonumber - PK (and faster) and not have to mess with typing in the lastname and adding a number to the name to make it unique. Searching is a lot easier also.

    My $0.02..........

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,817
    If each family last name were guaranteed to be unique, there would not be an issue. But I find that unlikely. Names make very poor unique identifiers.

    I would not build unique identifier with even part of name.
    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.

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

Similar Threads

  1. Replies: 8
    Last Post: 07-15-2014, 05:56 PM
  2. Replies: 3
    Last Post: 12-17-2013, 08:27 AM
  3. Replies: 2
    Last Post: 11-14-2012, 04:47 PM
  4. Replies: 11
    Last Post: 03-29-2012, 02:32 PM

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