Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    48

    Red face Unanswered: Runtime error using Dynamic Query

    I have gone through the following article

    ACC: Query by Form (QBF) Using Dynamic QueryDef (95/97)

    & accordingly tried to execute using Dynamic Query as follows :-

    Private Sub OKBtn_Click()
    Dim MyDatabase As Database
    Dim MyQueryDef As QueryDef
    Dim where As Variant

    Set MyDatabase = CurrentDb()

    If ObjectExists("Queries", "qryDynamic_QBF") = True Then
    MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
    MyDatabase.QueryDefs.Refresh
    End If

    where = Null


    where = where & " AND [Si M])>= " + Forms!Form1![Text0] + "'"
    where = where & " AND [Si X])>= " + Forms!Form1![Text0] + "'"
    where = where & " AND [Fe M])>= " + Forms!Form1![Text1] + "'"
    where = where & " AND [Fe X])>= " + Forms!Form1![Text1] + "'"

    Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", _
    "Select * from ProductMix " & (" where " + Mid(where, 6) & ";"))
    DoCmd.OpenQuery "qryDynamic_QBF"
    End Sub


    But the same gives an error message :-

    Extra ) in query expression '[Si M]>=0.5' AND [Si X]) >=0.5".

    whereas 0.5 is Text0 value.

    I further observed that in 2nd Text box i.e. Fe, I did not give any value & left blank but in query it contains the expression like [Si M]>=[Text1] AND [Si X]) >=[Text1].

    Any suggestion to overcome this problem will be highly appreciated.

  2. #2
    Join Date
    May 2008
    Posts
    48
    Oops! Criteria has wrongly been expressed. It should be read as :


    where = where & " AND (((ProductMix.[Si M])<=Forms!Form1![Text0]))"
    where = where & " AND (((ProductMix.[Si X])>=Forms!Form1![Text0]))"
    where = where & " AND (((ProductMix.[Fe M])<=Forms!Form1![Text1]))"
    where = where & " AND (((ProductMix.[Fe X])>=Forms!Form1![Text1]))"

  3. #3
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Isn't WHERE a reserved word in VBA ? perhaps a prefix or suffix to the name of the variable might be called for.

  4. #4
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Ok, just fixing the error you posted, you have an extra ")" at the end of your SQL ie & "));" needed to be &");"

    I'm guessing you're going to have quite a few more errors pop up with this chunk of code, but, as I don't generally run this type of query, I can't really say definitively.

    Since "WHERE" is a SQL keyword, naming a variable that sounds extremely dangerous to me, I would personally name your variable to something like "strWhere" so the system knows which is a WHERE and which is your variable.

    Make sure to put your Parens within the quotes and make sure to wrap any text/string information within a single quote 'like such'.

    Sam, hth

    ::Edit::
    I also notice you're using a combination of "+" and "&" for your string concatenation. The + will attempt to add the numeric value if there is one, whereas the & will just do a pure concatenation. i.e. "3" + "4" will come through as "7" whereas "3" & "4" will come through as "34".
    Last edited by SCrandall; 03-29-10 at 14:54.
    Good, fast, cheap...Pick 2.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you may also benefit from putting somehting at the start of the where clause

    eg
    strSQL = "Where 1=1"
    strSQL = strSQL & " AND [Si M])>= " & Forms!Form1![Text0]
    strSQL = strSQL & " AND [Si X])>= " & Forms!Form1![Text0]
    strSQL = strSQL & " AND [Fe M])>= " & Forms!Form1![Text1]
    strSQL = strSQL & " AND [Fe X])>= " & Forms!Form1![Text1]
    or
    Code:
    strSQL = "Where 1=1 AND [Si M])>= " & Forms!Form1![Text0] & " AND [Si X])>= " & Forms!Form1![Text0]  & " AND [Fe M])>= " & Forms!Form1![Text1]  & " AND [Fe X])>= " & Forms!Form1![Text1]
    remember text/string columns should be encapsulated in ' or " characters, if there is a " or ' in the text then use the escape character (repalce function)
    dates should be encapsualeted with "# and #" and be in US format mm/dd/yyyy

    to make code easier to read, especially when writing rfor the first time I prefer to use the chr$(34) to add a " symbol
    "where mytestxtcolum = ' & chr$(34) & atextvariable & chr$(34)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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