Queries that work randomly

[mmiram]

Hey guys:

This is a problem that I run into intermittently in Access. I run office XP.

I write a query that works fine when it is first run. Then when I try to run it again, it asks me to enter a parameter value for one of the fields when it actually is not a parameter query. I dont know why this keeps happening but it happens.

It usually happens in queries that I use aggregate functions. The only way it works is if I delete the query and re-write it. Then it works once or twice and then it stops working again.

Did anyone else have similar issues? What could be the problem?

Thanks.

 

[hneal98]

It sounds like the table you are basing the query on is changing somehow. Access usually will popup with a prompt when it does not recognize the field. I would go back and recheck all the fields to make sure they exist.

 

[mmiram]

I checked that..the fields are the same. They don't change. I type in the exact same query that I used before and it works fine when I first run it. Then it starts prompting me for a value.

 

[hneal98]

It is not the query that changes, I am thinking that your data source that the query is based on is what is changing. Again, Access only prompts either when it does not recognize the field or if you make the query a parameter query.

 

[mmiram]

Maybe I am misunderstanding what you are trying to say. I double checked the table (which is the data source). The fields are the same in the table. I am the only person who works on the database and I did not modify the structure of the table or the datatypes in any way. I understand that Access prompts for fields that it does not recognize. What is surprising to me is the first time it runs the query, it recognizes the fields and shows the values. However, the second time I run it, it prompts me for a value. There is something happening in between that is messing the query up. The surprising part is if I delete the query and type in the exact same query (verbatim), it works the first time. Then the same issue crops up again.

Is there any scnario where the data source (or fields in the table) is locked up and therefore are not available for the query when it is run? Or for some reason, the query engine loses its connection to the table/data source?

I guess I could try to reinstall Access and see if the issue still happens.

 

[hneal98]

Ok, I would not think that the query engine would loose connection. Another thing you might want to try if you haven't already, is repare (Fix) the database and see if that helps. Can you post the sql to the query here?

 

[mmiram]

Hi:

Here is the query:

SELECT DISTINCTAGENTS.*, AGENTCONTACTINFO.AEMAIL, AGENTCONTACTINFO.AHPHONE, AGENTCONTACTINFO.AOPHONE, AGENTCONTACTINFO.AVPHONE
FROM DISTINCTAGENTS LEFT JOIN AGENTCONTACTINFO ON (DISTINCTAGENTS.OFADD = AGENTCONTACTINFO.OFADD) AND (DISTINCTAGENTS.Expr1 = AGENTCONTACTINFO.Expr1);

Both DISTINCTAGENTS and AGENTCONTACTINFO are queries that run on the table DC. When I run the query, it asks for to enter the value for "Query1.sumofCLprice".

sumofCLprice is the aggregate function that derives values in the query DISTINCTAGENTS.

 

[hneal98]

Ok, so this query has a couple of data sources that are queries, correct? If that is the case, what is happening is if the query that is calculating the sum does not come up with a sum, there is no field there, and hense you get the error.

However, I am surpised by the Query1. How does that come into play?

 

[mmiram]

thats interesting. I will check it out. I just ran the query DISTINCTAGENTS separately and the sumofCLprice column does not have any NULL fields. So my guess is the query DISTINCTAGENTS is not running fast enough to get the results for all rows when the main query is running. Also, I don't know why Access refers to DISTINCTAGENTS as query1. Maybe it is the way it refers internally to these queries during run time.

 

[hneal98]

Do you have any queries called query1? Perhaps it is somehow linked to another query. Or if you have multiple instance of the query, it might refere to one as query1.