Field0 AS Column: How To Change

[GGleason]

I have a field that is called "DETAIL", which looks fine if I look at is as a table. However, in a query it turns to "Field0". Why does Access do that and how can I fix it?

TIA,
GGleason

 

[Eupher]

Check the table in design view - does the "Detail" field have anything in the Caption property?

Ken S.

 

[GGleason]

I looked at the caption and it is empty.

Thx

 

[sahmiele]

In query design, is "Field0:" typed in front of "DETAIL"? That can be used to rename a field in a query.

 

[dhookom]

I expect Detail is a reserved word and can't be displayed as a column heading in a query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GGleason]

That is what I thought. However, according to Access Help and other SQL sources I have found no evidence that DETAIL is a reserved word.

Thx

 

[jedraw]

GGleason,
Can you post the table design and the query?

 

[GGleason]

jedraw,

Here is the table design:

Name Size Type
---- ---- ----
PROJ 50 10 (dbText)
SPEC 50 10 (dbText)
TYPE 50 10 (dbText)
NAME 50 10 (dbText)
PBOR1 4 6
PBOR2 4 6
PBOR3 4 6
STYP 50 10 (dbText)
SHOP 50 10 (dbText)
ANGL 50 10 (dbText)
RADI 50 10 (dbText)
CATREF 50 10 (dbText)
DETAIL 50 10 (dbText)
MATXT 50 10 (dbText)
CMPREF 50 10 (dbText)
BLTREF 50 10 (dbText)

When I view the table alone, it is ok.

Here is a query:

SELECT q0.*
FROM tblSPCOdata AS q0;

Pretty simple I think, but yet what is displayed is Field0. The interesting thing in design view it shows up properly as DETAIL.

To sahmielel,

The fields are all presenented using:

q0.*

Thx for helping to solve the mystery.

 

[jedraw]

GGleason,

That's a strange one. It definitely doesn't like DETAIL.
I found a list of reserve words A2k at

http://support.microsoft.com/kb/q209187/

but DETAIL isn't among them.

My guess is it's an undocumented reserved word.

 

[dhookom]

I recently posted this question to the very secret private news group for MS Access MVPs. This seems to be new information to them also. No one could find documentation anywhere on this. I'm not sure if our "Lead" (MS employee who babysits us) has reviewed the thread.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GGleason]

Duane,

I appreciate the review. My guess is this is an Access thing and not as SQL thing. I don't have another db program to check that. If it is an Access thing, I would further guess that it is legacy code going back many versions.

Thx
GGleason

 

[dhookom]

I expect you are correct with the legacy code. One of the Access MVPs has created an add-in that identifies reserved words within your mdb. I just asked him if it's ok if I post the link to the utility. I'll post it if he allows.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

Here is a link to Allen Browne's issue checker. Keep in mind this is a free utility and all the bugs haven't been worked out. It is fairly robust and offers great functionality.

http://allenbrowne.com/unlinked/DbIssueChecker.zip

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]