Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    11

    Unhappy Unanswered: Linking Forms & Queries w/wildcards

    I have a database looking up plant information (max height(m), max width(m), and the amount of light needed [shade medium sun]) from a table.

    I have linked the form to the database using
    [Forms]![Form1]![light] for light needed,
    [Forms]![Form1]![Height] for max height and
    [Forms]![Form1]![Width] for max width
    in the queries/criteria

    The problem is i need to use the wildcards less than, more than, less than and equal to, and more than and equal to certain heights.
    ( <, >, <=, >=)

    But the text box doesn't allow any wildcards so it comes up with the error:
    "The expression is typed incorrectly, or is too complex tobe evaluated. E.g a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how about two combo boxes comboW & comboH (i will type in the values i want) with values:
    <
    <=
    =
    >=
    >

    and some SQL along the lines of:

    dim strSQL as string
    strSQL = "SELECT [Light], [Hight], [Width] "
    strSQL = strSQL & "FROM yourTableName WHERE (([Height] "
    strSQL = strSQL & comboH.column(0)
    strSQL = strSQL & [Forms]![Form1]![Height]
    strSQL = strSQL &") AND ([Width] "
    strSQL = strSQL & comboW.column(0)
    strSQL = strSQL & [Forms]![Form1]![Width] & "));"

    me.form.recordsource = strSQL

    izy

  3. #3
    Join Date
    Sep 2003
    Posts
    11
    I wondered if a sql union query would work (seemed simpler to me than the combo box lookup) but it came up with the attached errors -
    Data type mismatch in criteria expression, when my union query was

    SELECT [Width]
    FROM [PLANT_DATA]
    WHERE [Width] = "[Forms]![Form1]!combow.column0"
    UNION SELECT [Height]
    FROM [PLANT_DATA]
    WHERE [Height]= "[Forms]![Form1]!comboh.column0"
    UNION SELECT [Light Needed]
    FROM [PLANT_DATA]
    WHere [Light Needed] = "[Forms]![Form1]combol.column0";
    Attached Thumbnails Attached Thumbnails sql.gif  

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    WHERE [Width] = "[Forms]![Form1]!combow.column0"
    does exactly that: it goes looking for a width = (literally) [Forms etc... which doesn't happen. you want to look for the value contained in [forms etc

    assuming width is numeric, try
    WHERE [Width] = " & [Forms]![Form1]!combow.column0 & "the rest of your sql

    izy

  5. #5
    Join Date
    Sep 2003
    Posts
    11

    Red face ?!

    Any simpler way of doing what i want?

Posting Permissions

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