Results 1 to 7 of 7
  1. #1
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2

    Unanswered: using part of criteria in a query

    I have a simple query where in the criteria I have [Enter Supervisor]. Supervisor is a field name. This works great and gives only the results for the suervisors name you enter into the window. My question is how do I simplify this process for my users so if they want results for a supervisor they just enter a portion of the name and they get the results. Example: instead of having to type Bob Smith, typing just a "b" would enter Bob Smith. There are only 10 possible names in this field.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Use a combo box, on a form that sets parameters
    or change the SQL to use a like clause

    where supervisor like '[enter supervisor]*'
    Or get your users to specify the wild card *
    In which case
    where supervisor like '[enter supervisor]'
    If the user wants bob, they type bob
    if they want andrew, angela or agnes type a*
    If they want matt or mark but not michael type ma*

    But in my books combo/list box on a form is better than getting users to type in parameters. Or possibly ypu have smarter users or simpler applications than I come accross
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2
    Here is the SQL. this works great.

    SELECT RAI.Lodged, Count(RAI.Lodged) AS CountOfLodged, RAI.Supervisor
    FROM RAI
    WHERE (((RAI.Date)>=DateAdd("m",-[Enter # of Months],Date())))
    GROUP BY RAI.Lodged, RAI.Supervisor
    HAVING (((RAI.Supervisor)=[Enter Supervisor]));


    I just want to create shortcuts when prompted for the [enter supervisor]. There are only 10 posible entries in the [enter supervisor]. ie. a=Adam Smith,
    b=Bob Jones, etc.

    Is this posible. It seems like I have done this before with IIF statments, but I can't figure out where to put it or the proper syntax.

  4. #4
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2
    Is what I am asking for not possible?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Healdem answered the question: use an expression such as:
    Code:
    HAVING (((RAI.Supervisor) Like [Enter Supervisor] & '*'));
    Have a nice day!

  6. #6
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2
    Thanks, this works great. I didn't understand the first response, thanks for clarifying.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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