[AccessD] Crosstab Query Null to 0 & Excel

In Access.
Open the query in design view.
Right click the area where the tables are visible.
The query properties should be visible.
"Column Headings" is the second item on the list.

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
Hollis,Virginia
Sent: Thursday, April 07, 2005 10:48 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Crosstab Query Null to 0 & Excel


The properties of the query in Access or Excel? Excel creates a linked
query to the database.
 
>Place the title of the columns, in quotes, separated by a comma, in the
Column Headings. 
        Where would this go, I don't see a place listing the column
headings in Access or Excel queries.
*************
Virginia,
Welcome back!
 
Go to the properties of the query.
Place the title of the columns, in quotes, separated by a comma, in the
Column Headings.
When the query runs, each column will be visible - even those that are
null.
When you export to Excel all the columns will be in place.
 
HTH
Jim
 
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
<http://databaseadvisors.com/mailman/listinfo/accessd> 
[mailto:accessd-bounces at databaseadvisors.com
<http://databaseadvisors.com/mailman/listinfo/accessd> ]On Behalf Of
Hollis,Virginia
Sent: Thursday, April 07, 2005 9:43 AM
To: accessD at databaseadvisors.com
<http://databaseadvisors.com/mailman/listinfo/accessd> 
Subject: [AccessD] Crosstab Query Null to 0 & Excel
 
 
I have a crosstab query listing New, Complete, & Past Due, On Time work
orders. In Excel I am using the MS Query to import the data from the
A2003 database & create a graph.
 
 
 
The problem: If there aren't any past due work orders when I refresh the
data the Excel query crashes. It also deletes the excel column I have
set up for past due totals. If there are past due work orders, I have to
manually edit the query to add the past due column back in. Run the
refresh query, it adds the past due column but it rearranges the Past
Due column to the right of the other columns.  When I modify the query I
put the past due column back in the order I want it in the list.
 
 
 
Ex:
 
The way I want it layed out & if there are any past due: Responsible,
New, Completed, Past Due, On Time
 
The way it puts the data after there are past due WOs (if there weren't
any the previous run & I add the column to the query): Responsible, New
Completed, On Time, Past Due
 
 
 
Can I put something in my crosstab query to allow for null values? Is
there a way to put a 0 in the query results if the value shows null?
That way it would always show my past due column in Excel because it
would have a 0 in the field. The data is from a database I can not
change, so I can't add a 0 to the tables. I just need the query or Excel
to display a 0 in the Null fields so the Excel query runs correctly.
 
 
 
Or is there a setting in Excel that will always keep the rows where I
want them &/or set the value to 0 if null when refreshing the MSQuery?
 
 
 
Virginia
 
--
AccessD mailing list
AccessD at databaseadvisors.com
<http://databaseadvisors.com/mailman/listinfo/accessd> 
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com