Parameter query and aggregates

[Dreamspinner]

I have two questions about a query I'm having trouble with. The DB is Access2000.

Select Count([UCall]) as Zcnt from tblScore where (Zonex Is Not Null and UCall=[in_call]);

When I run this in Access, I get the prompt for in_call twice. The first time it does nothing (AFAICS), the second time it returns Zcnt.

I'd also like to put an order by Zonex in the query.

TIA DS

 

[dhookom]

Is that actually your SQL? It shouldn't prompt for in_call more than once. IMO, you should never use parameter prompts in queries. Consider using controls on forms.

If you want the query to be ordered, add an ORDER BY Zonex after the WHERE clause.

Duane
Hook'D on Access
MS Access MVP

 

[Dreamspinner]

Yep, it shouldn't, but it does. This is a saved query, and much safer than a control on a form, plus it's part of an ASP page that reports some totals, so nothing visual til the totals are calculated.

"Changing the query to Select Count([UCall]) as Zcnt from tblScore where (Zonex Is Not Null and UCall=[in_call]) order by Zonex;" gives the error "You tried to execute a query that does not include the specified expression 'Zonex' as pert of an aggregate function".

 

[dhookom]

That query should return a single record. Why do you need to sort one record?

Duane
Hook'D on Access
MS Access MVP

 

[Dreamspinner]

Excellent question. The answer is that it doesn't. Back to the double parm entry issue.

 

[dhookom]

You are using a parameter query in an ASP page? I never would have thought it could be done. Do you mind sharing some code?

Duane
Hook'D on Access
MS Access MVP

 

[Dreamspinner]

Happy to. I got the technique in the old ASP or Access newsgroup. I miss them.

Create your query in Access and save it. I named mine ZoneSummary.

set conn = server.createobject("adodb.connection")
set RS = server.createobject("adodb.recordset")
conn.OPen "provider = microsoft.jet.oledb.4.0;" & "data source=" & path_to_your_db

To execute the query:
Conn.ZoneSummary('Parm_to_pass_in'), RS

 

[dhookom]

Is there a reason you don't simply create a sql statement to open a recordset?

Duane
Hook'D on Access
MS Access MVP

 

[Dreamspinner]

Well, actually I do create an SQL statement. I just execute it slightly differently some of the time. It's the way I was taught, so it's comfortable for me.

Still looking for a solution to the parameter being prompted for twice

 

[dhookom]

I would try create a test page with a very simple query with a prompt and try it. If it behaves differently then there might be something on the first page. If they behave the same then I expect its just the way your code works on your web page.

Duane
Hook'D on Access
MS Access MVP

 

[Dreamspinner]

It's in Access, not on a page yet. I still dunno why it happened, but I fixed it. I was opening Access from ExpressionWeb, which created a temporary copy of the DB. Once I opened the DB from Access itself, all was well.

 

[dhookom]

Dreamspinner,
You are totally confusing my old brain when you state "plus it's part of an ASP page" but apparently it's not. Please try to provide all relevant information in your initial postings.

Duane
Hook'D on Access
MS Access MVP

 

[Dreamspinner]

What part confused you? I was developing a query in Access (my usual practice) before deploying a classic ASP web page that used the query.

http://www.tek-tips.com/viewthread.cfm?qid=1703078

 

[dhookom]

I thought you were prompted twice when running the query in Access. Then I thought you were prompted twice when opening an ASP web page. Then I thought you were prompted twice when opening the query in Access.

Duane
Hook'D on Access
MS Access MVP