This HAS to be a bug...Why does Access remember...

[NoMagic@Certegy]

Why does Access remember column headers from a query result, then ask me to
provide a value for that column before it will run the query?

Here is an example that I can reproduce at will....

This query: SELECT smf30.Job, Sum(smf30.CPU) produces a table with 2
columns, one named Job and the other column header is Access generated,
Expr1001.

change the query to add an 'AS' clause: SELECT smf30.Job, Sum(smf30.CPU) AS
[Net CPU] and run the query. Access throws up a text box and says Enter
Parameter Value queryx.Ecpr1001

Unbelievable.

What could one possibly enter as a meaningful value for the RESULTS table
BEFORE the query even runs?

The only way to get Access to 'forget' that original column (with the "AS"
clause) is to reboot the machine, closing Access and bringing it back up will
not solve it.

Anybody have ANY idea what the heck is going on here?

Thanks.

 

[Dirk Goldgar]

Why does Access remember column headers from a query result, then ask
me to provide a value for that column before it will run the query?

Here is an example that I can reproduce at will....

This query: SELECT smf30.Job, Sum(smf30.CPU) produces a table with 2
columns, one named Job and the other column header is Access
generated, Expr1001.

That's can't be the actual query; it's not a valid query, since there's
no GROUP BY clause. Please post the SQL of the actual query you're
starting with. Are you building, modifying, and testing these queries
in Query Design View?

change the query to add an 'AS' clause: SELECT smf30.Job,
Sum(smf30.CPU) AS [Net CPU] and run the query. Access throws up a
text box and says Enter Parameter Value queryx.Ecpr1001

How are you running this query? Is it the recordsource of a form, the
rowsource of a combo box, just opened as a datasheet from the Queries
tab of the database window, or what?

Unbelievable.

What could one possibly enter as a meaningful value for the RESULTS
table BEFORE the query even runs?

The only way to get Access to 'forget' that original column (with the
"AS" clause) is to reboot the machine, closing Access and bringing it
back up will not solve it.

Anybody have ANY idea what the heck is going on here?

I need more info about what you have actually done. Can you give me
simple steps to reproduce the error?

 

[NoMagic@Certegy]

That's can't be the actual query; it's not a valid query, since there's
no GROUP BY clause. Please post the SQL of the actual query you're
starting with. Are you building, modifying, and testing these queries
in Query Design View?

==That was the operative part of the query for the question, here is the
whole query, not sure why it matters:
SELECT job, sum(cpu) AS [Net CPU]
FROM smf30
WHERE type in (2,3)
GROUP BY job
HAVING sum(cpu) > 500
ORDER BY job DESC;

change the query to add an 'AS' clause: SELECT smf30.Job,
Sum(smf30.CPU) AS [Net CPU] and run the query. Access throws up a
text box and says Enter Parameter Value queryx.Ecpr1001

How are you running this query? Is it the recordsource of a form, the
rowsource of a combo box, just opened as a datasheet from the Queries
tab of the database window, or what?

Run from the SQL editing window with the exclamation point (!)

I need more info about what you have actually done. Can you give me
simple steps to reproduce the error?

Yep.

Type the above query, (or like version based on your data) without the "AS"
clause.
Get the results table with Expr1001 as the header for the SUM funciton
results.

Edit the query and add the "AS [Net CPU]" clause. Hit the exclamation
point, and Access asks for the value of yourqueryname.Expr001

Thanks for your help

 

[NoMagic@Certegy]

BTW, this problem exists with other facets as well.

If I were to click on one of the result table columns and sort it. Access
remembers the sort order for some reason. If I change a rerun the query, it
comes up sorted in the order that I requested earlier with a column click.
There is NO order by clause in the SQL in this example. This MIGHT be a
feature, but it is incredibly misguided. if I really wanted the query
permanantly sorted in the order I chose for the results table, I would have
put it in the original SQL. Maybe Access' maniac insistance in remembering
the sort order has something to do with these other occurrences.

That's can't be the actual query; it's not a valid query, since there's
no GROUP BY clause. Please post the SQL of the actual query you're
starting with. Are you building, modifying, and testing these queries
in Query Design View?

==That was the operative part of the query for the question, here is the
whole query, not sure why it matters:
SELECT job, sum(cpu) AS [Net CPU]
FROM smf30
WHERE type in (2,3)
GROUP BY job
HAVING sum(cpu) > 500
ORDER BY job DESC;

change the query to add an 'AS' clause: SELECT smf30.Job,
Sum(smf30.CPU) AS [Net CPU] and run the query. Access throws up a
text box and says Enter Parameter Value queryx.Ecpr1001

How are you running this query? Is it the recordsource of a form, the
rowsource of a combo box, just opened as a datasheet from the Queries
tab of the database window, or what?

Run from the SQL editing window with the exclamation point (!)

I need more info about what you have actually done. Can you give me
simple steps to reproduce the error?

Yep.

Type the above query, (or like version based on your data) without the "AS"
clause.
Get the results table with Expr1001 as the header for the SUM funciton
results.

Edit the query and add the "AS [Net CPU]" clause. Hit the exclamation
point, and Access asks for the value of yourqueryname.Expr001

Thanks for your help

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

That's can't be the actual query; it's not a valid query, since
there's no GROUP BY clause. Please post the SQL of the actual query
you're starting with. Are you building, modifying, and testing
these queries in Query Design View?

==That was the operative part of the query for the question, here is
the whole query, not sure why it matters:
SELECT job, sum(cpu) AS [Net CPU]
FROM smf30
WHERE type in (2,3)
GROUP BY job
HAVING sum(cpu) > 500
ORDER BY job DESC;

change the query to add an 'AS' clause: SELECT smf30.Job,
Sum(smf30.CPU) AS [Net CPU] and run the query. Access throws up a
text box and says Enter Parameter Value queryx.Ecpr1001

How are you running this query? Is it the recordsource of a form,
the rowsource of a combo box, just opened as a datasheet from the
Queries tab of the database window, or what?

Run from the SQL editing window with the exclamation point (!)

I need more info about what you have actually done. Can you give me
simple steps to reproduce the error?

Yep.

Type the above query, (or like version based on your data) without
the "AS" clause.
Get the results table with Expr1001 as the header for the SUM funciton
results.

Edit the query and add the "AS [Net CPU]" clause. Hit the exclamation
point, and Access asks for the value of yourqueryname.Expr001

Thanks for your help

Hmm, it didn't do that for me, using Access 2002. Here's what I did:

1. Create new query in design view.

2. Switch to SQL view.

3. Enter:

SELECT tblData.BaseName, Sum(tblData.intNumber)
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

4. Run it. Column header is the generated name "Expr1001".

5. Switch back to SQL view.

6. Edit SQL, making it like this:

SELECT tblData.BaseName, Sum(tblData.intNumber) AS MySum
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

7. Run it. No parameter prompt appears. Column header is "MySum".

Did you leave out something in your steps to reproduce?

 

['69 Camaro]

Hi.

Did you leave out something in your steps to reproduce?

<vbg> I know what he did!

After running the query, he sorted on the column "Expr1001," then went back
to Design View, changed the SQL to use an alias, then ran the query again.
Since the column name no longer exists, Jet asked for it because the
QueryDef's OrderBy Property was set on this column.

To fix it, open the query in Design View, right-click on the query diagram
and select "Properties" from the pop-up menu. Delete Expr1001 from the
Order By Property, then close the Properties dialog window and save the
query.

This behavior is by design, because users can set the sort order by SQL
statement, by the "Sort" field in the query grid, or merely selecting the
column, then pressing the "Sort Ascending" (or Descending) button on the
toolbar.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as
"Answers," so that all may benefit by filtering on "Answered questions" and
quickly finding the right answers to similar questions. Remember that the
first and best answers are often given to those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

message Why does Access remember column headers from a query result, then
ask me to provide a value for that column before it will run the
query?

Here is an example that I can reproduce at will....

This query: SELECT smf30.Job, Sum(smf30.CPU) produces a table with 2
columns, one named Job and the other column header is Access
generated, Expr1001.

That's can't be the actual query; it's not a valid query, since
there's no GROUP BY clause. Please post the SQL of the actual query
you're starting with. Are you building, modifying, and testing
these queries in Query Design View?

==That was the operative part of the query for the question, here is
the whole query, not sure why it matters:
SELECT job, sum(cpu) AS [Net CPU]
FROM smf30
WHERE type in (2,3)
GROUP BY job
HAVING sum(cpu) > 500
ORDER BY job DESC;

change the query to add an 'AS' clause: SELECT smf30.Job,
Sum(smf30.CPU) AS [Net CPU] and run the query. Access throws up a
text box and says Enter Parameter Value queryx.Ecpr1001

How are you running this query? Is it the recordsource of a form,
the rowsource of a combo box, just opened as a datasheet from the
Queries tab of the database window, or what?

Run from the SQL editing window with the exclamation point (!)

Unbelievable.

What could one possibly enter as a meaningful value for the RESULTS
table BEFORE the query even runs?

The only way to get Access to 'forget' that original column (with
the "AS" clause) is to reboot the machine, closing Access and
bringing it back up will not solve it.

Anybody have ANY idea what the heck is going on here?

I need more info about what you have actually done. Can you give me
simple steps to reproduce the error?

Yep.

Type the above query, (or like version based on your data) without
the "AS" clause.
Get the results table with Expr1001 as the header for the SUM funciton
results.

Edit the query and add the "AS [Net CPU]" clause. Hit the exclamation
point, and Access asks for the value of yourqueryname.Expr001

Thanks for your help

Hmm, it didn't do that for me, using Access 2002. Here's what I did:

1. Create new query in design view.

2. Switch to SQL view.

3. Enter:

SELECT tblData.BaseName, Sum(tblData.intNumber)
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

4. Run it. Column header is the generated name "Expr1001".

5. Switch back to SQL view.

6. Edit SQL, making it like this:

SELECT tblData.BaseName, Sum(tblData.intNumber) AS MySum
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

7. Run it. No parameter prompt appears. Column header is "MySum".

Did you leave out something in your steps to reproduce?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[NoMagic@Certegy]

sure didn't leave anything out..that is why it is so annoying...there just
isn't anything complex going on here. I've used Access for years and had the
problem occassionaly with '97 on a Dell laptop, now I'm using an IBM R51 with
2000 9.0.3821 SR-1 and it is maddening.

I'll figure it out eventually...hard to believe we actually pay money for
this stuff.

Thanks for your help.

message Why does Access remember column headers from a query result, then
ask me to provide a value for that column before it will run the
query?

Here is an example that I can reproduce at will....

This query: SELECT smf30.Job, Sum(smf30.CPU) produces a table with 2
columns, one named Job and the other column header is Access
generated, Expr1001.

That's can't be the actual query; it's not a valid query, since
there's no GROUP BY clause. Please post the SQL of the actual query
you're starting with. Are you building, modifying, and testing
these queries in Query Design View?

==That was the operative part of the query for the question, here is
the whole query, not sure why it matters:
SELECT job, sum(cpu) AS [Net CPU]
FROM smf30
WHERE type in (2,3)
GROUP BY job
HAVING sum(cpu) > 500
ORDER BY job DESC;

change the query to add an 'AS' clause: SELECT smf30.Job,
Sum(smf30.CPU) AS [Net CPU] and run the query. Access throws up a
text box and says Enter Parameter Value queryx.Ecpr1001

How are you running this query? Is it the recordsource of a form,
the rowsource of a combo box, just opened as a datasheet from the
Queries tab of the database window, or what?

Run from the SQL editing window with the exclamation point (!)

Unbelievable.

What could one possibly enter as a meaningful value for the RESULTS
table BEFORE the query even runs?

The only way to get Access to 'forget' that original column (with
the "AS" clause) is to reboot the machine, closing Access and
bringing it back up will not solve it.

Anybody have ANY idea what the heck is going on here?

I need more info about what you have actually done. Can you give me
simple steps to reproduce the error?

Yep.

Type the above query, (or like version based on your data) without
the "AS" clause.
Get the results table with Expr1001 as the header for the SUM funciton
results.

Edit the query and add the "AS [Net CPU]" clause. Hit the exclamation
point, and Access asks for the value of yourqueryname.Expr001

Thanks for your help

Hmm, it didn't do that for me, using Access 2002. Here's what I did:

1. Create new query in design view.

2. Switch to SQL view.

3. Enter:

SELECT tblData.BaseName, Sum(tblData.intNumber)
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

4. Run it. Column header is the generated name "Expr1001".

5. Switch back to SQL view.

6. Edit SQL, making it like this:

SELECT tblData.BaseName, Sum(tblData.intNumber) AS MySum
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

7. Run it. No parameter prompt appears. Column header is "MySum".

Did you leave out something in your steps to reproduce?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[NoMagic@Certegy]

My first example, and the basis for the post, doesn't include any sort
interaction. It is simply changing the underlying query that chokes Access.

I understand the point you are making and thought it might be a place to
start to look for the answer. note: hard to believe that would be by
design...if I wanted the sort to stick I would have put it in the SQL. I
left it OUT of the SQL specifically, yet Access wants to put it in just
because I sorted the results table...amazing.

Hi.

Did you leave out something in your steps to reproduce?

<vbg> I know what he did!

After running the query, he sorted on the column "Expr1001," then went back
to Design View, changed the SQL to use an alias, then ran the query again.
Since the column name no longer exists, Jet asked for it because the
QueryDef's OrderBy Property was set on this column.

To fix it, open the query in Design View, right-click on the query diagram
and select "Properties" from the pop-up menu. Delete Expr1001 from the
Order By Property, then close the Properties dialog window and save the
query.

This behavior is by design, because users can set the sort order by SQL
statement, by the "Sort" field in the query grid, or merely selecting the
column, then pressing the "Sort Ascending" (or Descending) button on the
toolbar.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as
"Answers," so that all may benefit by filtering on "Answered questions" and
quickly finding the right answers to similar questions. Remember that the
first and best answers are often given to those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

:

message Why does Access remember column headers from a query result, then
ask me to provide a value for that column before it will run the
query?

Here is an example that I can reproduce at will....

This query: SELECT smf30.Job, Sum(smf30.CPU) produces a table with 2
columns, one named Job and the other column header is Access
generated, Expr1001.

That's can't be the actual query; it's not a valid query, since
there's no GROUP BY clause. Please post the SQL of the actual query
you're starting with. Are you building, modifying, and testing
these queries in Query Design View?

==That was the operative part of the query for the question, here is
the whole query, not sure why it matters:
SELECT job, sum(cpu) AS [Net CPU]
FROM smf30
WHERE type in (2,3)
GROUP BY job
HAVING sum(cpu) > 500
ORDER BY job DESC;


change the query to add an 'AS' clause: SELECT smf30.Job,
Sum(smf30.CPU) AS [Net CPU] and run the query. Access throws up a
text box and says Enter Parameter Value queryx.Ecpr1001

How are you running this query? Is it the recordsource of a form,
the rowsource of a combo box, just opened as a datasheet from the
Queries tab of the database window, or what?

Run from the SQL editing window with the exclamation point (!)

Unbelievable.

What could one possibly enter as a meaningful value for the RESULTS
table BEFORE the query even runs?

The only way to get Access to 'forget' that original column (with
the "AS" clause) is to reboot the machine, closing Access and
bringing it back up will not solve it.

Anybody have ANY idea what the heck is going on here?

I need more info about what you have actually done. Can you give me
simple steps to reproduce the error?

Yep.

Type the above query, (or like version based on your data) without
the "AS" clause.
Get the results table with Expr1001 as the header for the SUM funciton
results.

Edit the query and add the "AS [Net CPU]" clause. Hit the exclamation
point, and Access asks for the value of yourqueryname.Expr001

Thanks for your help

Hmm, it didn't do that for me, using Access 2002. Here's what I did:

1. Create new query in design view.

2. Switch to SQL view.

3. Enter:

SELECT tblData.BaseName, Sum(tblData.intNumber)
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

4. Run it. Column header is the generated name "Expr1001".

5. Switch back to SQL view.

6. Edit SQL, making it like this:

SELECT tblData.BaseName, Sum(tblData.intNumber) AS MySum
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

7. Run it. No parameter prompt appears. Column header is "MySum".

Did you leave out something in your steps to reproduce?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

['69 Camaro]

Hi.

My first example, and the basis for the post, doesn't include any sort
interaction. It is simply changing the underlying query that chokes
Access.

A QueryDef's Property such as Order By or Filter has been changed to include
Expr1001, even if inadvertently. Next time this happens to you, change to
Design View and check the QueryDef Properties. I'll bet you'll be amazed at
what you discover.

note: hard to believe that would be by
design...if I wanted the sort to stick I would have put it in the SQL. I
left it OUT of the SQL specifically, yet Access wants to put it in just
because I sorted the results table...amazing.

Just another handy-dandy, helpful feature! One size fits all -- whether we
want it to or not. :-{

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

My first example, and the basis for the post, doesn't include any sort
interaction. It is simply changing the underlying query that chokes
Access.

I understand the point you are making and thought it might be a place to
start to look for the answer. note: hard to believe that would be by
design...if I wanted the sort to stick I would have put it in the SQL. I
left it OUT of the SQL specifically, yet Access wants to put it in just
because I sorted the results table...amazing.

Hi.

Did you leave out something in your steps to reproduce?

<vbg> I know what he did!

After running the query, he sorted on the column "Expr1001," then went
back
to SQL View, changed the SQL to use an alias, then ran the query again.
Since the column name no longer exists, Jet asked for it because the
QueryDef's OrderBy Property was set on this column.

To fix it, open the query in Design View, right-click on the query
diagram
and select "Properties" from the pop-up menu. Delete Expr1001 from the
Order By Property, then close the Properties dialog window and save the
query.

This behavior is by design, because users can set the sort order by SQL
statement, by the "Sort" field in the query grid, or merely selecting the
column, then pressing the "Sort Ascending" (or Descending) button on the
toolbar.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail
harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's
Online
Community, please sign in to the Community and mark these posts as
"Answers," so that all may benefit by filtering on "Answered questions"
and
quickly finding the right answers to similar questions. Remember that
the
first and best answers are often given to those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

message "Dirk Goldgar" wrote:

message Why does Access remember column headers from a query result, then
ask me to provide a value for that column before it will run the
query?

Here is an example that I can reproduce at will....

This query: SELECT smf30.Job, Sum(smf30.CPU) produces a table with 2
columns, one named Job and the other column header is Access
generated, Expr1001.

That's can't be the actual query; it's not a valid query, since
there's no GROUP BY clause. Please post the SQL of the actual query
you're starting with. Are you building, modifying, and testing
these queries in Query Design View?

==That was the operative part of the query for the question, here is
the whole query, not sure why it matters:
SELECT job, sum(cpu) AS [Net CPU]
FROM smf30
WHERE type in (2,3)
GROUP BY job
HAVING sum(cpu) > 500
ORDER BY job DESC;


change the query to add an 'AS' clause: SELECT smf30.Job,
Sum(smf30.CPU) AS [Net CPU] and run the query. Access throws up a
text box and says Enter Parameter Value queryx.Ecpr1001

How are you running this query? Is it the recordsource of a form,
the rowsource of a combo box, just opened as a datasheet from the
Queries tab of the database window, or what?

Run from the SQL editing window with the exclamation point (!)

Unbelievable.

What could one possibly enter as a meaningful value for the RESULTS
table BEFORE the query even runs?

The only way to get Access to 'forget' that original column (with
the "AS" clause) is to reboot the machine, closing Access and
bringing it back up will not solve it.

Anybody have ANY idea what the heck is going on here?

I need more info about what you have actually done. Can you give me
simple steps to reproduce the error?

Yep.

Type the above query, (or like version based on your data) without
the "AS" clause.
Get the results table with Expr1001 as the header for the SUM funciton
results.

Edit the query and add the "AS [Net CPU]" clause. Hit the exclamation
point, and Access asks for the value of yourqueryname.Expr001

Thanks for your help

Hmm, it didn't do that for me, using Access 2002. Here's what I did:

1. Create new query in design view.

2. Switch to SQL view.

3. Enter:

SELECT tblData.BaseName, Sum(tblData.intNumber)
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

4. Run it. Column header is the generated name "Expr1001".

5. Switch back to SQL view.

6. Edit SQL, making it like this:

SELECT tblData.BaseName, Sum(tblData.intNumber) AS MySum
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

7. Run it. No parameter prompt appears. Column header is "MySum".

Did you leave out something in your steps to reproduce?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[John Vinson]

sure didn't leave anything out..that is why it is so annoying...there just
isn't anything complex going on here. I've used Access for years and had the
problem occassionaly with '97 on a Dell laptop, now I'm using an IBM R51 with
2000 9.0.3821 SR-1 and it is maddening.

grasping at straws here but... try clearing Name Autocorrect in the
database's Options. It may be one of the many, many bugs with this
feature.

John W. Vinson[MVP]

 

[Albert D. Kallal]

note: hard to believe that would be by
design...if I wanted the sort to stick I would have put it in the SQL.
I > left it OUT of the SQL specifically, yet Access wants to put it in
just
because I sorted the results table...amazing.

The design decision made here was a correct one. You mean to say that while
user is looking at a form, a continues form, or a datasheet, and hits a sort
ascending button, that ms-access is to actually go and find the sql
used..and
modify that? You mean to say that I am looking at some data (coming from a
oracle database),a nd the END user whacks the sort button, and you want
the sql to be modified?

Boy, can you imagine what is going to happen in a multi-user environment? I
working on a form that is based on some query. Some other user opens the
query and whacks the sort ascending button..and now all users who are using
the query get a new modified sql statement? I seen some pretty ridiculous
things proposed in this newsgroup..but you are close to getting a prize for
the best worst idea yet!!

You are kidding here right? You mean that users looking at a datasheet, a
form, or a query and hit a sort button...and the sql gets modified?

The fact of the matter here is that forms, reports and yes even querydefs
has a order by feature..and use of this feature does NOT modify the original
sql, and would be quite incredible to see such system operate with any kind
of sensibility here. What would happen when you decide to un-do the use of
the
sort feature. What does the sql revert back to now? With the current design,
users are free to change the sort order, but then simply un-do there
sort..and it will revert BACK TO THE SQL sort order specified. If you
actually modify the sql, then how can a user remember, or even know what the
original sort order was?

I hate to bust your bubble...but I think the current design choice is a good
one....

 

[NoMagic@Certegy]

Albert you haven't 'busted my bubble' at all.

What, at all actually, gives you the impression I want the SQL changed?? I
specifically stated that I DON'T want it changed. Interesting discourse but
it doesn't address the issue at all.

To help you with some insight...

A query could eventually include several returned columns. Once I get the
results table up, based on my research I may choose to sort the table on a
number of different columns. I DON'T ever want the query changed to reflect
my ad-hoc bouncing around with sort during the 'what if' phase of my
research...yet Access insists on remembering it...as I stated, if I want the
sort order changed in the query, I'll do it, I don't want Access doing it
behind the scenes on 'my behalf'.

I will try the querydef property as suggested by 69 Camaro.

Tchau.

 

[Rick Brandt]

Albert you haven't 'busted my bubble' at all.

What, at all actually, gives you the impression I want the SQL
changed?? I specifically stated that I DON'T want it changed.
Interesting discourse but it doesn't address the issue at all.

To help you with some insight...

A query could eventually include several returned columns. Once I
get the results table up, based on my research I may choose to sort
the table on a number of different columns. I DON'T ever want the
query changed to reflect my ad-hoc bouncing around with sort during
the 'what if' phase of my research...yet Access insists on
remembering it...as I stated, if I want the sort order changed in the
query, I'll do it, I don't want Access doing it behind the scenes on
'my behalf'.

I will try the querydef property as suggested by 69 Camaro.

Tchau.

I don't believe it's a bug, but just a bad idea. Here's another twist. I
have a generic query I use for ad-hoc pass-through statements. I discovered
that I can enter some SQL and look at the resulting datasheet and hide a
column. I can then continue to use that container for all sorts of ad-hoc
queries against other tables (even other databases) and months later if I
query that same table and field, it will still come up as hidden. Boy can
THAT drive you crazy when yuou don't know what's going on!

 

[NoMagic@Certegy]

RBrandt, AMEN Brother!

I've been stung by that 'feature' repeatedly, just didn't bring it up
because it seemed much more obscure than the one I posted.

 

[Albert D. Kallal]

Albert you haven't 'busted my bubble' at all.

No...not a problem at all...I may have in fact miss understood your
complaint...

What, at all actually, gives you the impression I want the SQL changed??

Well, assuming that means that a user changes the sort..then yes...it does
hint that you want sort orders when changed to be put in the sql....I mean,
where else would the above sort order go then? (answer: either the sql gets
changed..OR we have what we got now..and objects like forms, reports,
querydefs have the ability to change the sort order without changing the
actual order in the sql).

I
specifically stated that I DON'T want it changed. Interesting discourse
but
it doesn't address the issue at all.

Sure..but I think now it does clear up why the design is as such.....

A query could eventually include several returned columns. Once I get the
results table up, based on my research I may choose to sort the table on a
number of different columns. I DON'T ever want the query changed to
reflect
my ad-hoc bouncing around with sort during the 'what if' phase of my
research...yet Access insists on remembering it

Hum, I have to admit..I can't say I have the above problem. (and, my
experience does NOT invalidate your experience in any way..and I don't want
to de-value your point). However, I do tend to build the sql query, and then
save it. I don't mess with the sort options/buttons in view mode as that is
for end users. If you save the query before playing with the sort stuff then
you should be ok. And, if you do play with sort stuff, then FOR SURE as you
state, you need to change the sort order ALWAYS in the query grid. Save the
ability of all objects "remembering" "temp" sorting for your END users...but
don't use this your self for development.

I have done a TON of ms-access stuff, and have NEVER been bitten by this
problem. I repeat ..never! I can only think that my great track record (or
horseshoe up my something luck) is due to making a distinction between end
users and developers, or simply being careful NOT to save things like
filters etc with objects like forms etc by accident.

I think with more understanding of the querydef object model...the whole
process will make more sense...

 

[Dirk Goldgar]

sure didn't leave anything out..that is why it is so annoying...there
just isn't anything complex going on here. I've used Access for
years and had the problem occassionaly with '97 on a Dell laptop, now
I'm using an IBM R51 with 2000 9.0.3821 SR-1 and it is maddening.

I'll figure it out eventually...hard to believe we actually pay money
for this stuff.

Thanks for your help.

:

in message Why does Access remember column headers from a query result, then
ask me to provide a value for that column before it will run the
query?

Here is an example that I can reproduce at will....

This query: SELECT smf30.Job, Sum(smf30.CPU) produces a table
with 2 columns, one named Job and the other column header is
Access generated, Expr1001.

That's can't be the actual query; it's not a valid query, since
there's no GROUP BY clause. Please post the SQL of the actual
query you're starting with. Are you building, modifying, and
testing these queries in Query Design View?

==That was the operative part of the query for the question, here is
the whole query, not sure why it matters:
SELECT job, sum(cpu) AS [Net CPU]
FROM smf30
WHERE type in (2,3)
GROUP BY job
HAVING sum(cpu) > 500
ORDER BY job DESC;


change the query to add an 'AS' clause: SELECT smf30.Job,
Sum(smf30.CPU) AS [Net CPU] and run the query. Access throws up a
text box and says Enter Parameter Value queryx.Ecpr1001

How are you running this query? Is it the recordsource of a form,
the rowsource of a combo box, just opened as a datasheet from the
Queries tab of the database window, or what?

Run from the SQL editing window with the exclamation point (!)

Unbelievable.

What could one possibly enter as a meaningful value for the
RESULTS table BEFORE the query even runs?

The only way to get Access to 'forget' that original column (with
the "AS" clause) is to reboot the machine, closing Access and
bringing it back up will not solve it.

Anybody have ANY idea what the heck is going on here?

I need more info about what you have actually done. Can you give
me simple steps to reproduce the error?

Yep.

Type the above query, (or like version based on your data) without
the "AS" clause.
Get the results table with Expr1001 as the header for the SUM
funciton results.

Edit the query and add the "AS [Net CPU]" clause. Hit the
exclamation point, and Access asks for the value of
yourqueryname.Expr001

Thanks for your help

Hmm, it didn't do that for me, using Access 2002. Here's what I did:

1. Create new query in design view.

2. Switch to SQL view.

3. Enter:

SELECT tblData.BaseName, Sum(tblData.intNumber)
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

4. Run it. Column header is the generated name "Expr1001".

5. Switch back to SQL view.

6. Edit SQL, making it like this:

SELECT tblData.BaseName, Sum(tblData.intNumber) AS MySum
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

7. Run it. No parameter prompt appears. Column header is "MySum".

Did you leave out something in your steps to reproduce?

I'm not sure from reading the further discussions whether Gunny
correctly identified your problem or not. That scenario certainly
sounds plausible, but would not then be *exactly* the same as the steps
to reproduce that you posted or that I posted. Would you mind following
up and telling whether the problem was resolved?

 

[Van T. Dinh]

Dirk

That was my guess also in a thread started earlier by the O.P.

See Google:

http://tinyurl/8k2v3

Cheers
Van

 

[NoMagic@Certegy]

Hey '69 Camaro...

that was it, big as day, thanks.

Hi.

My first example, and the basis for the post, doesn't include any sort
interaction. It is simply changing the underlying query that chokes
Access.

A QueryDef's Property such as Order By or Filter has been changed to include
Expr1001, even if inadvertently. Next time this happens to you, change to
Design View and check the QueryDef Properties. I'll bet you'll be amazed at
what you discover.

note: hard to believe that would be by
design...if I wanted the sort to stick I would have put it in the SQL. I
left it OUT of the SQL specifically, yet Access wants to put it in just
because I sorted the results table...amazing.

Just another handy-dandy, helpful feature! One size fits all -- whether we
want it to or not. :-{

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

My first example, and the basis for the post, doesn't include any sort
interaction. It is simply changing the underlying query that chokes
Access.

I understand the point you are making and thought it might be a place to
start to look for the answer. note: hard to believe that would be by
design...if I wanted the sort to stick I would have put it in the SQL. I
left it OUT of the SQL specifically, yet Access wants to put it in just
because I sorted the results table...amazing.

Hi.

Did you leave out something in your steps to reproduce?

<vbg> I know what he did!

After running the query, he sorted on the column "Expr1001," then went
back
to SQL View, changed the SQL to use an alias, then ran the query again.
Since the column name no longer exists, Jet asked for it because the
QueryDef's OrderBy Property was set on this column.

To fix it, open the query in Design View, right-click on the query
diagram
and select "Properties" from the pop-up menu. Delete Expr1001 from the
Order By Property, then close the Properties dialog window and save the
query.

This behavior is by design, because users can set the sort order by SQL
statement, by the "Sort" field in the query grid, or merely selecting the
column, then pressing the "Sort Ascending" (or Descending) button on the
toolbar.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail
harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's
Online
Community, please sign in to the Community and mark these posts as
"Answers," so that all may benefit by filtering on "Answered questions"
and
quickly finding the right answers to similar questions. Remember that
the
first and best answers are often given to those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


message "Dirk Goldgar" wrote:

message Why does Access remember column headers from a query result, then
ask me to provide a value for that column before it will run the
query?

Here is an example that I can reproduce at will....

This query: SELECT smf30.Job, Sum(smf30.CPU) produces a table with 2
columns, one named Job and the other column header is Access
generated, Expr1001.

That's can't be the actual query; it's not a valid query, since
there's no GROUP BY clause. Please post the SQL of the actual query
you're starting with. Are you building, modifying, and testing
these queries in Query Design View?

==That was the operative part of the query for the question, here is
the whole query, not sure why it matters:
SELECT job, sum(cpu) AS [Net CPU]
FROM smf30
WHERE type in (2,3)
GROUP BY job
HAVING sum(cpu) > 500
ORDER BY job DESC;


change the query to add an 'AS' clause: SELECT smf30.Job,
Sum(smf30.CPU) AS [Net CPU] and run the query. Access throws up a
text box and says Enter Parameter Value queryx.Ecpr1001

How are you running this query? Is it the recordsource of a form,
the rowsource of a combo box, just opened as a datasheet from the
Queries tab of the database window, or what?

Run from the SQL editing window with the exclamation point (!)

Unbelievable.

What could one possibly enter as a meaningful value for the RESULTS
table BEFORE the query even runs?

The only way to get Access to 'forget' that original column (with
the "AS" clause) is to reboot the machine, closing Access and
bringing it back up will not solve it.

Anybody have ANY idea what the heck is going on here?

I need more info about what you have actually done. Can you give me
simple steps to reproduce the error?

Yep.

Type the above query, (or like version based on your data) without
the "AS" clause.
Get the results table with Expr1001 as the header for the SUM funciton
results.

Edit the query and add the "AS [Net CPU]" clause. Hit the exclamation
point, and Access asks for the value of yourqueryname.Expr001

Thanks for your help

Hmm, it didn't do that for me, using Access 2002. Here's what I did:

1. Create new query in design view.

2. Switch to SQL view.

3. Enter:

SELECT tblData.BaseName, Sum(tblData.intNumber)
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

4. Run it. Column header is the generated name "Expr1001".

5. Switch back to SQL view.

6. Edit SQL, making it like this:

SELECT tblData.BaseName, Sum(tblData.intNumber) AS MySum
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

7. Run it. No parameter prompt appears. Column header is "MySum".

Did you leave out something in your steps to reproduce?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[NoMagic@Certegy]

I meant to ask if anyone knows how to turn off this 'feature'?

 

['69 Camaro]

You're welcome. Glad that mystery is solved. Hopefully, you don't have any
dents in your skull from banging your head against the monitor trying figure
out why Access decides to act up just in time for a demo in front of your
boss and an important customer.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

Hey '69 Camaro...

that was it, big as day, thanks.

Hi.

My first example, and the basis for the post, doesn't include any sort
interaction. It is simply changing the underlying query that chokes
Access.

A QueryDef's Property such as Order By or Filter has been changed to
include
Expr1001, even if inadvertently. Next time this happens to you, change
to
Design View and check the QueryDef Properties. I'll bet you'll be amazed
at
what you discover.

note: hard to believe that would be by
design...if I wanted the sort to stick I would have put it in the SQL.
I
left it OUT of the SQL specifically, yet Access wants to put it in just
because I sorted the results table...amazing.

Just another handy-dandy, helpful feature! One size fits all -- whether
we
want it to or not. :-{

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail
harvesters
for spammers are [email protected] and [email protected]

My first example, and the basis for the post, doesn't include any sort
interaction. It is simply changing the underlying query that chokes
Access.

I understand the point you are making and thought it might be a place
to
start to look for the answer. note: hard to believe that would be by
design...if I wanted the sort to stick I would have put it in the SQL.
I
left it OUT of the SQL specifically, yet Access wants to put it in just
because I sorted the results table...amazing.

:

Hi.

Did you leave out something in your steps to reproduce?

<vbg> I know what he did!

After running the query, he sorted on the column "Expr1001," then went
back
to SQL View, changed the SQL to use an alias, then ran the query
again.
Since the column name no longer exists, Jet asked for it because the
QueryDef's OrderBy Property was set on this column.

To fix it, open the query in Design View, right-click on the query
diagram
and select "Properties" from the pop-up menu. Delete Expr1001 from
the
Order By Property, then close the Properties dialog window and save
the
query.

This behavior is by design, because users can set the sort order by
SQL
statement, by the "Sort" field in the query grid, or merely selecting
the
column, then pressing the "Sort Ascending" (or Descending) button on
the
toolbar.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail
harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's
Online
Community, please sign in to the Community and mark these posts as
"Answers," so that all may benefit by filtering on "Answered
questions"
and
quickly finding the right answers to similar questions. Remember that
the
first and best answers are often given to those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


in
message "Dirk Goldgar" wrote:

in
message Why does Access remember column headers from a query result, then
ask me to provide a value for that column before it will run the
query?

Here is an example that I can reproduce at will....

This query: SELECT smf30.Job, Sum(smf30.CPU) produces a table
with 2
columns, one named Job and the other column header is Access
generated, Expr1001.

That's can't be the actual query; it's not a valid query, since
there's no GROUP BY clause. Please post the SQL of the actual
query
you're starting with. Are you building, modifying, and testing
these queries in Query Design View?

==That was the operative part of the query for the question, here
is
the whole query, not sure why it matters:
SELECT job, sum(cpu) AS [Net CPU]
FROM smf30
WHERE type in (2,3)
GROUP BY job
HAVING sum(cpu) > 500
ORDER BY job DESC;


change the query to add an 'AS' clause: SELECT smf30.Job,
Sum(smf30.CPU) AS [Net CPU] and run the query. Access throws up
a
text box and says Enter Parameter Value queryx.Ecpr1001

How are you running this query? Is it the recordsource of a form,
the rowsource of a combo box, just opened as a datasheet from the
Queries tab of the database window, or what?

Run from the SQL editing window with the exclamation point (!)

Unbelievable.

What could one possibly enter as a meaningful value for the
RESULTS
table BEFORE the query even runs?

The only way to get Access to 'forget' that original column (with
the "AS" clause) is to reboot the machine, closing Access and
bringing it back up will not solve it.

Anybody have ANY idea what the heck is going on here?

I need more info about what you have actually done. Can you give
me
simple steps to reproduce the error?

Yep.

Type the above query, (or like version based on your data) without
the "AS" clause.
Get the results table with Expr1001 as the header for the SUM
funciton
results.

Edit the query and add the "AS [Net CPU]" clause. Hit the
exclamation
point, and Access asks for the value of yourqueryname.Expr001

Thanks for your help

Hmm, it didn't do that for me, using Access 2002. Here's what I
did:

1. Create new query in design view.

2. Switch to SQL view.

3. Enter:

SELECT tblData.BaseName, Sum(tblData.intNumber)
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

4. Run it. Column header is the generated name "Expr1001".

5. Switch back to SQL view.

6. Edit SQL, making it like this:

SELECT tblData.BaseName, Sum(tblData.intNumber) AS MySum
FROM tblData
GROUP BY tblData.BaseName
HAVING Sum(tblData.intNumber)>1;

7. Run it. No parameter prompt appears. Column header is "MySum".

Did you leave out something in your steps to reproduce?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)