Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Czech Republic

    Unanswered: expressions in tables


    i'm using VC++ and MS Access 2000 and i have this problem

    i've created two tables. one containing information about different shape types and the second one containing information about concrete shapes. and i want to apply the rules of the types to the shape specific data. i'll give you very simple example

    TABLE shape_types (id VARCHAR), index INTEGER, xFormula VARCHAR, yFormula VARCHAR) ;

    'triangle', 1, '0', 'h * 2/3'
    'triangle', 2, '-w/2', '-h * 1/3'
    'triangle', 3, 'w/2', '-h * 1/3'
    'rectangle', 1, '-w/2', '-h/2'
    'rectangle', 2, '-w/2', 'h/2'
    'rectangle', 3, 'w/2', 'h/2'
    'rectangle', 4, 'w/2', '-h/2'

    TABLE shapes (name VARCHAR, type VARCHAR, w NUMBER, h NUMBER)

    'sq20', 'rectangle', 20.0, 20.0
    'rq10*30', 'rectangle', 10.0, 30.0
    'tr5', 'triangle', 5.0, 5.0

    i thought that when i want to recieve all the coordinates for the 'sq20' shape i would make a querry like

    SELECT index, EVAL(xFormula) AS x, EVAL(yFormula) AS y
    FROM shape_types, shapes
    WHERE type=id AND name='sq20'

    and the result would be
    1, -10.0, -10.0
    2, -10.0, 10.0
    3, 10.0, 10.0
    4, 10.0, -10.0

    but Access returns this:
    1, #Error, #Error
    2, #Error, #Error

    i've asked in different forums allready and no one could give me any answer (even if is like "it's impossible, sql doesn't allow that"). i've found a 'solution' construncting recordset from every single line separately, but it squeezes my eyes when i see it:

    make a recordset with textual form of the formulas

    SELECT index, xFormula, yFormula
    FROM shape_types, shapes
    WHERE type=id AND name='sq20'

    and then construct second recordset using formulas for each line separately (i use sort of pseudocode here, because it is more understandable)

    while (not eof) {
    str += "SELECT %d, %s AS x, %s AS y FROM shapes", index, rec.x, rec.y
    if (not eof)
    str += "UNION"

    well. it's definitelly working. but i suppose the union command takes more memory and more time (the shapes can be quite complex, more vertices, not only width/height but more parameters. the examples here are really informative).

    please help me if there is a simple sql solution of this.

    thank you.

  2. #2
    Join Date
    Apr 2003
    Santa Barbara, CA

    Re: expressions in tables

    The Eval() function doesn't know that the text character in the formula is a variable. So when all else fails write your own function.

    Function myEval(Formula$, MyNo#, MyVar$) As Single
    'Formula i.e. -h/2
    'MyNo i.e. 20
    'MyVar is "h"

    On Error GoTo myEval_error

    Formula = UCase(Trim(Formula))
    MyVar = UCase(MyVar)
    Do While InStr(Formula, MyVar)
    Formula = Left(Formula, InStr(Formula, MyVar) - 1) & MyNo & Mid(Formula, InStr(Formula, MyVar) + 1)

    myEval = Eval(Formula)
    Exit Function
    myEval = 0

    End Function

    Yvalue: myEval([yFormula],[y],"h")

Posting Permissions

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