dynamic queries

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jl2886
    New Member
    • Mar 2007
    • 83

    #1

    dynamic queries

    I would like to create a form that includes all the variables in my dataset, in which I can select a certain value, or a range of certain values for any amount of variables in one of tables and based on the values I put in the form, it will generate a query.

    For example, Lets say my table had four variables
    Face Amount AGE Life Expectency Policy Loan


    In the form I would like to specify.....let s say.....

    Face Amount 100,000-1000000
    AGE 70-85
    Life Expectency 60-100
    Policy Loan (nothing) I want to just leave it blank and thus no criteria will be selected
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    Okay, so what problems are you having?

    Comment

    • jl2886
      New Member
      • Mar 2007
      • 83

      #3
      Originally posted by Rabbit
      Okay, so what problems are you having?
      I am not even sure where to begin. HOw do I do it?

      Comment

      • Rabbit
        Recognized Expert MVP
        • Jan 2007
        • 12517

        #4
        There are two routes. One is to rebuild the query definition from code when they want to view the query. Another is to use the form's controls as criteria in the query. This will also require code to make sure the criteria entered is valid in a query.

        Comment

        • ADezii
          Recognized Expert Expert
          • Apr 2006
          • 8834

          #5
          Originally posted by jl2886
          I would like to create a form that includes all the variables in my dataset, in which I can select a certain value, or a range of certain values for any amount of variables in one of tables and based on the values I put in the form, it will generate a query.

          For example, Lets say my table had four variables
          Face Amount AGE Life Expectency Policy Loan


          In the form I would like to specify.....let s say.....

          Face Amount 100,000-1000000
          AGE 70-85
          Life Expectency 60-100
          Policy Loan (nothing) I want to just leave it blank and thus no criteria will be selected
          What you are requesting is a Criteria Form in which a User can specify multiple criteria relating to several Fields. The Values or Range of Values would be defined by you, the developer. A fairly complex undertaking unless you have had some programming experience.

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32669

            #6
            Originally posted by jl2886
            I would like to create a form that includes all the variables in my dataset, in which I can select a certain value, or a range of certain values for any amount of variables in one of tables and based on the values I put in the form, it will generate a query.

            For example, Lets say my table had four variables
            Face Amount AGE Life Expectency Policy Loan


            In the form I would like to specify.....let s say.....

            Face Amount 100,000-1000000
            AGE 70-85
            Life Expectency 60-100
            Policy Loan (nothing) I want to just leave it blank and thus no criteria will be selected
            I don't find your question very clear, but on the off-chance that it may be helpful, I'll post a link (Example Filtering on a Form.). If nothing else, it should give you some ideas.

            Comment

            • jl2886
              New Member
              • Mar 2007
              • 83

              #7
              Hey, I know how to filter a form. Is there anyway to construct a form that creates criteria statements for a query. So far, I have created a query with the fields from a table that I am intersted in. I unbound all of the variables in the form. I then can create a form filter that will filter cases from the query, but that's just the same thing as filtering the main table. How do I create a form...that allows me to input search criteria...then it brings up a query table with the criteria that I specify.

              Originally posted by NeoPa
              I don't find your question very clear, but on the off-chance that it may be helpful, I'll post a link (Example Filtering on a Form.). If nothing else, it should give you some ideas.

              Comment

              • NeoPa
                Recognized Expert Moderator MVP
                • Oct 2006
                • 32669

                #8
                I guess you didn't feel it worthwhile even to look at the thread then?

                Comment

                • Rabbit
                  Recognized Expert MVP
                  • Jan 2007
                  • 12517

                  #9
                  Originally posted by jl2886
                  Hey, I know how to filter a form. Is there anyway to construct a form that creates criteria statements for a query. So far, I have created a query with the fields from a table that I am intersted in. I unbound all of the variables in the form. I then can create a form filter that will filter cases from the query, but that's just the same thing as filtering the main table. How do I create a form...that allows me to input search criteria...then it brings up a query table with the criteria that I specify.
                  I refer you to posts #4 and #5

                  Comment

                  • Denburt
                    Recognized Expert Top Contributor
                    • Mar 2007
                    • 1356

                    #10
                    I would love to try and help but obviously there is a communications issue....

                    Answer post #4 since I do believe Rabbit is on the right track. Remember though that post #5 is very true also.

                    Comment

                    • jl2886
                      New Member
                      • Mar 2007
                      • 83

                      #11
                      OH NO, I created a search for a form for the database using that link. I already understand it.

                      Originally posted by NeoPa
                      I guess you didn't feel it worthwhile even to look at the thread then?

                      Comment

                      • jl2886
                        New Member
                        • Mar 2007
                        • 83

                        #12
                        Here is a sample of the search form I made.
                        Code:
                        Private Sub CheckFilter()
                            Dim strFilter As String, strOldFilter As String
                               strOldFilter = Me.Filter
                                If Me.Find_Case_Number > "" Then _
                                strFilter = strFilter & _
                                            " AND ( [LSI Case Number] LIKE '" & Me.Find_Case_Number & "')"
                                           
                                        If Me.Find_Insured_Name > "" Then _
                                strFilter = strFilter & _
                                            " AND ([Insured Name]='" & _
                                            Me.Find_Insured_Name & "')"
                           
                            If Me.Find_Policy_Number > "" Then _
                                strFilter = strFilter & _
                                            " AND ([Policy Number]='" & _
                                            Me.Find_Policy_Number & "')"
                        
                            If strFilter > "" Then strFilter = Mid(strFilter, 6)
                            If strFilter <> strOldFilter Then
                                Me.Filter = strFilter
                                Me.FilterOn = (strFilter > "")
                            End If
                        End Sub
                        This how I think it can be done, but it doesn't seem like anyone wants to help me.
                        First, create a form with all the fields I want to look at. Unbound them
                        (hopefully there is a way to create criteria statements for fields that I dont' want to show)
                        Then create a visual basic code that will input criteria based on the unbound values. the code should be similar to that of the search for a form???
                        Then create a docommand statement that generates q query based on the criteria specified in the code.

                        this is a guess, can anyone either, tell me how in words, or give me a sample.

                        Thanks

                        Comment

                        • Denburt
                          Recognized Expert Top Contributor
                          • Mar 2007
                          • 1356

                          #13
                          Originally posted by jl2886
                          This how I think it can be done, but it doesn't seem like anyone wants to help me.
                          as you can see from the number of people that posted in here that this statement isn't true, we are all glad to help but we need to be able to understand just what it is you need or want otherwise we can sit here and guess all day.

                          As Rabbit stated Post #4 you can change the query def itself (the actual saved query) every time you want to run this but you will also need to replace the where statement every time.
                          QueryDefs:
                          http://msdn2.microsoft.com/en-us/library/bb177500.aspx

                          OR

                          As Rabbit mentioned you can look at the query in design view and set the criteria for each field that will have criteria to look at your form for the information. Set the criteria in the query as such:
                          Forms!MyForm!My Control


                          Using your code you could make a few changes then
                          Declare and set your querydef then you should be set.
                          qdef.sql = strFilter

                          Code:
                          Private Sub CheckFilter()
                              Dim strFilter As String, strFilter As String
                          strFilter = " Where"
                                  If Me.Find_Case_Number > "" Then _
                                  strFilter = strFilter & _
                                              " ( [LSI Case Number] LIKE '" & Me.Find_Case_Number & "') And"
                                             
                                          If Me.Find_Insured_Name > "" Then _
                                  strFilter = strFilter & _
                                              " ([Insured Name]='" & _
                                              Me.Find_Insured_Name & "') AND"
                             
                              If Me.Find_Policy_Number > "" Then _
                                  strFilter = strFilter & _
                                              " ([Policy Number]='" & _
                                              Me.Find_Policy_Number & "') AND "
                          
                              If strFilter > "" Then strFilter = Left(strFilter, len(strFilter)-4)
                                  Qdf.sql = strFilter
                          
                              End If
                          qdf.close
                          End Sub

                          Comment

                          • jl2886
                            New Member
                            • Mar 2007
                            • 83

                            #14
                            I know everyone is trying to help, but maybe i am not explaining myself. I want to create a form that specifies the criteria of a query. When I click a button a query opens with the criteria and the field values that I specify. A check box, means show field. and the values in the text boxes specify the criteria.

                            Originally posted by Denburt
                            as you can see from the number of people that posted in here that this statement isn't true, we are all glad to help but we need to be able to understand just what it is you need or want otherwise we can sit here and guess all day.

                            As Rabbit stated Post #4 you can change the query def itself (the actual saved query) every time you want to run this but you will also need to replace the where statement every time.
                            QueryDefs:
                            http://msdn2.microsoft.com/en-us/library/bb177500.aspx

                            OR

                            As Rabbit mentioned you can look at the query in design view and set the criteria for each field that will have criteria to look at your form for the information. Set the criteria in the query as such:
                            Forms!MyForm!My Control


                            Using your code you could make a few changes then
                            Declare and set your querydef then you should be set.
                            qdef.sql = strFilter

                            Code:
                            Private Sub CheckFilter()
                                Dim strFilter As String, strFilter As String
                            strFilter = " Where"
                                    If Me.Find_Case_Number > "" Then _
                                    strFilter = strFilter & _
                                                " ( [LSI Case Number] LIKE '" & Me.Find_Case_Number & "') And"
                                               
                                            If Me.Find_Insured_Name > "" Then _
                                    strFilter = strFilter & _
                                                " ([Insured Name]='" & _
                                                Me.Find_Insured_Name & "') AND"
                               
                                If Me.Find_Policy_Number > "" Then _
                                    strFilter = strFilter & _
                                                " ([Policy Number]='" & _
                                                Me.Find_Policy_Number & "') AND "
                            
                                If strFilter > "" Then strFilter = Left(strFilter, len(strFilter)-4)
                                    Qdf.sql = strFilter
                            
                                End If
                            qdf.close
                            End Sub

                            Comment

                            • Denburt
                              Recognized Expert Top Contributor
                              • Mar 2007
                              • 1356

                              #15
                              The code that I posted is real close to what I think you want did you check out the link and or make anymore changes? I do not mind helping and I even post copy/paste code once in a while but that is rare, I like to help those that are willing to help themselves as you did earlier make an attempt. I like most people here are volunteering to HELP (keyword). My main goal here is to not only see you through an issue but make sure you understand the code used as well.

                              Comment

                              Working...