Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012

    Unanswered: query by code parameter


    I created a query that queries by a code, the database is of a heirarchical nature and each level has its own code, then the code has its own sub-code.
    for example 201 and 201.01 as a sub-code
    how do i put in a parameter where the input needs to be six characters long so that when i input 201 it will not allow me, but will only allow 201.01 or any other sub code.
    the way i found was by determining criterias for the query but that will give a long list of "or" and i do not know if it is good practice or not.

  2. #2
    Join Date
    May 2004
    New York State
    How about creating a combobox reading off the table, so it will display only the valid codes/subcodes? You can make it as intricate as you want, as it is the same as any other query. You can then make the selected code as input for your other query.


  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    suggest you use the where predicate
    eg where <mycode> like "blah*"

    if the required code was 201
    then you'd get 201 and all subcodes starting with 201

    if the required code was 201.0*
    then you'd get 201.0 and all subcodes starting with 201.0
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2012
    That wouldn't be feasible since I have a lot of subdisciplines (around 50)

    What i'm trying to do is query by sub code and get the related sub information, and then querying the entire database by sub_code and sub_inf, the problem is when i'm trying to do the nested query it will still give me redundant data:

    the SQL in the initial query:

    SELECT compet_inf.compet_inf_code, compet_inf.compet_name
    FROM compet_inf
    WHERE (((compet_inf.compet_inf_code) Like "*" & [Please enter competency code:] & "*"));

    now i'm trying to encorporate it's result as a query in this query:

    SELECT disc_name, subdisc_inf_code, subdisc_name, compet_inf_code, compet_name, lvl_number, know_inf_code, know_inf, perf_inf_code, perf_inf, und_inf_code, und_inf

    FROM disc_inf, subdisc_inf, know_inf, perf_inf, und_inf, compet_inf INNER JOIN lvl_inf ON compet_inf.compet_inf_code=lvl_inf.compet_code

    (SELECT nested_query.compet_inf_code, nested_query.compet_name FROM nested_query
    (((nested_query.compet_inf_code) Like "*" & [Please enter competency code:] & "*")));

    ...I somehow need to search the database by RESULT (and obtain only exact matches) of nested_query's compet_inf_code and nested_query's compet_name valuebut I'm such a noob at SQL
    Last edited by Intern101; 07-25-12 at 05:53.

Posting Permissions

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