Results 1 to 14 of 14
  1. #1
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20

    Setting query criteria

    I'm having trouble selecting values for "nested" queries. (Not sure if that's the correct term.)

    I have a combo box on form that I want to use to select a value to use as criteria to define the rows selected from a table. This is query1.

    Query1 data is then used in a crosstab query (query2) that performs some additional filtering.



    Ultimately, I want a button on the form that runs query2 and displays the output.

    What I'm finding is that the combo box value will work correctly to select the desired rows in query1. However, when query2 runs I get an error that the form combo box is not recognized as a valid field name or expression. This is weird because it's not used in query2 and it works fine when query1 is run on its own.

    Am I missing something?
    - Dave

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Am I missing something?
    Maybe. Nested shouldn't confuse anyone; stacked is another term used.
    You open query2 - not query1. 2 opens 1. As long as the form is open, 1 should get the value fine, same as it does on its own.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The syntax Forms!FormName.ComboName works fine in query filter criteria to get the bound field value.
    However, you cannot use that followed by e.g. .Column(1) in a query to get the value for any other field used in the combo.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Quote Originally Posted by Micron View Post
    Maybe. Nested shouldn't confuse anyone; stacked is another term used.
    You open query2 - not query1. 2 opens 1. As long as the form is open, 1 should get the value fine, same as it does on its own.
    Yes - you are correct. With the form open, running query 1 is fine. The error is triggered by running query 2, which opens query 1.

    Quote Originally Posted by isladogs View Post
    The syntax Forms!FormName.ComboName works fine in query filter criteria to get the bound field value.
    However, you cannot use that followed by e.g. .Column(1) in a query to get the value for any other field used in the combo.
    Unless I'm misunderstanding your point, I don't think that's what I'm doing. The only filter criteria in query 1 is Forms!FormName.ComboName. That's the only connection to the combo in either query. I'm using the output of query 1 as the data source for query 2. Query 2 has no filter criteria.

    Is there another way I should accomplish this?

    Incidentally, I can achieve what I need by creating separate versions of query 1 where I manually enter each of the possible the filter criteria, so I know both queries are working properly together. But this isn't a viable option for a combo box with a long list of options.
    - Dave

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    may not be relevant, but if using a crosstab with parameters (your form combo in this case) you need to define the parameters - see parameters option on the ribbon. It doesn't matter that your actual crosstab does not use parameters directly, it is via the source query, so set the parameters in your query1

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The error is triggered by running query 2, which opens query 1.
    I presumed you already had query1 open but if you don't, and you're saying that if you open query 2 and the form is open, and the field in question contains data, there is still an error?

    This is weird because it's not used in query2 and it works fine when query1 is run on its own.
    No, it's not weird. If you open the query1 and the form is open it works as you'd expect provided the form control reference is correct. If you open query2 and the form is open, 2 opens 1 and 1 looks for the form control, then 1 runs and passes a recordset to query2. I'm not certain if that's your issue, because if I was doing this I would not have already opened query1, thus I can't tell you if that's the issue.

    If you're saying you open query2 and the form is open and you get that error it makes no sense to me - unless perhaps as I'm saying, query1 is already open. IF you are in fact doing this by forms and one of those forms is a subform, then I'd expect that to be the problem. Ajax might be right, but I would not expect that to cause your particular error because it's a form control reference at play here, not a field in a ct query that may or may not be there.

    If what I just wrote doesn't help, I think you'll have to post a copy of your db with just enough data and objects that will allow us to find the problem.
    Last edited by Micron; 02-23-2020 at 02:00 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Quote Originally Posted by Micron View Post
    I presumed you already had query1 open but if you don't, and you're saying that if you open query 2 and the form is open, and the field in question contains data, there is still an error?

    No, it's not weird. If you open the query1 and the form is open it works as you'd expect provided the form control reference is correct. If you open query2 and the form is open, 2 opens 1 and 1 looks for the form control, then 1 runs and passes a recordset to query2. I'm not certain if that's your issue, because if I was doing this I would not have already opened query1, thus I can't tell you if that's the issue.

    If you're saying you open query2 and the form is open and you get that error it makes no sense to me - unless perhaps as I'm saying, query1 is already open. IF you are in fact doing this by forms and one of those forms is a subform, then I'd expect that to be the problem. Ajax might be right, but I would not expect that to cause your particular error because it's a form control reference at play here, not a field in a ct query that may or may not be there.

    If what I just wrote doesn't help, I think you'll have to post a copy of your db with just enough data and objects that will allow us to find the problem.
    I agree that query1 should not be open when query2 is called.

    I start with nothing open but the form with a valid value in the combo box set on the form. If I open only query2 I get an error. If I open only query1 it works. If I leave query 1 open and then open query2, I still get the same error.

    I'm going to try the suggestion from Ajax. If that doesn't work I'll post an example.
    - Dave

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by dth122 View Post
    If that doesn't work I'll post an example.
    Yes, he might be on to something there. If that doesn't do it, a db copy would be your best bet. I don't see where you addressed whether or not the combo is on a subform though. If it is, I'd say the issue is there.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Quote Originally Posted by Ajax View Post
    may not be relevant, but if using a crosstab with parameters (your form combo in this case) you need to define the parameters - see parameters option on the ribbon. It doesn't matter that your actual crosstab does not use parameters directly, it is via the source query, so set the parameters in your query1
    I tried using parameters on query1, but I still end up with the same error when running query2.

    Quote Originally Posted by Micron View Post
    Yes, he might be on to something there. If that doesn't do it, a db copy would be your best bet. I don't see where you addressed whether or not the combo is on a subform though. If it is, I'd say the issue is there.
    The combo box is not on a subform.

    Incidentally, I also tried entering the criteria from the combo box on the crosstab query (and sending the unfiltered data from query1 to query2), but I still get the same error. This solution would require me to create an extra query since query1 is used for other reporting besides feeding to query2.

    I'm out of ideas, so I've attached a sample that exhibits the error. Maybe it's something simple I've overlooking.
    - Dave
    Attached Files Attached Files

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In the sample you provided the Filter field was not defined as a parameter. Once I defined it, no problem. It seems you had the answer in post 5, so what happened after post 7 - or maybe didn't?

    Forgot to add that Filter is not a good name for any Access object. See http://allenbrowne.com/AppIssueBadWord.html
    Last edited by Micron; 02-23-2020 at 10:41 PM. Reason: added comment

  11. #11
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Quote Originally Posted by Micron View Post
    In the sample you provided the Filter field was not defined as a parameter. Once I defined it, no problem. It seems you had the answer in post 5, so what happened after post 7 - or maybe didn't?

    Forgot to add that Filter is not a good name for any Access object. See http://allenbrowne.com/AppIssueBadWord.html
    Understand about Filter... I just threw that example together. I didn't use any of the naming conventions that I normally would.

    I didn't have Filter defined as a parameter because I tried that and still got the same error. I'm not sure what happened. Maybe I defined it in the wrong query?
    - Dave

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    this is what your query1 sql should look like

    Code:
    PARAMETERS [Forms]![Form1]![Combo0] Text ( 255 );
    SELECT Table1.X, Table1.Y, Table1.Data
    FROM Table1
    WHERE (((Table1.Filter)=[Forms]![Form1]![Combo0]));

  13. #13
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Quote Originally Posted by Ajax View Post
    this is what your query1 sql should look like

    Code:
    PARAMETERS [Forms]![Form1]![Combo0] Text ( 255 );
    SELECT Table1.X, Table1.Y, Table1.Data
    FROM Table1
    WHERE (((Table1.Filter)=[Forms]![Form1]![Combo0]));
    That worked! Thanks for your help.

    One other quick question... currently the combo box has all of the possible value for the criteria field. Is there an easy way I can add option to let the query run against all of the records regardless of the value of the criteria field? I know I could add a value like "all" in the combo box, but is there a way I can get that to work as the query criteria?

    If not, I'll just duplicate the query without the any criteria, but it seems like that shouldn't be necessary.
    - Dave

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 11-25-2018, 11:31 PM
  2. Setting Criteria for one of 3 related fields
    By dmelsass in forum Access
    Replies: 9
    Last Post: 08-29-2017, 08:28 AM
  3. Setting Query Criteria using Iif Statement
    By thexxvi in forum Access
    Replies: 7
    Last Post: 12-14-2015, 01:27 PM
  4. Replies: 2
    Last Post: 11-03-2015, 08:56 AM
  5. Replies: 2
    Last Post: 10-19-2012, 01:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums