View Poll Results: Dynamic Question

Voters
3. You may not vote on this poll
  • test

    1 33.33%
  • test

    2 66.67%
Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Dynamic Query

  1. #1
    Join Date
    Apr 2003
    Posts
    31

    Unanswered: Dynamic Query

    Is it possible to create a end-user dynamic query in which the user selects the desired propertis? Let me explain:

    Let's say there are three fields: Property Operator & Value

    Field Property contains the fields within a table, example tableQuestion has 4 fields; bidder, status, priority and question

    Field Operator would be something like: is, is not, contains, & wildcard

    Filed Value would be the desired string the user is searching for.

    Each field may change based on the desired search criteria. Is there a way to pass this information to a query in this format?

    If you can point me in the right direction I would be grateful.

    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Talking

    I've never seen it, but it should be able to be done. I would to attemp to use a form with three dropdowns fields. One the user has made his/her choices I would store those responses in a variable which can be used to run in the query.

  3. #3
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: Dynamic Query

    Originally posted by eciabattari
    Is it possible to create a end-user dynamic query in which the user selects the desired propertis? Let me explain:

    Let's say there are three fields: Property Operator & Value

    Field Property contains the fields within a table, example tableQuestion has 4 fields; bidder, status, priority and question

    Field Operator would be something like: is, is not, contains, & wildcard

    Filed Value would be the desired string the user is searching for.

    Each field may change based on the desired search criteria. Is there a way to pass this information to a query in this format?

    If you can point me in the right direction I would be grateful.

    Thanks
    You can build queries at RunTime.

    Dim Db as database, Qd as querydef, Quotes$
    Quotes$ = """"
    set Db = currentdb()
    set qd = Db.querydefs("ExistingQuery")

    sql$ = "Select MyTbl.* from MyTbl where MyTbl.Fld1 = " & Quotes$ & Me!Str & Quotes$

    qd.sql = sql$

    db.close
    set db = nothing
    set qd = nothing

    ExistingQuery is now hardcoded into your database.

    docmd.openquery "ExistingQuery"

  4. #4
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770
    Again, I never seen it done before. I;m thinking in term of theory & Logic. You can pass any data to a variable as long as you define the variable. where yours would be ex: var1, var2, var3 then run the variable through http://support.microsoft.com/newsgroups/default.aspxa loop. Try this site also it might be very helpful.

  5. #5
    Join Date
    Apr 2003
    Posts
    31

    Re: Dynamic Query

    Thanks for the informtion.

    By the way, what do I call my dropdown box & text box.

    I have dropdown box "Property" & "Variable" and textbox "Value"?

    Thanks for the help

  6. #6
    Join Date
    Apr 2003
    Posts
    31

    Re: Dynamic Query

    I forgot to say, when I attempt to run the function I get the following error: Invalid use of Me keyword

  7. #7
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Wink Extreme VB Forum

    http://www.visualbasicforum.com/

    This site is very good for tracking down errors. I'm a novice with vb and I track down all the error I had from here. Check it out.

  8. #8
    Join Date
    Apr 2003
    Posts
    31

    Modified Code

    I've modified the following code with the new SQL$
    but I get the following error "Object Required". What am I missing?

    Please help.

    ---------------------------------------------------------------
    Function ahtRunTimeQuery()
    ' Build queries at RunTime.

    Dim Db As Database, Qd As QueryDef, Quotes$
    Quotes$ = """"
    Set Db = CurrentDb()
    Set Qd = Db.QueryDefs("qryExistingQuery" )

    SQL$ = "Select tblQuestions.* from tblQuestions where '" & cboProperty.Text & "' & '" & cboOperator.Text & "' & '" & txtValue.Text & "'"

    Qd.SQL = SQL$

    Db.Close
    Set Db = Nothing
    Set Qd = Nothing

    ' ExistingQuery is now hardcoded into your database.
    DoCmd.OpenQuery "qryExistingQuery"

    End Function
    -------------------------------------------------------------------

  9. #9
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: Modified Code

    Originally posted by eciabattari
    I've modified the following code with the new SQL$
    but I get the following error "Object Required". What am I missing?

    Please help.

    ---------------------------------------------------------------
    Function ahtRunTimeQuery()
    ' Build queries at RunTime.

    Dim Db As Database, Qd As QueryDef, Quotes$
    Quotes$ = """"
    Set Db = CurrentDb()
    Set Qd = Db.QueryDefs("qryExistingQuery" )

    SQL$ = "Select tblQuestions.* from tblQuestions where '" & cboProperty.Text & "' & '" & cboOperator.Text & "' & '" & txtValue.Text & "'"

    Qd.SQL = SQL$

    Db.Close
    Set Db = Nothing
    Set Qd = Nothing

    ' ExistingQuery is now hardcoded into your database.
    DoCmd.OpenQuery "qryExistingQuery"

    End Function
    -------------------------------------------------------------------
    Be sure that you have Microsoft DAO 3.x Object Library selected in the references.

    From the VBA module:
    Tools: Referennces: Microsoft DAO 3.x Object Library

    oh and it looks like I didn't
    Dim SQL$

    sorry about that.

  10. #10
    Join Date
    Apr 2003
    Posts
    31

    Re: Modified Code

    First, I wanted to say thank you for helping me and I really do appreciate it.

    OK, I've checked and I do have the Referennces DAO 3.6 Object Library. However, I'm still getting errors.

    I went back and copied the original code, that you sent, and pasted it into my function. The only things that I have changed are as follows:
    1. ExistingQuery > qryExistingQuery
    2. Renamed MyTbl > tblQuestions
    3. Added Dim SQL$

    I have a form (titled Dynamic Search) that has the following items on it:
    1. Combobox, titled "Property"
    2. Combobox, titled "Operator"
    3. Textbox, titled "Value"
    3. Button to run query

    When I go to run the query I get the following error "Invalid use of Me Keyword". Why?

    The next thing I don't understand, how is the above three boxes being passed to the query? I want to understand how.

    Is this the format that is passed to the SQL query?

    SELECT tblQuestions.* FROM tblQuestions WHERE (((tblQuestions.Bidder)="LG"));

    Well, I want to say thanks for help me again and I hope you don't mind.

    Ed

    ------------------------------------------------------------------------------------
    Function ahtRunTimeQuery()
    ' Build queries at RunTime.

    Dim SQL$

    Dim Db As Database, Qd As QueryDef, Quotes$
    Quotes$ = """"
    Set Db = CurrentDb()
    Set Qd = Db.QueryDefs("qryExistingQuery")

    SQL$ = "Select tblQuestions.* from tblQuestions where tblQuestions.Fld1 = " & Quotes$ & Me!Str & Quotes$

    Qd.SQL = SQL$

    Db.Close
    Set Db = Nothing
    Set Qd = Nothing

    'ExistingQuery is now hardcoded into your database.

    DoCmd.OpenQuery "qryExistingQuery"

    End Function
    -----------------------------------------------------------------------------------

  11. #11
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: Modified Code

    Originally posted by eciabattari
    First, I wanted to say thank you for helping me and I really do appreciate it.

    OK, I've checked and I do have the Referennces DAO 3.6 Object Library. However, I'm still getting errors.

    I went back and copied the original code, that you sent, and pasted it into my function. The only things that I have changed are as follows:
    1. ExistingQuery > qryExistingQuery
    2. Renamed MyTbl > tblQuestions
    3. Added Dim SQL$

    I have a form (titled Dynamic Search) that has the following items on it:
    1. Combobox, titled "Property"
    2. Combobox, titled "Operator"
    3. Textbox, titled "Value"
    3. Button to run query

    When I go to run the query I get the following error "Invalid use of Me Keyword". Why?

    The next thing I don't understand, how is the above three boxes being passed to the query? I want to understand how.

    Is this the format that is passed to the SQL query?

    SELECT tblQuestions.* FROM tblQuestions WHERE (((tblQuestions.Bidder)="LG"));

    Well, I want to say thanks for help me again and I hope you don't mind.

    Ed

    ------------------------------------------------------------------------------------
    Function ahtRunTimeQuery()
    ' Build queries at RunTime.

    Dim SQL$

    Dim Db As Database, Qd As QueryDef, Quotes$
    Quotes$ = """"
    Set Db = CurrentDb()
    Set Qd = Db.QueryDefs("qryExistingQuery")

    SQL$ = "Select tblQuestions.* from tblQuestions where tblQuestions.Fld1 = " & Quotes$ & Me!Str & Quotes$

    Qd.SQL = SQL$

    Db.Close
    Set Db = Nothing
    Set Qd = Nothing

    'ExistingQuery is now hardcoded into your database.

    DoCmd.OpenQuery "qryExistingQuery"

    End Function
    -----------------------------------------------------------------------------------
    Should be able to plug this in with a FORM NAME change. qryExistingQuery needs to be a query already. When you run this it will change it to the sql statement below.

    Function ahtRunTimeQuery()
    ' Build queries at RunTime.

    Dim Db As Database, Qd As QueryDef, SQL$, frm As Form

    Set frm = Forms!MyForm 'change this to your form name
    Set Db = CurrentDb()
    Set Qd = Db.QueryDefs("qryExistingQuery")

    SQL$ = "Select tblQuestions.* from tblQuestions where tblQuestions.Property ='" & frm!cboProperty & "'and tblQuestions.Operator = '" & frm!cboOperator & "' and tblQuestions.value = '" & frm!txtvalue & "'"

    Qd.SQL = SQL$

    Db.Close
    Set Db = Nothing
    Set Qd = Nothing

    ' ExistingQuery is now hardcoded into your database.
    DoCmd.OpenQuery "qryExistingQuery"

    End Function

  12. #12
    Join Date
    Apr 2003
    Posts
    31

    GOT THE ANSWER

    I finially figured out the answer and it wan't that hard. I had the function located within a modules and not as a sub. Once I did that, everything started to work. I have a copy of the finished script below.

    I just have one last question.
    Currenlty the function works properly is the user selects "=". However, if the user selects "NOT", it doesn't work. I was going to do different cases. Would that work? If so, do you know of any sites that I can find information on Cases?

    Thanks for all the help.

    ----------------------------------------------------------------------------------
    Function ahtRunTimeQuery()
    ' Build queries at RunTime.

    Dim SQL$

    Dim Db As Database, Qd As QueryDef, Quotes$
    Quotes$ = """"
    Set Db = CurrentDb()
    Set Qd = Db.QueryDefs("qryExistingQuery")

    SQL$ = "SELECT tblQuestions.* FROM tblQuestions WHERE tblQuestions." & Me!cboProperty & _
    Me!cboOperator & Quotes$ & Me!txtValue & Quotes$

    Qd.SQL = SQL$

    Db.Close
    Set Db = Nothing
    Set Qd = Nothing

    'ExistingQuery is now hardcoded into your database.
    'DoCmd.OpenQuery "qryExistingQuery"
    DoCmd.Close
    DoCmd.OpenForm "frmBooleanSearchOutput"

    End Function
    ----------------------------------------------------------------------------------

  13. #13
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: GOT THE ANSWER

    Originally posted by eciabattari
    I finially figured out the answer and it wan't that hard. I had the function located within a modules and not as a sub. Once I did that, everything started to work. I have a copy of the finished script below.

    I just have one last question.
    Currenlty the function works properly is the user selects "=". However, if the user selects "NOT", it doesn't work. I was going to do different cases. Would that work? If so, do you know of any sites that I can find information on Cases?

    Thanks for all the help.

    ----------------------------------------------------------------------------------
    Function ahtRunTimeQuery()
    ' Build queries at RunTime.

    Dim SQL$

    Dim Db As Database, Qd As QueryDef, Quotes$
    Quotes$ = """"
    Set Db = CurrentDb()
    Set Qd = Db.QueryDefs("qryExistingQuery")

    SQL$ = "SELECT tblQuestions.* FROM tblQuestions WHERE tblQuestions." & Me!cboProperty & _
    Me!cboOperator & Quotes$ & Me!txtValue & Quotes$

    Qd.SQL = SQL$

    Db.Close
    Set Db = Nothing
    Set Qd = Nothing

    'ExistingQuery is now hardcoded into your database.
    'DoCmd.OpenQuery "qryExistingQuery"
    DoCmd.Close
    DoCmd.OpenForm "frmBooleanSearchOutput"

    End Function
    ----------------------------------------------------------------------------------
    Very good!

    <> is the operator for not equal

    Select case me!operator
    case "=": 'Do this
    case "<>"
    'Do A
    'Do B
    case ">=", ">"
    'Do A
    'Do B
    case "<=","<"
    'Do A
    'Do B
    else case
    'Do nothing
    end select

    the key to Select Case is that all of the case need to be associated with the Select Case variable, i.e. me!operator

    A way of filtering for multiple variables is:

    if this=that then
    else if that>this then
    else
    end if

    <F1> on the words Select Case in the VBA module will potentially pull up help and examples.

  14. #14
    Join Date
    Apr 2003
    Posts
    31

    Cases & Multi Lines

    Thanks for your help. I modified the function to included a case based on the Operator, and it works just perfectly.

    However, I have one last question, I hope.... Let's say I want to included another row of itmes, called "Property2, Operator2 & Value2". So the user can search on several item. Can I create a Case that looks at Operator1 & Operator2 and determines which on to use. What I mean is, if Operator2 is Null then only do Operator1; however, if Operator2 is equal to something, like "=" then only do Operator2 and not Operator1.

    I've tried to use IF ElseIf statements but I'm not getting it, but it doesn't want to work.

    Thanks for all the help.

    ----------------------------------------------------------------------------------
    Function BooleanSearch()

    ' Build queries at RunTime.
    Dim SQL$
    Dim Db As Database, Qd As QueryDef, Quotes$, LeftBraket, SingleRightBraket, DoubleRightBraket, TBL

    LeftBraket = "("
    SingleRightBraket = ")"
    DoubleRightBraket = "));"
    TBL = "(tblQuestions."
    Quotes$ = """"

    Set Db = CurrentDb()
    Set Qd = Db.QueryDefs("qryExistingQuery")
    Set ObjectType = cboOperator

    Select Case ObjectType
    Case "="
    SQL$ = "SELECT tblQuestions.* FROM tblQuestions WHERE tblQuestions." & Me!cboProperty & _
    Me!cboOperator & Quotes$ & Me!txtValue & Quotes$
    Case "Not"
    SQL$ = "SELECT tblQuestions.* FROM tblQuestions WHERE ((" & _
    Me!cboOperator & TBL & Me!cboProperty & ")=" & Quotes$ & Me!txtValue & _
    Quotes$ & DoubleRightBraket
    Case "Like"
    SQL$ = "SELECT tblQuestions.* FROM tblQuestions " & _
    "WHERE (((" & Me!cboProperty & ")" & Me!cboOperator & _
    Quotes$ & "*" & Me!txtValue & "*" & Quotes$ & DoubleRightBraket
    End Select

    Qd.SQL = SQL$

    Db.Close
    Set Db = Nothing
    Set Qd = Nothing

    DoCmd.Close
    DoCmd.OpenForm "frmBooleanSearchOutput"

    End Function
    ----------------------------------------------------------------------------------

  15. #15
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: Cases & Multi Lines

    Originally posted by eciabattari
    Thanks for your help. I modified the function to included a case based on the Operator, and it works just perfectly.

    However, I have one last question, I hope.... Let's say I want to included another row of itmes, called "Property2, Operator2 & Value2". So the user can search on several item. Can I create a Case that looks at Operator1 & Operator2 and determines which on to use. What I mean is, if Operator2 is Null then only do Operator1; however, if Operator2 is equal to something, like "=" then only do Operator2 and not Operator1.

    I've tried to use IF ElseIf statements but I'm not getting it, but it doesn't want to work.

    Thanks for all the help.

    ----------------------------------------------------------------------------------
    Function BooleanSearch()

    ' Build queries at RunTime.
    Dim SQL$
    Dim Db As Database, Qd As QueryDef, Quotes$, LeftBraket, SingleRightBraket, DoubleRightBraket, TBL

    LeftBraket = "("
    SingleRightBraket = ")"
    DoubleRightBraket = "));"
    TBL = "(tblQuestions."
    Quotes$ = """"

    Set Db = CurrentDb()
    Set Qd = Db.QueryDefs("qryExistingQuery")
    Set ObjectType = cboOperator

    Select Case ObjectType
    Case "="
    SQL$ = "SELECT tblQuestions.* FROM tblQuestions WHERE tblQuestions." & Me!cboProperty & _
    Me!cboOperator & Quotes$ & Me!txtValue & Quotes$
    Case "Not"
    SQL$ = "SELECT tblQuestions.* FROM tblQuestions WHERE ((" & _
    Me!cboOperator & TBL & Me!cboProperty & ")=" & Quotes$ & Me!txtValue & _
    Quotes$ & DoubleRightBraket
    Case "Like"
    SQL$ = "SELECT tblQuestions.* FROM tblQuestions " & _
    "WHERE (((" & Me!cboProperty & ")" & Me!cboOperator & _
    Quotes$ & "*" & Me!txtValue & "*" & Quotes$ & DoubleRightBraket
    End Select

    Qd.SQL = SQL$

    Db.Close
    Set Db = Nothing
    Set Qd = Nothing

    DoCmd.Close
    DoCmd.OpenForm "frmBooleanSearchOutput"

    End Function
    ----------------------------------------------------------------------------------
    Very Good.

    A multiple statement is the same process. Build each statement and combined them according to set theory. So AND means both conditions are necessary. OR means that only one condition needs to be valid.

    if Me!txtValue2 ="" then

    cri$ = "(" &Me!cboProperty1 & Me!cboOperator1 & Quotes$ & "*" & Me!txtValue1 & "*" & Quotes$ & ")

    elseif len(Me!txtvalue2) >1 then

    cri$ = "(" &Me!cboProperty1 & Me!cboOperator1 & Quotes$ & "*" & Me!txtValue1 & "*" & Quotes$ & ") OR (" & Me!cboProperty2 & Me!cboOperator2 & Quotes$ & "*" & Me!txtValue2 & "*" & Quotes$ & ")"

    else

    msgbox "No criteria set"

    end if

Posting Permissions

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