Column headings display as "Expr" then number

R

Richard

Somehow my query table stopped showing that actual headings (like Name,
Address, Phone). Now many of the column headings in the format Expr####.

How can I get my true headings back.
 
J

John W. Vinson

Somehow my query table stopped showing that actual headings (like Name,
Address, Phone). Now many of the column headings in the format Expr####.

How can I get my true headings back.
Click to expand...

By correcting the error in your query... which we cannot see.

Please open the query in SQL view and post the SQL text here.


John W. Vinson [MVP]
 
K

KARL DEWEY

These are names assigned IF you perform a function or you duplicate an output
field.
 
R

Richard

John,
Thanks for taking the time to look at my SQL code.
It seems to be working, in terms of giving the right results.
I've been experimenting with various logical statements within the Switch
command.

SELECT [ERDC FY06 income + vendor + Deb].Fund_Acct_No, [ERDC FY06 income +
vendor + Deb].[Fund Type Code], [ERDC FY06 income + vendor + Deb].[Approp
Dept Code], [ERDC FY06 income + vendor + Deb].[Approp Symbol], [ERDC FY06
income + vendor + Deb].[Amsco Code], [ERDC FY06 income + vendor +
Deb].Vendor_Id, [ERDC FY06 income + vendor + Deb].Vendor_Name, [ERDC FY06
income + vendor + Deb].[Vendor City], [ERDC FY06 income + vendor +
Deb].[Debtor Class Desc], Switch(
[Fund Type Code]="D" And [Approp Dept Code] In ("21","96","97"),[Approp
Symbol],
[Debtor Class Desc]="PUBLIC","PUBLIC",
[Fund Type Code]<>"D" and [Approp Dept Code]="57","AIR FORCE",
[Fund Type Code]<>"D" and [Approp Dept Code]="96" AND [Debtor Class Desc] In
("INTRA-DISTRICT","OTHER CORPS"), "CORPS",
[Vendor_ID]="ARARLCS","Yes",[Approp Dept Code]="21","21",[Fund Type
Code]="D" And [Approp Dept Code] In ("21","96","97"),[Approp
Symbol],True,"Unknown") AS RMCustomer, [ERDC FY06 income + vendor +
Deb].[Approp Symbol], [ERDC FY06 income + vendor + Deb].[Amsco Code], [ERDC
FY06 income + vendor + Deb].Vendor_Id, [ERDC FY06 income + vendor +
Deb].Vendor_Name, [ERDC FY06 income + vendor + Deb].[Vendor City], [ERDC FY06
income + vendor + Deb].[Debtor Class Desc]
FROM [ERDC FY06 income + vendor + Deb];
 
J

John W. Vinson

John,
Thanks for taking the time to look at my SQL code.
It seems to be working, in terms of giving the right results.
I've been experimenting with various logical statements within the Switch
command.

SELECT [ERDC FY06 income + vendor + Deb].Fund_Acct_No, [ERDC FY06 income +
vendor + Deb].[Fund Type Code], [ERDC FY06 income + vendor + Deb].[Approp
Dept Code], [ERDC FY06 income + vendor + Deb].[Approp Symbol], [ERDC FY06
income + vendor + Deb].[Amsco Code], [ERDC FY06 income + vendor +
Deb].Vendor_Id, [ERDC FY06 income + vendor + Deb].Vendor_Name, [ERDC FY06
income + vendor + Deb].[Vendor City], [ERDC FY06 income + vendor +
Deb].[Debtor Class Desc], Switch(
[Fund Type Code]="D" And [Approp Dept Code] In ("21","96","97"),[Approp
Symbol],
[Debtor Class Desc]="PUBLIC","PUBLIC",
[Fund Type Code]<>"D" and [Approp Dept Code]="57","AIR FORCE",
[Fund Type Code]<>"D" and [Approp Dept Code]="96" AND [Debtor Class Desc] In
("INTRA-DISTRICT","OTHER CORPS"), "CORPS",
[Vendor_ID]="ARARLCS","Yes",[Approp Dept Code]="21","21",[Fund Type
Code]="D" And [Approp Dept Code] In ("21","96","97"),[Approp
Symbol],True,"Unknown") AS RMCustomer, [ERDC FY06 income + vendor +
Deb].[Approp Symbol], [ERDC FY06 income + vendor + Deb].[Amsco Code], [ERDC
FY06 income + vendor + Deb].Vendor_Id, [ERDC FY06 income + vendor +
Deb].Vendor_Name, [ERDC FY06 income + vendor + Deb].[Vendor City], [ERDC FY06
income + vendor + Deb].[Debtor Class Desc]
FROM [ERDC FY06 income + vendor + Deb];
Click to expand...

oops... sent a previous message prematurely I think!

The problem is that you're displaying fields Approp Symbol, Amsco Code,
Vendor_ID and so on *twice* - once before the nasty complicated Switch() call
and then again afterward. Access will alias the first instance (why it works
backwards I have no idea) since you can't use the same fieldname twice in a
query.

Including punctuation like + in table and fieldnames is also a possible source
of problems. You might try aliasing the tablename to make the query simpler
and more readable:

SELECT [E].Fund_Acct_No, [E].[Fund Type Code], [E].[Approp Dept Code],
[E].[Approp Symbol], [E].[Amsco Code], [E].Vendor_Id, [E].Vendor_Name,
[E].[Vendor City], [E].[Debtor Class Desc],
Switch(
[Fund Type Code]="D" And [Approp Dept Code] In ("21","96","97"),[Approp
Symbol],
[Debtor Class Desc]="PUBLIC","PUBLIC",
[Fund Type Code]<>"D" and [Approp Dept Code]="57","AIR FORCE",
[Fund Type Code]<>"D" and [Approp Dept Code]="96" AND [Debtor Class Desc] In
("INTRA-DISTRICT","OTHER CORPS"), "CORPS",
[Vendor_ID]="ARARLCS","Yes",
[Approp Dept Code]="21","21",
[Fund Type Code]="D" And [Approp Dept Code] In ("21","96","97"),[Approp
Symbol],
True,"Unknown") AS RMCustomer
FROM [ERDC FY06 income + vendor + Deb] AS E;


John W. Vinson [MVP]
 
R

Richard

John,
I like idea of using aliases, but am having trouble with using them in
conditioan statments.
For example,

select table1.name as lastname, table.address2 as street from table1
where street = "elm";

gives me a message box asking for a street rather than just filtering on "elm"

--
Richard


John W. Vinson said:
John,
Thanks for taking the time to look at my SQL code.
It seems to be working, in terms of giving the right results.
I've been experimenting with various logical statements within the Switch
command.

SELECT [ERDC FY06 income + vendor + Deb].Fund_Acct_No, [ERDC FY06 income +
vendor + Deb].[Fund Type Code], [ERDC FY06 income + vendor + Deb].[Approp
Dept Code], [ERDC FY06 income + vendor + Deb].[Approp Symbol], [ERDC FY06
income + vendor + Deb].[Amsco Code], [ERDC FY06 income + vendor +
Deb].Vendor_Id, [ERDC FY06 income + vendor + Deb].Vendor_Name, [ERDC FY06
income + vendor + Deb].[Vendor City], [ERDC FY06 income + vendor +
Deb].[Debtor Class Desc], Switch(
[Fund Type Code]="D" And [Approp Dept Code] In ("21","96","97"),[Approp
Symbol],
[Debtor Class Desc]="PUBLIC","PUBLIC",
[Fund Type Code]<>"D" and [Approp Dept Code]="57","AIR FORCE",
[Fund Type Code]<>"D" and [Approp Dept Code]="96" AND [Debtor Class Desc] In
("INTRA-DISTRICT","OTHER CORPS"), "CORPS",
[Vendor_ID]="ARARLCS","Yes",[Approp Dept Code]="21","21",[Fund Type
Code]="D" And [Approp Dept Code] In ("21","96","97"),[Approp
Symbol],True,"Unknown") AS RMCustomer, [ERDC FY06 income + vendor +
Deb].[Approp Symbol], [ERDC FY06 income + vendor + Deb].[Amsco Code], [ERDC
FY06 income + vendor + Deb].Vendor_Id, [ERDC FY06 income + vendor +
Deb].Vendor_Name, [ERDC FY06 income + vendor + Deb].[Vendor City], [ERDC FY06
income + vendor + Deb].[Debtor Class Desc]
FROM [ERDC FY06 income + vendor + Deb];
Click to expand...

oops... sent a previous message prematurely I think!

The problem is that you're displaying fields Approp Symbol, Amsco Code,
Vendor_ID and so on *twice* - once before the nasty complicated Switch() call
and then again afterward. Access will alias the first instance (why it works
backwards I have no idea) since you can't use the same fieldname twice in a
query.

Including punctuation like + in table and fieldnames is also a possible source
of problems. You might try aliasing the tablename to make the query simpler
and more readable:

SELECT [E].Fund_Acct_No, [E].[Fund Type Code], [E].[Approp Dept Code],
[E].[Approp Symbol], [E].[Amsco Code], [E].Vendor_Id, [E].Vendor_Name,
[E].[Vendor City], [E].[Debtor Class Desc],
Switch(
[Fund Type Code]="D" And [Approp Dept Code] In ("21","96","97"),[Approp
Symbol],
[Debtor Class Desc]="PUBLIC","PUBLIC",
[Fund Type Code]<>"D" and [Approp Dept Code]="57","AIR FORCE",
[Fund Type Code]<>"D" and [Approp Dept Code]="96" AND [Debtor Class Desc] In
("INTRA-DISTRICT","OTHER CORPS"), "CORPS",
[Vendor_ID]="ARARLCS","Yes",
[Approp Dept Code]="21","21",
[Fund Type Code]="D" And [Approp Dept Code] In ("21","96","97"),[Approp
Symbol],
True,"Unknown") AS RMCustomer
FROM [ERDC FY06 income + vendor + Deb] AS E;


John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

John,
I like idea of using aliases, but am having trouble with using them in
conditioan statments.
For example,

select table1.name as lastname, table.address2 as street from table1
where street = "elm";

gives me a message box asking for a street rather than just filtering on "elm"
Click to expand...

Hrm. Shouldn't! Please post the SQL.

It's vital to have the aliases *exactly* the same wherever you use them, and
it's worthwhile to routinely enclose all fieldnames, tablenames and aliases in
[square brackets].

John W. Vinson [MVP]
 
Top