Query Editing Getting Goofey?

[(PeteCresswell)]

I've been developing this app for about 2 weeks now, religiously
decompiling/compacting it a couple times per day while working on it.

Tonite, when I swapped two columns in a query, saved the query, and then
re-opened it in Design mode, the columns had reverted to their previous
positions.

Tried copying the SQL and pasting it into a new query, same thing.

Also tried changing the query from a Totals query to a plain Select query and,
right in front of me - while I was looking at the screen - the Totals-specific
row did not go away.

Some kind of corruption would be my first guess.

Deadlines loom.....

This one *really* has me scared....

Anybody?

 

[Allen Browne]

This really sounds like you forgot to turn off Name AutoCorrect, Pete:
http://allenbrowne.com/bug-03.html

This feature is guaranteed to get Access thoroughly confused about the field
names, captions, and aliases, past and present. If you *used* to have a
Caption on a field in a table at some time in the past, and you now use that
same name as an alias for a field in your query, ...

 

[(PeteCresswell)]

Per Allen Browne:

This really sounds like you forgot to turn off Name AutoCorrect, Pete:
http://allenbrowne.com/bug-03.html

That was good for a brief rush of hope.... but I just checked and it's off.

Tools|Options|General|Track Name Autocorrect Info=False

'Perform Name Autocorrect' and 'Log name autocorrect changes' are both greyed
out. i.e. .Enabled=False.

Don't remember turning it off.... but then again, I can't remember what I had
for breakfast and I have to check the toothbrush every so often to see if it's
wet....-)

 

['69 Camaro]

Hi, Pete.

I've been developing this app for about 2 weeks now, religiously
decompiling/compacting it a couple times per day while working on it.

I know decompiling is the recommended solution by a number of experts, but
Michka recommended not using it unless the code was already broken/bloated, and
even then after all other avenues were exhausted, because /decompile was
designed for something totally different than what you're using it for.

http://trigeminal.com/usenet/usenet004.asp

Tonite, when I swapped two columns in a query, saved the query, and then
re-opened it in Design mode, the columns had reverted to their previous
positions.

Ensure Track Name AutoCorrect is turned off. It makes things go wacky like
this.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Allen Browne]

Post the SQL statement.

Indicate what section(s) Access is transposing.
Anything obvious about those tables/fields, e.g. attached from another data
source? calculated fields from a lower level query?

Perhaps someone will be able to see what's happening.

There are some weird things, e.g. Access messes up a Totals query if you
remove everything from the GROUP BY but leave the HAVING clause in place.

 

[JK]

Goofey,

Are you for real?, I think not!

Say no more.

J

 

[(PeteCresswell)]

Per Allen Browne:

Post the SQL statement.

Indicate what section(s) Access is transposing.
Anything obvious about those tables/fields, e.g. attached from another data
source? calculated fields from a lower level query?

Perhaps someone will be able to see what's happening.

There are some weird things, e.g. Access messes up a Totals query if you
remove everything from the GROUP BY but leave the HAVING clause in place.

It's three queries that I know of.
Here's one, along with the query it refs.
--------------------------------------------------------
PARAMETERS theBeginDate DateTime, theEndDate DateTime;
SELECT qryDealsActive.DealName
FROM ((qryDealsActive INNER JOIN tblTranche ON qryDealsActive.DealID =
tblTranche.DealID) LEFT JOIN tlkpUnderwriter ON qryDealsActive.UnderwriterID =
tlkpUnderwriter.UnderwriterID) INNER JOIN tblTrade ON tblTranche.TrancheID =
tblTrade.TrancheID
WHERE (((qryDealsActive.ClosingDate) Between [theBeginDate] And [theEndDate])
AND ((tblTrade.TradeTypeID)=1))
GROUP BY qryDealsActive.DealName, qryDealsActive.UnderwriterID;
--------------------------------------------------------
SELECT tblDeal.*
FROM tblDeal
WHERE (((tblDeal.DeletedAt) Is Null));
--------------------------------------------------------

I tried creating a new query and pasting the SQL, but no luck.

Then I created a brand new empty DB and imported all the front end's
objects into it. Still no-go.

Finally, I just renamed the three offending queries and built each one up from
scratch and they seemed OK.

Having the problem persist after both pasting the SQL and importing into a new
DB - and then go away after re-creating the query from scratch tells me there's
plenty I don't have a clue about....

 

[(PeteCresswell)]

Per JK:

Goofey,

Are you for real?, I think not!

Say no more.

Local dialect for demented. Usually prefixed
with the gerund form of a four-letter vulgarity for
sexual intercourse - in the manner of "Damn Yankee"
------------------------------------------------
"Did you hear Mickey Mouse is divorcing Minnie?"

"No, why?"

"She was f*&^#'in Goofey"

 

[David W. Fenton]

Having the problem persist after both pasting the SQL and
importing into a new DB - and then go away after re-creating the
query from scratch tells me there's plenty I don't have a clue
about....

Importing is never a solution to corruption by itself for any type
of object, because Access often imports the problem along with the
good stuff.

I'm actually surprised, though, that a compact didn't fix it, as
that's supposed to remove all query compilation. Seems it was not a
compiling problem, then, but something Access did in reformatting
your SQL and the only way to get rid of that was to start over from
the beginning. Weird.

 

[David W. Fenton]

I know decompiling is the recommended solution by a number of
experts, but Michka recommended not using it unless the code was
already broken/bloated, and even then after all other avenues were
exhausted, because /decompile was designed for something totally
different than what you're using it for.

http://trigeminal.com/usenet/usenet004.asp

I know about MichKa's recommendations, but I think he's overly
cautious. I know what decompile does and it prevents corruption from
developing in code-bearing objects. I have never had any problems
with it at all -- not lost a single object to a decompile (even when
it reveals and fixes corruption).

 

[JK]

Goffey,
I am not interested in exchanging insults. Your pretend Queen English does
no more than bear the question I asked before, are you for real?

Before you dam all Yankees how about you establish where I came form first?
(I was born on the border between Korea and Portugal

I shall leave it at that
J

 

[Allen Browne]

Thanks, Pete. Sounds like you have a workaround now, so no further
assistance is needed.

Not sure I understand what the issue is anyway. The query outputs only a
single field, so I guess you were seeing the fields change places in the
query design grid. That's quite normal, i.e. Access will display the fields
checked for "show" first, and then those that are only need for the other
clauses (WHERE or GROUP BY), but that makes no practical difference to the
query's effeciency or execution.

On another note, a compact might help a problem query (by removing its
execution plan), but a decompile can't help a query since it affects VBA
code only.

All the best.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Per Allen Browne:

Post the SQL statement.

Indicate what section(s) Access is transposing.
Anything obvious about those tables/fields, e.g. attached from another
data
source? calculated fields from a lower level query?

Perhaps someone will be able to see what's happening.

There are some weird things, e.g. Access messes up a Totals query if you
remove everything from the GROUP BY but leave the HAVING clause in place.

It's three queries that I know of.
Here's one, along with the query it refs.
--------------------------------------------------------
PARAMETERS theBeginDate DateTime, theEndDate DateTime;
SELECT qryDealsActive.DealName
FROM ((qryDealsActive INNER JOIN tblTranche ON qryDealsActive.DealID =
tblTranche.DealID) LEFT JOIN tlkpUnderwriter ON
qryDealsActive.UnderwriterID =
tlkpUnderwriter.UnderwriterID) INNER JOIN tblTrade ON tblTranche.TrancheID
=
tblTrade.TrancheID
WHERE (((qryDealsActive.ClosingDate) Between [theBeginDate] And
[theEndDate])
AND ((tblTrade.TradeTypeID)=1))
GROUP BY qryDealsActive.DealName, qryDealsActive.UnderwriterID;
--------------------------------------------------------
SELECT tblDeal.*
FROM tblDeal
WHERE (((tblDeal.DeletedAt) Is Null));
--------------------------------------------------------

I tried creating a new query and pasting the SQL, but no luck.

Then I created a brand new empty DB and imported all the front end's
objects into it. Still no-go.

Finally, I just renamed the three offending queries and built each one up
from
scratch and they seemed OK.

Having the problem persist after both pasting the SQL and importing into a
new
DB - and then go away after re-creating the query from scratch tells me
there's
plenty I don't have a clue about....

 

[(PeteCresswell)]

Per Allen Browne:

That's quite normal, i.e. Access will display the fields
checked for "show" first, and then those that are only need for the other
clauses (WHERE or GROUP BY), but that makes no practical difference to the
query's effeciency or execution.

The issue was that when I swapped the two GROUP BY columns (DealName and
UnderwriterID) and coerced the UnderwriterID col header to "EntityID:
UnderwriterID", and then saved the query and re-opened in Design mode, the
swapped columns had reverted to their original positions and the coerced
"EntityID:" had disappeared.

 

[(PeteCresswell)]

Per JK:

I shall leave it at that

You need to develop more fluency in the language before taking the content too
personally.

What you have works for factual exchange, but you should not rely on it for
anything more.

There was no insult intended - or present. You just misunderstood it.

 

['69 Camaro]

Hi, David.

I know about MichKa's recommendations, but I think he's overly
cautious.

I've found out the hard way several times that I should have put my money on
MichKa's recommendations. Call me paranoid, but I plan to follow his
recommendations in the future.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Allen Browne]

Okay.

The alias is meaningful as a column name only if that column is output (i.e.
if it is part of the SELECT clause.)

The order of fields in a GROUP BY clause makes no difference, e.g. you get
the same result if you group by the City and State fields, or if you group
by the State and City fields.

Whenever you alter the query in query design, Access re-writes the SQL
statement, so you can expect it to change in ways that don't affect the
resutls.

 

[David W. Fenton]

Hi, David.


I've found out the hard way several times that I should have put
my money on MichKa's recommendations. Call me paranoid, but I
plan to follow his recommendations in the future.

But I've been compiling daily for over half a decade and haven't
lost a single line of code. That's good enough for me.

 

[(PeteCresswell)]

Per David W. Fenton:

But I've been compiling daily for over half a decade and haven't
lost a single line of code. That's good enough for me.

Ditto that - but since early 1994...

 

['69 Camaro]

Hi, David.

But I've been compiling daily for over half a decade and haven't
lost a single line of code. That's good enough for me.

I was compiling daily and getting corrupted databases infrequently, but since I
stopped doing things Michka advises against, I haven't lost a single line of
code. That's good enough for me, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[David W. Fenton]

Per David W. Fenton:

Ditto that - but since early 1994...

Er, I don't think so, as VBA was not introduced until Access 95, and
the decompile switch with it.