If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I don't know of an easy way to do what you are looking to do in Access. I
think that the earlier advice to build the sql string in code may solve your problem. It should result in a much simpler sql statement because you won't need all of the OR conditions for null cases. Instead, the VBA code would look at each control value and ignore it if it is null, or append the appropriate SQL string if it is not. There are many ways that you could approach the VBA code, but I'll show a few typical lines (you'll need a reference to DAO): Dim db as DAO.Database Dim qdf as DAO.QueryDef Dim strSQL as String Dim strWhere as String Dim StrAnd as String StrWhere = "" StrAnd = "" If Nz(Me.Ctrl1,"") "" Then strWhere = " QueryField1 Like '*" & Me.Ctrl1 & "*'" strAnd = " AND" Endif If Nz(Me.Ctrl2,"") "" Then strWhere = strAND & " QueryField2 Like '*" & Me.Ctrl2 & "*'" strAnd = " AND" Endif 'Go through the rest of the ctrls StrSQL = "Select ... (Enter Your Base Select Statement)" StrSQL = strSQL & " WHERE" & strWhere Set db = CurrentDb Set qdf = db.QueryDefs("YourQueryName") qdf.sql = strSQL set qdf = nothing set db = nothing Note though, that you may not be able to change the sql of a query that is bound to an open subform, so you may have to temporarily change the data source of the subform to nothing, then change the query, then change the data source back. I'm not sure, I haven't tried that before. Also, there are obviously other ways that you could loop through the controls, and set the criteria, but it would depend somewhat on the data types and other things. Also, the above assumes that you are adding LIKE criteria in text fields, you would have to modify these somewhat to give the exact criteria that you would be looking for. I think that I have seen some earlier posts with precoded query forms that would probably have some very good sample code. You may want to do some google searches of the Access newsgroup to find some if you do go that route. HTH, Ted Allen "rgrantz" wrote: Okay, I've got a pretty big problem here; I'm trying to get rid of about 45 extra minutes of crap labor for 4 people at once, as well as cutting down on duplicative data and reporting time. I'm almost there now, and the 4 people are ecstatic about what's here so far. However, there is ONE thing missing that I'm having a hard time doing: This manager I'm making this for has about 13 different criteria he likes to change and see new data for (customer name, who entered it, what date it was received, what date range it was entered, what date range it was processed, who processed it, what machine it was made on, who packaged it, who shipped it, etc. etc.). Currently he uses a combination of the Autofilter utility and the Dcount(Data!...etc.) formula in Excel, which returns data based on any criteria put in an entire row, which is nice. He can change any criteria along the entire row, adding some, changing some, deleting others, etc., and Excel just uses the whole row to continuously dynamically change the records returned as the criteria change (ie. he enters a particular name for OrderEnteredBy, and only sees those records, then he enters a name in OrderShippedBy, and sees only records with BOTH criteria, then he enters another one, etc. etc.., and when he deletes a criteria, the records reflect the additional records that no longer have criteria there, but still meet the OTHER criteria. You get what I'm saying here, right? It's pretty much being able to stack 13 criteria, with each criteria field also having an "ALL" (or technically, "NONE", for no criteria) choice in addition to actual criteria to apply. I need to do this on a form, and duplicate how the data changes after each criteria is altered. However, there are too many criteria fields, apparently, for Access to handle using the query builder. I can use the [forms]![FormName]![UnboundCriteriaFieldName] in the criteria cell and [forms]![FormName]![UnboundCriteriaFieldName] is null in the "Or" cell of the query builder, and this works great for about 3 fields. He has too many that he wants to see, though, and Access returns errors about the query being too complex, or the form starts returning unpredictable results. It seems very strange to me that Access can continually compound filters on a form using the "filter menu" for different fields, and that Excel can do all this with one simple formula, but that I can't get an Access form to do it. Someone, please help me, this thing I'm making is PERFECT and has solved 12 big problems, but if the manager can't get this ability to change these multiple critieria and see the records change as he does so (and be able to delete criteria as well as change them), then the whole thing's a no go. All I've been able to think of so far is to make 500 different queries and run a Select Case on the AfterUpdate of every unbound criteria control, which cycles through to see which fields are null and which have criteria and run the corresponding query to refresh the form with. This would be insane. Someone mentioned using a SQL string and code to change the query results, rather than using specific queries, but I either don't know how to do this or the SQL string would be too complex too (don't Access queries made using the query builder represent SQL strings anyway?) Someone, please help, I can't help thinking there MUST be a way to do this. I'm sorry to sound deperate, but the idea of canning this thing and having 4 people spend an extra 45 minutes a day on completely duplicative data, w/ more possibilities for error, less flexibility for reporting, etc., is driving me crazy. It's not helping the 4 people out either. And everyone's on board, even this manager, if I can just get this one little gimmicky thing going. Thanks for ANY help, I would really appreciate it. I can buy lunch for anyone who helps me come up w/ a solution. |
#2
|
|||
|
|||
Complex query criteria - desperate appeal
Okay, I've got a pretty big problem here;
I'm trying to get rid of about 45 extra minutes of crap labor for 4 people at once, as well as cutting down on duplicative data and reporting time. I'm almost there now, and the 4 people are ecstatic about what's here so far. However, there is ONE thing missing that I'm having a hard time doing: This manager I'm making this for has about 13 different criteria he likes to change and see new data for (customer name, who entered it, what date it was received, what date range it was entered, what date range it was processed, who processed it, what machine it was made on, who packaged it, who shipped it, etc. etc.). Currently he uses a combination of the Autofilter utility and the Dcount(Data!...etc.) formula in Excel, which returns data based on any criteria put in an entire row, which is nice. He can change any criteria along the entire row, adding some, changing some, deleting others, etc., and Excel just uses the whole row to continuously dynamically change the records returned as the criteria change (ie. he enters a particular name for OrderEnteredBy, and only sees those records, then he enters a name in OrderShippedBy, and sees only records with BOTH criteria, then he enters another one, etc. etc.., and when he deletes a criteria, the records reflect the additional records that no longer have criteria there, but still meet the OTHER criteria. You get what I'm saying here, right? It's pretty much being able to stack 13 criteria, with each criteria field also having an "ALL" (or technically, "NONE", for no criteria) choice in addition to actual criteria to apply. I need to do this on a form, and duplicate how the data changes after each criteria is altered. However, there are too many criteria fields, apparently, for Access to handle using the query builder. I can use the [forms]![FormName]![UnboundCriteriaFieldName] in the criteria cell and [forms]![FormName]![UnboundCriteriaFieldName] is null in the "Or" cell of the query builder, and this works great for about 3 fields. He has too many that he wants to see, though, and Access returns errors about the query being too complex, or the form starts returning unpredictable results. It seems very strange to me that Access can continually compound filters on a form using the "filter menu" for different fields, and that Excel can do all this with one simple formula, but that I can't get an Access form to do it. Someone, please help me, this thing I'm making is PERFECT and has solved 12 big problems, but if the manager can't get this ability to change these multiple critieria and see the records change as he does so (and be able to delete criteria as well as change them), then the whole thing's a no go. All I've been able to think of so far is to make 500 different queries and run a Select Case on the AfterUpdate of every unbound criteria control, which cycles through to see which fields are null and which have criteria and run the corresponding query to refresh the form with. This would be insane. Someone mentioned using a SQL string and code to change the query results, rather than using specific queries, but I either don't know how to do this or the SQL string would be too complex too (don't Access queries made using the query builder represent SQL strings anyway?) Someone, please help, I can't help thinking there MUST be a way to do this. I'm sorry to sound deperate, but the idea of canning this thing and having 4 people spend an extra 45 minutes a day on completely duplicative data, w/ more possibilities for error, less flexibility for reporting, etc., is driving me crazy. It's not helping the 4 people out either. And everyone's on board, even this manager, if I can just get this one little gimmicky thing going. Thanks for ANY help, I would really appreciate it. I can buy lunch for anyone who helps me come up w/ a solution. |
#3
|
|||
|
|||
P.s. You can find a lot of other code samples and sample applications by
doing a google search. The following will search for query by form: http://groups.google.com/groups?hl=e...ublic.access.* You can tweak this by also looking for dynamic sql or other key words that you think may help. Also, you may want to look at a microsoft KB article at: http://support.microsoft.com/?id=210242 HTH, Ted Allen "Ted Allen" wrote: I don't know of an easy way to do what you are looking to do in Access. I think that the earlier advice to build the sql string in code may solve your problem. It should result in a much simpler sql statement because you won't need all of the OR conditions for null cases. Instead, the VBA code would look at each control value and ignore it if it is null, or append the appropriate SQL string if it is not. There are many ways that you could approach the VBA code, but I'll show a few typical lines (you'll need a reference to DAO): Dim db as DAO.Database Dim qdf as DAO.QueryDef Dim strSQL as String Dim strWhere as String Dim StrAnd as String StrWhere = "" StrAnd = "" If Nz(Me.Ctrl1,"") "" Then strWhere = " QueryField1 Like '*" & Me.Ctrl1 & "*'" strAnd = " AND" Endif If Nz(Me.Ctrl2,"") "" Then strWhere = strAND & " QueryField2 Like '*" & Me.Ctrl2 & "*'" strAnd = " AND" Endif 'Go through the rest of the ctrls StrSQL = "Select ... (Enter Your Base Select Statement)" StrSQL = strSQL & " WHERE" & strWhere Set db = CurrentDb Set qdf = db.QueryDefs("YourQueryName") qdf.sql = strSQL set qdf = nothing set db = nothing Note though, that you may not be able to change the sql of a query that is bound to an open subform, so you may have to temporarily change the data source of the subform to nothing, then change the query, then change the data source back. I'm not sure, I haven't tried that before. Also, there are obviously other ways that you could loop through the controls, and set the criteria, but it would depend somewhat on the data types and other things. Also, the above assumes that you are adding LIKE criteria in text fields, you would have to modify these somewhat to give the exact criteria that you would be looking for. I think that I have seen some earlier posts with precoded query forms that would probably have some very good sample code. You may want to do some google searches of the Access newsgroup to find some if you do go that route. HTH, Ted Allen "rgrantz" wrote: Okay, I've got a pretty big problem here; I'm trying to get rid of about 45 extra minutes of crap labor for 4 people at once, as well as cutting down on duplicative data and reporting time. I'm almost there now, and the 4 people are ecstatic about what's here so far. However, there is ONE thing missing that I'm having a hard time doing: This manager I'm making this for has about 13 different criteria he likes to change and see new data for (customer name, who entered it, what date it was received, what date range it was entered, what date range it was processed, who processed it, what machine it was made on, who packaged it, who shipped it, etc. etc.). Currently he uses a combination of the Autofilter utility and the Dcount(Data!...etc.) formula in Excel, which returns data based on any criteria put in an entire row, which is nice. He can change any criteria along the entire row, adding some, changing some, deleting others, etc., and Excel just uses the whole row to continuously dynamically change the records returned as the criteria change (ie. he enters a particular name for OrderEnteredBy, and only sees those records, then he enters a name in OrderShippedBy, and sees only records with BOTH criteria, then he enters another one, etc. etc.., and when he deletes a criteria, the records reflect the additional records that no longer have criteria there, but still meet the OTHER criteria. You get what I'm saying here, right? It's pretty much being able to stack 13 criteria, with each criteria field also having an "ALL" (or technically, "NONE", for no criteria) choice in addition to actual criteria to apply. I need to do this on a form, and duplicate how the data changes after each criteria is altered. However, there are too many criteria fields, apparently, for Access to handle using the query builder. I can use the [forms]![FormName]![UnboundCriteriaFieldName] in the criteria cell and [forms]![FormName]![UnboundCriteriaFieldName] is null in the "Or" cell of the query builder, and this works great for about 3 fields. He has too many that he wants to see, though, and Access returns errors about the query being too complex, or the form starts returning unpredictable results. It seems very strange to me that Access can continually compound filters on a form using the "filter menu" for different fields, and that Excel can do all this with one simple formula, but that I can't get an Access form to do it. Someone, please help me, this thing I'm making is PERFECT and has solved 12 big problems, but if the manager can't get this ability to change these multiple critieria and see the records change as he does so (and be able to delete criteria as well as change them), then the whole thing's a no go. All I've been able to think of so far is to make 500 different queries and run a Select Case on the AfterUpdate of every unbound criteria control, which cycles through to see which fields are null and which have criteria and run the corresponding query to refresh the form with. This would be insane. Someone mentioned using a SQL string and code to change the query results, rather than using specific queries, but I either don't know how to do this or the SQL string would be too complex too (don't Access queries made using the query builder represent SQL strings anyway?) Someone, please help, I can't help thinking there MUST be a way to do this. I'm sorry to sound deperate, but the idea of canning this thing and having 4 people spend an extra 45 minutes a day on completely duplicative data, w/ more possibilities for error, less flexibility for reporting, etc., is driving me crazy. It's not helping the 4 people out either. And everyone's on board, even this manager, if I can just get this one little gimmicky thing going. Thanks for ANY help, I would really appreciate it. I can buy lunch for anyone who helps me come up w/ a solution. |
#4
|
|||
|
|||
pps, okay, one more thing. One of the things that I use most often that I
didn't mention before is to populate list boxes with data (such as all project managers, or project categories), which allows users to select multiple options. I then build a string of the selected ID's and use syntax such as: "Myfield In (" & strList & ")" to limit the query to the selected ID's from the appropriate field. I'm not sure if this would be helpful in your case, but I find it very useful in that it allows the user to come up with more combinations. Post back if you are interested and I can post some vba syntax for building the string from the list box. -Ted Allen "Ted Allen" wrote: I don't know of an easy way to do what you are looking to do in Access. I think that the earlier advice to build the sql string in code may solve your problem. It should result in a much simpler sql statement because you won't need all of the OR conditions for null cases. Instead, the VBA code would look at each control value and ignore it if it is null, or append the appropriate SQL string if it is not. There are many ways that you could approach the VBA code, but I'll show a few typical lines (you'll need a reference to DAO): Dim db as DAO.Database Dim qdf as DAO.QueryDef Dim strSQL as String Dim strWhere as String Dim StrAnd as String StrWhere = "" StrAnd = "" If Nz(Me.Ctrl1,"") "" Then strWhere = " QueryField1 Like '*" & Me.Ctrl1 & "*'" strAnd = " AND" Endif If Nz(Me.Ctrl2,"") "" Then strWhere = strAND & " QueryField2 Like '*" & Me.Ctrl2 & "*'" strAnd = " AND" Endif 'Go through the rest of the ctrls StrSQL = "Select ... (Enter Your Base Select Statement)" StrSQL = strSQL & " WHERE" & strWhere Set db = CurrentDb Set qdf = db.QueryDefs("YourQueryName") qdf.sql = strSQL set qdf = nothing set db = nothing Note though, that you may not be able to change the sql of a query that is bound to an open subform, so you may have to temporarily change the data source of the subform to nothing, then change the query, then change the data source back. I'm not sure, I haven't tried that before. Also, there are obviously other ways that you could loop through the controls, and set the criteria, but it would depend somewhat on the data types and other things. Also, the above assumes that you are adding LIKE criteria in text fields, you would have to modify these somewhat to give the exact criteria that you would be looking for. I think that I have seen some earlier posts with precoded query forms that would probably have some very good sample code. You may want to do some google searches of the Access newsgroup to find some if you do go that route. HTH, Ted Allen "rgrantz" wrote: Okay, I've got a pretty big problem here; I'm trying to get rid of about 45 extra minutes of crap labor for 4 people at once, as well as cutting down on duplicative data and reporting time. I'm almost there now, and the 4 people are ecstatic about what's here so far. However, there is ONE thing missing that I'm having a hard time doing: This manager I'm making this for has about 13 different criteria he likes to change and see new data for (customer name, who entered it, what date it was received, what date range it was entered, what date range it was processed, who processed it, what machine it was made on, who packaged it, who shipped it, etc. etc.). Currently he uses a combination of the Autofilter utility and the Dcount(Data!...etc.) formula in Excel, which returns data based on any criteria put in an entire row, which is nice. He can change any criteria along the entire row, adding some, changing some, deleting others, etc., and Excel just uses the whole row to continuously dynamically change the records returned as the criteria change (ie. he enters a particular name for OrderEnteredBy, and only sees those records, then he enters a name in OrderShippedBy, and sees only records with BOTH criteria, then he enters another one, etc. etc.., and when he deletes a criteria, the records reflect the additional records that no longer have criteria there, but still meet the OTHER criteria. You get what I'm saying here, right? It's pretty much being able to stack 13 criteria, with each criteria field also having an "ALL" (or technically, "NONE", for no criteria) choice in addition to actual criteria to apply. I need to do this on a form, and duplicate how the data changes after each criteria is altered. However, there are too many criteria fields, apparently, for Access to handle using the query builder. I can use the [forms]![FormName]![UnboundCriteriaFieldName] in the criteria cell and [forms]![FormName]![UnboundCriteriaFieldName] is null in the "Or" cell of the query builder, and this works great for about 3 fields. He has too many that he wants to see, though, and Access returns errors about the query being too complex, or the form starts returning unpredictable results. It seems very strange to me that Access can continually compound filters on a form using the "filter menu" for different fields, and that Excel can do all this with one simple formula, but that I can't get an Access form to do it. Someone, please help me, this thing I'm making is PERFECT and has solved 12 big problems, but if the manager can't get this ability to change these multiple critieria and see the records change as he does so (and be able to delete criteria as well as change them), then the whole thing's a no go. All I've been able to think of so far is to make 500 different queries and run a Select Case on the AfterUpdate of every unbound criteria control, which cycles through to see which fields are null and which have criteria and run the corresponding query to refresh the form with. This would be insane. Someone mentioned using a SQL string and code to change the query results, rather than using specific queries, but I either don't know how to do this or the SQL string would be too complex too (don't Access queries made using the query builder represent SQL strings anyway?) Someone, please help, I can't help thinking there MUST be a way to do this. I'm sorry to sound deperate, but the idea of canning this thing and having 4 people spend an extra 45 minutes a day on completely duplicative data, w/ more possibilities for error, less flexibility for reporting, etc., is driving me crazy. It's not helping the 4 people out either. And everyone's on board, even this manager, if I can just get this one little gimmicky thing going. Thanks for ANY help, I would really appreciate it. I can buy lunch for anyone who helps me come up w/ a solution. |
#5
|
|||
|
|||
Ted:
Thanks very much for the posts, it is greatly appreciated. I'm curious about one thing: I made an ASP website using an Access DB backend, and made a custom search page using exactly what I'm talking about, but each drop-down list also had an "ALL" choice, which I believe passed the "%" wildcard to the search. Why can we not use a "*" or "%" value in the form, which passes a wildcard to the query as opposed to a Null? In the page (which, again, is using ASP and MS Access), 10 different criteria listboxes have the ALL choice, and the value passed to the query is %: option value="%"ALL/option The ASP in the query language (on the results page) is: SELECT * FROM SearchPage WHERE (Country LIKE '%::Country::%' AND Sale2 LIKE '%::Sale2::%' AND Whatever LIKE '%::Whatever::%' AND etc. etc.') ORDER BY ::SortOrder:: ASC, Name ASC" fp_sDefault="Country=&Genre2=&Allegiance=&Associat ion=&Association2=&SortOrd er=" fp_sNoRecords="NO ITEMS FOUND: TRY OTHER CRITERIA" Now, I know this is Frontpage extensions and ASP, but it IS a field passing the wildcard criteria to SQL-based language in ASP. Is there really no way to pass a "*" or some kind of "anything" criteria to a parameter in JUST Access? I mean, this would be a magic bullet. Thanks again. "Ted Allen" wrote in message ... pps, okay, one more thing. One of the things that I use most often that I didn't mention before is to populate list boxes with data (such as all project managers, or project categories), which allows users to select multiple options. I then build a string of the selected ID's and use syntax such as: "Myfield In (" & strList & ")" to limit the query to the selected ID's from the appropriate field. I'm not sure if this would be helpful in your case, but I find it very useful in that it allows the user to come up with more combinations. Post back if you are interested and I can post some vba syntax for building the string from the list box. -Ted Allen "Ted Allen" wrote: I don't know of an easy way to do what you are looking to do in Access. I think that the earlier advice to build the sql string in code may solve your problem. It should result in a much simpler sql statement because you won't need all of the OR conditions for null cases. Instead, the VBA code would look at each control value and ignore it if it is null, or append the appropriate SQL string if it is not. There are many ways that you could approach the VBA code, but I'll show a few typical lines (you'll need a reference to DAO): Dim db as DAO.Database Dim qdf as DAO.QueryDef Dim strSQL as String Dim strWhere as String Dim StrAnd as String StrWhere = "" StrAnd = "" If Nz(Me.Ctrl1,"") "" Then strWhere = " QueryField1 Like '*" & Me.Ctrl1 & "*'" strAnd = " AND" Endif If Nz(Me.Ctrl2,"") "" Then strWhere = strAND & " QueryField2 Like '*" & Me.Ctrl2 & "*'" strAnd = " AND" Endif 'Go through the rest of the ctrls StrSQL = "Select ... (Enter Your Base Select Statement)" StrSQL = strSQL & " WHERE" & strWhere Set db = CurrentDb Set qdf = db.QueryDefs("YourQueryName") qdf.sql = strSQL set qdf = nothing set db = nothing Note though, that you may not be able to change the sql of a query that is bound to an open subform, so you may have to temporarily change the data source of the subform to nothing, then change the query, then change the data source back. I'm not sure, I haven't tried that before. Also, there are obviously other ways that you could loop through the controls, and set the criteria, but it would depend somewhat on the data types and other things. Also, the above assumes that you are adding LIKE criteria in text fields, you would have to modify these somewhat to give the exact criteria that you would be looking for. I think that I have seen some earlier posts with precoded query forms that would probably have some very good sample code. You may want to do some google searches of the Access newsgroup to find some if you do go that route. HTH, Ted Allen "rgrantz" wrote: Okay, I've got a pretty big problem here; I'm trying to get rid of about 45 extra minutes of crap labor for 4 people at once, as well as cutting down on duplicative data and reporting time. I'm almost there now, and the 4 people are ecstatic about what's here so far. However, there is ONE thing missing that I'm having a hard time doing: This manager I'm making this for has about 13 different criteria he likes to change and see new data for (customer name, who entered it, what date it was received, what date range it was entered, what date range it was processed, who processed it, what machine it was made on, who packaged it, who shipped it, etc. etc.). Currently he uses a combination of the Autofilter utility and the Dcount(Data!...etc.) formula in Excel, which returns data based on any criteria put in an entire row, which is nice. He can change any criteria along the entire row, adding some, changing some, deleting others, etc., and Excel just uses the whole row to continuously dynamically change the records returned as the criteria change (ie. he enters a particular name for OrderEnteredBy, and only sees those records, then he enters a name in OrderShippedBy, and sees only records with BOTH criteria, then he enters another one, etc. etc.., and when he deletes a criteria, the records reflect the additional records that no longer have criteria there, but still meet the OTHER criteria. You get what I'm saying here, right? It's pretty much being able to stack 13 criteria, with each criteria field also having an "ALL" (or technically, "NONE", for no criteria) choice in addition to actual criteria to apply. I need to do this on a form, and duplicate how the data changes after each criteria is altered. However, there are too many criteria fields, apparently, for Access to handle using the query builder. I can use the [forms]![FormName]![UnboundCriteriaFieldName] in the criteria cell and [forms]![FormName]![UnboundCriteriaFieldName] is null in the "Or" cell of the query builder, and this works great for about 3 fields. He has too many that he wants to see, though, and Access returns errors about the query being too complex, or the form starts returning unpredictable results. It seems very strange to me that Access can continually compound filters on a form using the "filter menu" for different fields, and that Excel can do all this with one simple formula, but that I can't get an Access form to do it. Someone, please help me, this thing I'm making is PERFECT and has solved 12 big problems, but if the manager can't get this ability to change these multiple critieria and see the records change as he does so (and be able to delete criteria as well as change them), then the whole thing's a no go. All I've been able to think of so far is to make 500 different queries and run a Select Case on the AfterUpdate of every unbound criteria control, which cycles through to see which fields are null and which have criteria and run the corresponding query to refresh the form with. This would be insane. Someone mentioned using a SQL string and code to change the query results, rather than using specific queries, but I either don't know how to do this or the SQL string would be too complex too (don't Access queries made using the query builder represent SQL strings anyway?) Someone, please help, I can't help thinking there MUST be a way to do this. I'm sorry to sound deperate, but the idea of canning this thing and having 4 people spend an extra 45 minutes a day on completely duplicative data, w/ more possibilities for error, less flexibility for reporting, etc., is driving me crazy. It's not helping the 4 people out either. And everyone's on board, even this manager, if I can just get this one little gimmicky thing going. Thanks for ANY help, I would really appreciate it. I can buy lunch for anyone who helps me come up w/ a solution. |
#6
|
|||
|
|||
You do have more flexibility if you want to use the Like operator. For
instance, for your query criteria you can have: Like [Forms]![YourFormName]![YourControlName] Then, in the field they could type an * for all, or *A for all starting with A, or *A* for all containing A, etc. If the * is entered for a field using Like, you wouldn't need the separate condition for Or IsNull() (which might trim the query enough to get rid of the error that you are encountering). You could also have a command button that checks all fields and enters an * if they were left blank before refreshing/opening the query so that the users would not have to enter them. In my applications, I haven't really had the need for this type of querying, as much as choosing specific categories or names from a list, which is why I have used the list boxes and dynamic sql more. You may want to try experimenting with the google searches on some of these key words, as there are many different approaches to query by form situations and you may find something else that will work even better for your situation. HTH, Ted Allen "rgrantz" wrote: Ted: Thanks very much for the posts, it is greatly appreciated. I'm curious about one thing: I made an ASP website using an Access DB backend, and made a custom search page using exactly what I'm talking about, but each drop-down list also had an "ALL" choice, which I believe passed the "%" wildcard to the search. Why can we not use a "*" or "%" value in the form, which passes a wildcard to the query as opposed to a Null? In the page (which, again, is using ASP and MS Access), 10 different criteria listboxes have the ALL choice, and the value passed to the query is %: option value="%"ALL/option The ASP in the query language (on the results page) is: SELECT * FROM SearchPage WHERE (Country LIKE '%::Country::%' AND Sale2 LIKE '%::Sale2::%' AND Whatever LIKE '%::Whatever::%' AND etc. etc.') ORDER BY ::SortOrder:: ASC, Name ASC" fp_sDefault="Country=&Genre2=&Allegiance=&Associat ion=&Association2=&SortOrd er=" fp_sNoRecords="NO ITEMS FOUND: TRY OTHER CRITERIA" Now, I know this is Frontpage extensions and ASP, but it IS a field passing the wildcard criteria to SQL-based language in ASP. Is there really no way to pass a "*" or some kind of "anything" criteria to a parameter in JUST Access? I mean, this would be a magic bullet. Thanks again. "Ted Allen" wrote in message ... pps, okay, one more thing. One of the things that I use most often that I didn't mention before is to populate list boxes with data (such as all project managers, or project categories), which allows users to select multiple options. I then build a string of the selected ID's and use syntax such as: "Myfield In (" & strList & ")" to limit the query to the selected ID's from the appropriate field. I'm not sure if this would be helpful in your case, but I find it very useful in that it allows the user to come up with more combinations. Post back if you are interested and I can post some vba syntax for building the string from the list box. -Ted Allen "Ted Allen" wrote: I don't know of an easy way to do what you are looking to do in Access. I think that the earlier advice to build the sql string in code may solve your problem. It should result in a much simpler sql statement because you won't need all of the OR conditions for null cases. Instead, the VBA code would look at each control value and ignore it if it is null, or append the appropriate SQL string if it is not. There are many ways that you could approach the VBA code, but I'll show a few typical lines (you'll need a reference to DAO): Dim db as DAO.Database Dim qdf as DAO.QueryDef Dim strSQL as String Dim strWhere as String Dim StrAnd as String StrWhere = "" StrAnd = "" If Nz(Me.Ctrl1,"") "" Then strWhere = " QueryField1 Like '*" & Me.Ctrl1 & "*'" strAnd = " AND" Endif If Nz(Me.Ctrl2,"") "" Then strWhere = strAND & " QueryField2 Like '*" & Me.Ctrl2 & "*'" strAnd = " AND" Endif 'Go through the rest of the ctrls StrSQL = "Select ... (Enter Your Base Select Statement)" StrSQL = strSQL & " WHERE" & strWhere Set db = CurrentDb Set qdf = db.QueryDefs("YourQueryName") qdf.sql = strSQL set qdf = nothing set db = nothing Note though, that you may not be able to change the sql of a query that is bound to an open subform, so you may have to temporarily change the data source of the subform to nothing, then change the query, then change the data source back. I'm not sure, I haven't tried that before. Also, there are obviously other ways that you could loop through the controls, and set the criteria, but it would depend somewhat on the data types and other things. Also, the above assumes that you are adding LIKE criteria in text fields, you would have to modify these somewhat to give the exact criteria that you would be looking for. I think that I have seen some earlier posts with precoded query forms that would probably have some very good sample code. You may want to do some google searches of the Access newsgroup to find some if you do go that route. HTH, Ted Allen "rgrantz" wrote: Okay, I've got a pretty big problem here; I'm trying to get rid of about 45 extra minutes of crap labor for 4 people at once, as well as cutting down on duplicative data and reporting time. I'm almost there now, and the 4 people are ecstatic about what's here so far. However, there is ONE thing missing that I'm having a hard time doing: This manager I'm making this for has about 13 different criteria he likes to change and see new data for (customer name, who entered it, what date it was received, what date range it was entered, what date range it was processed, who processed it, what machine it was made on, who packaged it, who shipped it, etc. etc.). Currently he uses a combination of the Autofilter utility and the Dcount(Data!...etc.) formula in Excel, which returns data based on any criteria put in an entire row, which is nice. He can change any criteria along the entire row, adding some, changing some, deleting others, etc., and Excel just uses the whole row to continuously dynamically change the records returned as the criteria change (ie. he enters a particular name for OrderEnteredBy, and only sees those records, then he enters a name in OrderShippedBy, and sees only records with BOTH criteria, then he enters another one, etc. etc.., and when he deletes a criteria, the records reflect the additional records that no longer have criteria there, but still meet the OTHER criteria. You get what I'm saying here, right? It's pretty much being able to stack 13 criteria, with each criteria field also having an "ALL" (or technically, "NONE", for no criteria) choice in addition to actual criteria to apply. I need to do this on a form, and duplicate how the data changes after each criteria is altered. However, there are too many criteria fields, apparently, for Access to handle using the query builder. I can use the [forms]![FormName]![UnboundCriteriaFieldName] in the criteria cell and [forms]![FormName]![UnboundCriteriaFieldName] is null in the "Or" cell of the query builder, and this works great for about 3 fields. He has too many that he wants to see, though, and Access returns errors about the query being too complex, or the form starts returning unpredictable results. It seems very strange to me that Access can continually compound filters on a form using the "filter menu" for different fields, and that Excel can do all this with one simple formula, but that I can't get an Access form to do it. Someone, please help me, this thing I'm making is PERFECT and has solved 12 big problems, but if the manager can't get this ability to change these multiple critieria and see the records change as he does so (and be able to delete criteria as well as change them), then the whole thing's a no go. All I've been able to think of so far is to make 500 different queries and run a Select Case on the AfterUpdate of every unbound criteria control, which cycles through to see which fields are null and which have criteria and run the corresponding query to refresh the form with. This would be insane. Someone mentioned using a SQL string and code to change the query results, rather than using specific queries, but I either don't know how to do this or the SQL string would be too complex too (don't Access queries made using the query builder represent SQL strings anyway?) Someone, please help, I can't help thinking there MUST be a way to do this. I'm sorry to sound deperate, but the idea of canning this thing and having 4 people spend an extra 45 minutes a day on completely duplicative data, w/ more possibilities for error, less flexibility for reporting, etc., is driving me crazy. It's not helping the 4 people out either. And everyone's on board, even this manager, if I can just get this one little gimmicky thing going. Thanks for ANY help, I would really appreciate it. I can buy lunch for anyone who helps me come up w/ a solution. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Duplicating Excel's Autofilter functionality | rgrantz | Running & Setting Up Queries | 0 | November 3rd, 2004 11:06 PM |
Need tips for solving "Query Too Complex" error | jclarkedude | General Discussion | 0 | November 2nd, 2004 07:49 PM |
Query Criteria from a SubForm | Sorlenny | Using Forms | 1 | November 1st, 2004 03:34 PM |
problem with criteria for query | Rawley | Running & Setting Up Queries | 4 | October 22nd, 2004 11:26 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |