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
LVL 1
mbanuetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.