none
Why am I being prompted to enter parameter value in Access 2010 sub-query? RRS feed

  • Question

  • Hello there,

    Here is my sub-query from Access 2010. I'm prompting user to enter year(yyyy) and Month (1-12) but for some reason user is also getting prompted for Site. Can someone tell me why? I do not want user prompted for Site.

    Joe

    PARAMETERS [Enter Year (yyyy)] Text ( 255 ), [Enter Month (1-12)] Short;
        SELECT Sum([AB Units]) AS [Monthly AB Units], 
          (SELECT Count(*) FROM [New Referrals] 
           WHERE ((Year([New Referrals].[Date of Referral]) = [Enter Year (yyyy)])
                  AND (Month([New Referrals].[Date of Referral]) = [Enter Month (1-12)]))
                  AND ([New Referrals].[First Visit] Is Null)) 
                  AND (([New Referrals].Site)="2")
           AS Waitlist FROM [DNS] 
        WHERE (((Year([DNS].[Date of Entry]))=[Enter Year (yyyy)])
               AND ((Month([DNS].[Date of Entry]))=[Enter Month (1-12)])
               AND (([DNS].Site)="2"));


    Tuesday, September 6, 2016 3:17 PM

Answers

  • Hi Green2004,

    I try to make a Tables from your above mentioned query and try to run the query then I got same result like you.

    so I break down the query in several parts.

    if we talk about sub query then there are some extra brackets. so to correct it I remove the extra brackets and run it and get correct result.

    below is the modified sub query.

    PARAMETERS [Enter Year (yyyy)] Text ( 255 ), [Enter Month (1-12)] Short;
    SELECT Count(*) FROM [New Referrals] 
           WHERE ((Year([New Referrals].[Date of Referral]) = [Enter Year (yyyy)])
                  AND (Month([New Referrals].[Date of Referral]) = [Enter Month (1-12)])             
                  AND ([New Referrals].[First Visit] Is Null) 
                  AND ([New Referrals].Site)="2");
           

    then I try to run the main query and find that it is asking for the [AB Units].

    if we see in the query then we can find that you are trying to sum the [AB Units] from [DNS] Table.

    here I don't know that you have [Ab Units] field in DNS Table or not. because I just create the tables based on your above query so did not include that column in DNS Table.

    but [AB Units] field is available in the New Referrals Table. so that's why it is again asking to enter it.

    so if [AB Units] is not available in DNS then correct it. and Run it. it will not ask you for Site.

    Below is the Separated main query.

    PARAMETERS [Enter Year (yyyy)] Text ( 255 ), [Enter Month (1-12)] Short;
        SELECT Sum([AB Units]) AS [Monthly AB Units]  FROM [DNS] 
              WHERE (((Year([DNS].[Date of Entry]))=[Enter Year (yyyy)])
               AND ((Month([DNS].[Date of Entry]))=[Enter Month (1-12)])
               AND (([DNS].Site)="2"));
      

    Below is the whole query.

    PARAMETERS [Enter Year (yyyy)] Text ( 255 ), [Enter Month (1-12)] Short;
        SELECT Sum([AB Units]) AS [Monthly AB Units], 
          (
    SELECT Count(*) FROM [New Referrals] 
           WHERE ((Year([New Referrals].[Date of Referral]) = [Enter Year (yyyy)])
                  AND (Month([New Referrals].[Date of Referral]) = [Enter Month (1-12)])             
                  AND ([New Referrals].[First Visit] Is Null) 
                  AND ([New Referrals].Site)="2")
    )
           AS Waitlist FROM [DNS]
        WHERE (((Year([DNS].[Date of Entry]))=[Enter Year (yyyy)])
               AND ((Month([DNS].[Date of Entry]))=[Enter Month (1-12)])
               AND (([DNS].Site)="2"));

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 7, 2016 1:15 AM
    Moderator

All replies

  • Maybe because Site is not a field in the DNS table?

    -Tom. Microsoft Access MVP

    Tuesday, September 6, 2016 3:54 PM
  • Are you sure that Site is a field in both the DNS and New Referrals tables?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, September 6, 2016 3:55 PM
  • Hi Green2004,

    I try to make a Tables from your above mentioned query and try to run the query then I got same result like you.

    so I break down the query in several parts.

    if we talk about sub query then there are some extra brackets. so to correct it I remove the extra brackets and run it and get correct result.

    below is the modified sub query.

    PARAMETERS [Enter Year (yyyy)] Text ( 255 ), [Enter Month (1-12)] Short;
    SELECT Count(*) FROM [New Referrals] 
           WHERE ((Year([New Referrals].[Date of Referral]) = [Enter Year (yyyy)])
                  AND (Month([New Referrals].[Date of Referral]) = [Enter Month (1-12)])             
                  AND ([New Referrals].[First Visit] Is Null) 
                  AND ([New Referrals].Site)="2");
           

    then I try to run the main query and find that it is asking for the [AB Units].

    if we see in the query then we can find that you are trying to sum the [AB Units] from [DNS] Table.

    here I don't know that you have [Ab Units] field in DNS Table or not. because I just create the tables based on your above query so did not include that column in DNS Table.

    but [AB Units] field is available in the New Referrals Table. so that's why it is again asking to enter it.

    so if [AB Units] is not available in DNS then correct it. and Run it. it will not ask you for Site.

    Below is the Separated main query.

    PARAMETERS [Enter Year (yyyy)] Text ( 255 ), [Enter Month (1-12)] Short;
        SELECT Sum([AB Units]) AS [Monthly AB Units]  FROM [DNS] 
              WHERE (((Year([DNS].[Date of Entry]))=[Enter Year (yyyy)])
               AND ((Month([DNS].[Date of Entry]))=[Enter Month (1-12)])
               AND (([DNS].Site)="2"));
      

    Below is the whole query.

    PARAMETERS [Enter Year (yyyy)] Text ( 255 ), [Enter Month (1-12)] Short;
        SELECT Sum([AB Units]) AS [Monthly AB Units], 
          (
    SELECT Count(*) FROM [New Referrals] 
           WHERE ((Year([New Referrals].[Date of Referral]) = [Enter Year (yyyy)])
                  AND (Month([New Referrals].[Date of Referral]) = [Enter Month (1-12)])             
                  AND ([New Referrals].[First Visit] Is Null) 
                  AND ([New Referrals].Site)="2")
    )
           AS Waitlist FROM [DNS]
        WHERE (((Year([DNS].[Date of Entry]))=[Enter Year (yyyy)])
               AND ((Month([DNS].[Date of Entry]))=[Enter Month (1-12)])
               AND (([DNS].Site)="2"));

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 7, 2016 1:15 AM
    Moderator