Column headings display as "Expr" then number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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]
 
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];
 
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]
 
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...
 
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word Create a Phone Book using MailMerge in word 2007 0
Specified Expression/Aggreagate Function 5
dynamic columns number in crosstab report 4
Query returns parameter value required........ 1
cannot use the crosstab of a non-fixed column as a subquery 3
RELATIONSHIPS 4
Combine data in two records from same field 16
Crosstab Qry-Add Rows & Columns 2

Back
Top