Incorrect Field Name produced by SQL

  • Thread starter Thread starter John D
  • Start date Start date
J

John D

I have this query:
___________________________________
SELECT

TAa1_AllAccts.OrgID

TAa1_AllAccts.[2006] AS 06_NonEntNet,
TAa1_AllAccts.[2005] AS 05_NonEntNet,
TAa1_AllAccts.[2004] AS 04_NonEntNet,
TAa1_AllAccts.[2003] AS 03_NonEntNet

FROM TAa1_AllAccts
WHERE (((TAa1_AllAccts.AcctNo)="6073"))
_____________________________________

The field (column) names for the 05 through 03 values are as I expect:

05_NonEntNet
04_NonEntNet
03_NonEntNet

But the field name for the 06 value is:

2006

I see nothing in the TAa1_AllAccts table design that could somehow cause
this - the field definitions for these 4 fields are exactly the same except
for the field name = "YYYY".

Any suggestions? Thanks - John D
 
Actually the comma is there - I cut out a lot of extraneous SQL to not take
too much time from you guys plowing through non-impactful code. There is a
comma.

Now - if I were in your place - I'd be saying "Oh, yeah. Right. Sure - that
"extraneous SQL" is where the problem is!" Before I show you the entire query
--- I've "suddenly" started having this problem, which is to say I haven't
noticed it before. I've got a few queries behaving this way (most are
behaving correctly) - not putting the "correct" field name in. I believe
these queries worked correctly before. Some of them simply pass on the
original field name in the file being queried, but much weirder is other
queries put a different field name entirely - such as

original file's field name: 2006
SQL: TAa1_AllAccts.[2006] AS 06_OthrFinUse,
Result: 06_TranOut: (with the colon mark)

I've compacted and repaired the database, and it's still doing it.

Full SQL for query:

___________________
SELECT TAa1_AllAccts!OrgID & "" & TAa1_AllAccts!Fund & "" &
TAa1_AllAccts!DistLdgr & "" & TAa1_AllAccts![Actvty] AS ComboKey,
TAa1_AllAccts.OrgID,
TAa1_AllAccts.Fund,
TAa1_AllAccts.DistLdgr,
TAa1_AllAccts.Actvty,
TAa1_AllAccts.AcctNo,

TAa1_AllAccts.[2006] AS 06_OthrFinUse,
TAa1_AllAccts.[2005] AS 05_OthrFinUse,
TAa1_AllAccts.[2004] AS 04_OthrFinUse,
TAa1_AllAccts.[2003] AS 03_OthrFinUse

FROM TAa1_AllAccts
WHERE (((TAa1_AllAccts.AcctNo)="7060"))
ORDER BY TAa1_AllAccts.DistLdgr;
____________________________________

I just created a "new" query - with EXACTLY the same SQL - I didn't copy it
but typed it in anew - and it works?!?

Gremlins - the only possibility remaining!

John D
 
John said:
Actually the comma is there - I cut out a lot of extraneous SQL to
not take too much time from you guys plowing through non-impactful
code. There is a comma.

Now - if I were in your place - I'd be saying "Oh, yeah. Right. Sure
- that "extraneous SQL" is where the problem is!"
Click to expand...

Remove all the fields from your query except that one. Is the alias still
ignored?

I just created a "new" query - with EXACTLY the same SQL - I didn't
copy it but typed it in anew - and it works?!?

Gremlins - the only possibility remaining!
Click to expand...
I hate when that happens ...
 
Remove all the fields from your query except that one. Is the alias still
ignored?
Click to expand...

I pared down the query to:

SELECT

TAa1_AllAccts.[2006] AS 06_OthrFinUse

FROM TAa1_AllAccts
WHERE (((TAa1_AllAccts.AcctNo)="7060"))

And the resulting alias is "06_TranOut:"

"06_TranOut" is an alias in a different saved query.

Do you think there's any clue given by the colon ":" in the result? The
colon usually does not show up in the result.

Could there be some slight corruption in the deep inner sanctum of the
structure of a database? Bummer if so.
 
John said:
Remove all the fields from your query except that one. Is the alias
still ignored?
Click to expand...

I pared down the query to:

SELECT

TAa1_AllAccts.[2006] AS 06_OthrFinUse

FROM TAa1_AllAccts
WHERE (((TAa1_AllAccts.AcctNo)="7060"))

And the resulting alias is "06_TranOut:"

"06_TranOut" is an alias in a different saved query.

Do you think there's any clue given by the colon ":" in the result?
The colon usually does not show up in the result.

Could there be some slight corruption in the deep inner sanctum of the
structure of a database? Bummer if so.
Click to expand...

I'm at a loss - are there any query properties set?
 
I'm at a loss - are there any query properties set?
Click to expand...

Just typical ones:
Default View: Datasheet
Output All Fields: No
Unique Values/Records: No
Permission: Users
Source DB: (current)
Locks: No
Recordset: Dynaset

a few other innocuous settings (Left to right, etc.)

Incidentally - don't know if you noticed that the full query example above
isn't for the "cut down" version I first presented - I'm trying to figure
this problem out for several queries - and I copied one I was working on at
the time. I'm having this problem with maybe 8 out of a total of, oh, 340 or
so saved queries (most of which actually are used at least from time to time).
 
John said:
I have this query:
___________________________________
SELECT

TAa1_AllAccts.OrgID

TAa1_AllAccts.[2006] AS 06_NonEntNet,
TAa1_AllAccts.[2005] AS 05_NonEntNet,
TAa1_AllAccts.[2004] AS 04_NonEntNet,
TAa1_AllAccts.[2003] AS 03_NonEntNet

FROM TAa1_AllAccts
WHERE (((TAa1_AllAccts.AcctNo)="6073"))
_____________________________________

The field (column) names for the 05 through 03 values are as I expect:

05_NonEntNet
04_NonEntNet
03_NonEntNet

But the field name for the 06 value is:

2006

I see nothing in the TAa1_AllAccts table design that could somehow
cause this - the field definitions for these 4 fields are exactly the
same except for the field name = "YYYY".

Any suggestions? Thanks - John D
Click to expand...

Try going to Tools>Options>General and disabling Name AutoCorrect (both
checkboxes need to be unchecked). Do a Compact/Repair on the database and
see if this bug goes away.
 
Bob - A couple of days after my last posting I booted up my computer, went
into the database - and lo and behold - the problem was gone.

I had compacted and repaired a couple of times - didn't do the disabling
Name AutoCorrect you suggested - and don't know at all what made a difference
- but ...

It isn't a comfortable feeling knowing that just underground where you can't
see it, something may be lurking. But for now, the system appears to have
"fixed itself".

??? thanks for you help - John D
 
Back
Top