dynamic queries

[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.....lets 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]

Okay, so what problems are you having?

[jl2886]

Okay, so what problems are you having?

I am not even sure where to begin. HOw do I do it?

[Rabbit]

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.

[ADezii]

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.....lets 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.

[NeoPa]

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.....lets 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.

[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 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.

[NeoPa]

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

[Rabbit]

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

[Denburt]

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.

[jl2886]

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

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

[jl2886]

Here is a sample of the search form I made.

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

[Denburt]

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!MyControl


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

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

[jl2886]

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.

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!MyControl


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

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

[Denburt]

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.

[NeoPa]

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

Received and understood :)
I'll come in again here if I'm needed, but it looks like the others have it covered atm.

[NeoPa]

As far as Dynamic Queries go generally, they can be changed in code by amending (or creating) a SQL string within your code.
This SQL can then be used in one of two ways :

1. Executed directly.
2. Saved away into a queryDef (Saved Query) and then executed from there.

QueryDefs can be created from scratch from code but it's usually easier to pop the SQL code into an existing QueryDef. This DOES have the drawback that an operator may not know what to expect from a QueryDef if they come to use it directly as your code may have changed it in the mean-time with specifics, relevant only to the instance when it was changed.
I hope this overview of the situation helps.

[jl2886]

Thank you, everyone. . I have a form with unbound controls with a visual basic code that does all the check filter stuff with qdef. Then I am going to create a button that opens the query. I will post the code soon. Thanks everyone

As far as Dynamic Queries go generally, they can be changed in code by amending (or creating) a SQL string within your code.
This SQL can then be used in one of two ways :

1. Executed directly.
2. Saved away into a queryDef (Saved Query) and then executed from there.

QueryDefs can be created from scratch from code but it's usually easier to pop the SQL code into an existing QueryDef. This DOES have the drawback that an operator may not know what to expect from a QueryDef if they come to use it directly as your code may have changed it in the mean-time with specifics, relevant only to the instance when it was changed.
I hope this overview of the situation helps.