Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

MSAccess query (not a parameter query) keeps prompting for parameters

Often I build querys which involve fields calculated using expressions (sumOfField, CountOfField..) but when I run the query, a parameter dialog box prompts me a parameter value (which is none). I have to press the Accept button every time and as I nest querys the unrequested parameters become very annoying. How can I solve that? It is preventing me from doing things like link the Access DB to an Excell spreadsheet.

Magda
0
mbanuet
Asked:
mbanuet
  • 6
  • 6
  • 2
2 Solutions
 
pique_techCommented:
Can you post the SQL to your query here?  It will help to troubleshoot a lot faster.
0
 
mbanuetAuthor Commented:
Here is an example

SELECT       Purchase2004.Num_Prov,
      Count(Purchase2004.ID) AS CountOfID,
      Sum(Purchase2004.Gest_GCA) AS SumOfGest_GCA,
      Purchase2004.DontInclude,
      IIf([CountOfID]=-[SumOfGest_GCA],"Resp_GCA",
      IIf([SumOfGest_GCA]=0,"No_GCA","Parcial_GCA")) AS Gest_GCA
FROM       Purchase2004
GROUP BY Purchase2004.Num_Prov,
      Purchase2004.DontInclude,
      IIf([CountOfID]=-[SumOfGest_GCA],"Resp_GCA",IIf([SumOfGest_GCA]=0,"No_GCA","Parcial_GCA"))
HAVING (((Purchase2004.DontInclude)=No));

0
 
aflockhartCommented:
and what parameter(s) do you get prompted for ... ?

check that there is definitely a field with that name in the source table/query Purchase2004
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
pique_techCommented:
One note:  How can a Count of anything every be negative?  Is it possible that the values in Purchase2004.Gest_GCA might be negative, so that their sum is negative and its negation is postive?  I ask because if it's not possible for the Sum to be negative, then some of your conditions will never be met and the behavior might be different than you expect.

Anyway, is the prompt for a parameter ever specific, i.e., Enter Parameter Value is the window's title, but above the empty white box where'd you type the value, is there a label?
0
 
mbanuetAuthor Commented:
I get prompted twice for CountOfID and SumOfGest_GCA.

About the negative values, I guess I get a negative value because Gest_GCA is boolean.
Here is an Example result:

Num_Prov    CountOfID   SumOfGest_GCA      DontInclude      Gest_GCA
96016852            1            -1      No            Resp_GCA
96029014            4            -4      No            Resp_GCA
96029089            1            -1      No            Resp_GCA
96016725            6            -6      No            Resp_GCA
96002060          116            -108      No            Parcial_GCA
96012130           55            -11      No            Parcial_GCA
96001687            4            -3      No            Parcial_GCA
96012914           90             0      No            No_GCA
0
 
pique_techCommented:
That makes sense about the boolean value.  I didn't think of that.

I'm taking a shot in the dark here:  what happens if you remove the square brackets from around your field aliases?  They're not required as all your aliases are Access-compliant.  I'm thinking maybe that confuses Access a little?
0
 
aflockhartCommented:
try removing the alias names from your Group By clause, use the original expressions.
0
 
mbanuetAuthor Commented:
pique_tech: I already tried removing the square brackets but two things happened: When I opened the query again in design mode, there were all the brackets again. When I was able to remove all the brackets, the functions didn't work at all (I got only 0)

aflockhart: I tried your suggestion, removed the aliases but Access keeps using them, so I tried to build the expression using directly sum(table.field) instead of the alias but I got an error that said I can't use agregate functions on the expression.
0
 
pique_techCommented:
I see what aflockhart is suggesting:  have you tried this?

SELECT      Purchase2004.Num_Prov,
     Count(Purchase2004.ID) AS CountOfID,
     Sum(Purchase2004.Gest_GCA) AS SumOfGest_GCA,
     Purchase2004.DontInclude,
     IIf(Count(Purchase2004.ID)=-Sum(Purchase2004.Gest_GCA),"Resp_GCA",
     IIf(Sum(Purchase2004.Gest_GCA)=0,"No_GCA","Parcial_GCA")) AS Gest_GCA
FROM      Purchase2004
GROUP BY Purchase2004.Num_Prov,
     Purchase2004.DontInclude,
     IIf(Count(Purchase2004.ID)=-Sum(Purchase2004.Gest_GCA),"Resp_GCA",IIf(Sum(Purchase2004.Gest_GCA)=0,"No_GCA","Parcial_GCA"))
HAVING (((Purchase2004.DontInclude)=No));

The only other solution I can see as being likely not to cause problems requires multiple queries.  I don't like that idea/suggestion too much though, so I hope maybe this will work.
0
 
mbanuetAuthor Commented:
Well, I tried that but got the error saying that there cannot be agregate functions on group by clause, so it ocurred to me that the expresion: "IIf(Count(Purchase2004.ID)=-Sum(Purchase2004.Gest_GCA),"Resp_GCA",IIf(Sum(Purchase2004.Gest_GCA)=0,"No_GCA","Parcial_GCA"))" shouldn't be on the group by clause at all. I removed it and it worked :D. Here's how it worked:

SELECT Purchase2004.Num_Prov, Count(Purchase2004.ID) AS CuentaDeID, Sum(Purchase2004.Gest_GCA) AS SumaDeGest_GCA, Purchase2004.DontInclude, IIf(Count([Purchase2004]![ID])=-Sum([Purchase2004]![Gest_GCA]),"Resp_GCA",IIf(Sum([Purchase2004]![Gest_GCA])=0,"No_GCA","Parcial_GCA")) AS Gest_GCA
FROM Purchase2004
GROUP BY Purchase2004.Num_Prov, Purchase2004.DontInclude
HAVING (((Purchase2004.DontInclude)=No));

0
 
pique_techCommented:
That makes sense....I thought the solution might be in how that expression was handled, just hadn't been able to think it through properly.

Since you solved your own problem, I'd encourage you to request that the question be closed and points be refunded.  I wouldn't delete the question though, it's a good learning question.
0
 
pique_techCommented:
Um...I typed above while you were accepting.  I don't want to seem ungrateful--your accept and my suggestion "crossed paths".

Thanks though.  And glad you got it worked out.
0
 
mbanuetAuthor Commented:
I didn't asked for refund because even that I solved it, you guys helped me see things I didn't saw before and it will help me a lot in the future since this kind of prompts had happened to me before.
0
 
mbanuetAuthor Commented:
haha :D

Thanks a lot! see ya on another question! :D
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now