dynamic queries | Member | | Join Date: Mar 2007
Posts: 82
| | |
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
|  | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: dynamic queries
Okay, so what problems are you having?
| | Member | | Join Date: Mar 2007
Posts: 82
| | | re: dynamic queries
Quote:
Originally Posted by Rabbit Okay, so what problems are you having? I am not even sure where to begin. HOw do I do it?
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: dynamic queries
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.
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: dynamic queries
Quote:
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.....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.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: dynamic queries
Quote:
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.....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.
| | Member | | Join Date: Mar 2007
Posts: 82
| | | re: dynamic queries
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. Quote:
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. | | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: dynamic queries
I guess you didn't feel it worthwhile even to look at the thread then?
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: dynamic queries
Quote:
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
|  | Moderator | | Join Date: Mar 2007 Location: Louisiana
Posts: 1,218
| | | re: dynamic queries
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.
| | Member | | Join Date: Mar 2007
Posts: 82
| | | re: dynamic queries
OH NO, I created a search for a form for the database using that link. I already understand it. Quote:
Originally Posted by NeoPa I guess you didn't feel it worthwhile even to look at the thread then? | | Member | | Join Date: Mar 2007
Posts: 82
| | | re: dynamic queries
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
| | Moderator | | Join Date: Mar 2007 Location: Louisiana
Posts: 1,218
| | | re: dynamic queries
Quote:
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!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
| | Member | | Join Date: Mar 2007
Posts: 82
| | | re: dynamic queries
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. Quote:
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!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
| | Moderator | | Join Date: Mar 2007 Location: Louisiana
Posts: 1,218
| | | re: dynamic queries
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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: dynamic queries
Quote:
Originally Posted by jl2886 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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: dynamic queries
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 : - Executed directly.
- 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.
| | Member | | Join Date: Mar 2007
Posts: 82
| | | re: dynamic queries
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 Quote:
Originally Posted by 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 : - Executed directly.
- 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. |  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
