exporting to Excel

[johnririe]

I am using Access 2003 at work and on a weekly basis we import several excel files to overwrite and update tables we use for a particular query. Weekly we run the query and export it back out to excel. The problem that there is some value in the blank cells and I have to manually (in excel) delete the blank cells to remove the values. Is there something that can be done in the query to prevent the appearance of these "blank" values?

[Robeen]

When you re-export the data, are you creating a new spreadsheet?

What values are you finding in your 'blank' cells?

I do a lot of this kind of thing in daily reports that are all automated and have not run into this problem. BUT . . . all my exports to Excel delete the previous .xls or .xlsx files and then write new ones.

[johnririe]

After the query is run, I copy the datasheet view of it and paste it a blank spreadsheet. The problem is when I enter a subtotal function above the headings to see how many values occur in a given field, it indicates that every cell has a value, even if it doesn't. I have to apply a filter and delete the contents of all of the blank cells in order for the subtotal function to accurately reflect the occurrences in that field.

[Robeen]

Have you tried 'Exporting' the query to see if that gives you different results?

I have never tried copying the contents from the Datasheet View of a query to a spreadsheet so I'm not sure about that.

You shouldn't have to do it that way when you can automate this task with the built-in Export function that Access has.

All my exports are done through Macros - using the 'ExportWithFormatting' action.

Try using Export and see if you still have the problem.

[johnririe]

I tried the export function and it did the same thing. If you can think of anything else let me know. Thanks for your help, I will start using export in the future.

[Robeen]

Are these 'values' showing up in all fields in your query - or just certain fields?

What is the data type of the fields in which this is happening?

Can you post the query here?

[johnririe]

It seems to be happening in all fields that contain blanks. The specific ones that are giving me trouble are text. The query is as follows:

SELECT DISTINCT [Support TO-Progress].[Rec ID], [Priority By System].[High Level Priority (By System)], [Priority By System].[System Wise Priority], [Support TO-Progress].WBS, [ISO List].STM, [Support System].[System #], [Construction Erection Sequence].[Construction Erection Sequence], [Construction Erection Sequence].[WBS Breakdown Description], [Support TO-Progress].[Iso Clean], [ISO List].[ISO REV], [ISO List].[TRANS #], [Support TO-Progress].[Support number], [Support TO-Progress].Material, [Support TO-Progress].[T/O] AS [Weight(MT)], [Support TO-Progress].TO_HRS_1, [Support TO-Progress].EARNED_HR_1, [Support TO-Progress].[To GO], [Support TO-Progress].[% Complete], [P6 Pipe Spools - Fixed Table].Start, [P6 Pipe Spools - Fixed Table].Finish, IIf([M1]="Y","Y","") AS [Install Support (90%)], [Support TO-Progress].M2 AS [Complete (10%)], [Selected Previously (Support)].[Selected (For ERECT)], [Selected Previously (Support)].[Week # (For ERECT)], [ISO HOLD LIST].[Rev Required], [ISO HOLD LIST].Hold, Deleted.Deleted, [OK To Build - Pipe Erection].[PICK TICKET], [OK To Build - Pipe Erection].[PT # 1], [Released - Pipe Supports].[Area Released (Y/N)], [OK To Build - Pipe Erection].[OK TO BUILD], [OK To Build - Pipe Erection].[WORKPLAN # 1], IIf(Deleted.Deleted="D","X",IIf([Status X]="X","X",IIf([HOLD]="Y","E",IIf([Rev Required]="Y","E",IIf([Status E]="E","E",IIf([Status D]="D","D",IIf([Status H]="H","H",IIf([Status A]="A","A",IIf([Status B]="B","B","G"))))))))) AS [Status Code], IIf([Status Code]="A","Pipe Support Installation In Progress",IIf([Status Code]="B","Pipe Support Installation Can Be Started",IIf([Status Code]="D","Material Not Available - Area Released",IIf([Status Code]="E","Engineering Hold/ Awaiting Iso Revision",IIf([Status Code]="G","Cannot Be Started",IIf([Status Code]="H","Pipe Support Installation Completed",IIf([Status Code]="X","Iso Deleted/ Drawing Cancelled",""))))))) AS [Status Description], [Support TO-Progress].Subcon
FROM [P6 Pipe Spools - Fixed Table] RIGHT JOIN (([Released - Pipe Supports] RIGHT JOIN (([Support System] RIGHT JOIN ([Selected Previously (Support)] RIGHT JOIN ([ISO HOLD LIST] RIGHT JOIN ([ISO List] RIGHT JOIN ([Priority - Fixed Table] RIGHT JOIN ([OK To Build - Pipe Erection] RIGHT JOIN (Deleted RIGHT JOIN ([Construction Erection Sequence] RIGHT JOIN [Support TO-Progress] ON [Construction Erection Sequence].[ISO #] = [Support TO-Progress].[Iso Clean]) ON Deleted.ISO = [Support TO-Progress].[Iso Clean]) ON [OK To Build - Pipe Erection].[ISO #] = [Support TO-Progress].[Iso Clean]) ON [Priority - Fixed Table].WBS = [Support TO-Progress].WBS) ON [ISO List].[ISO #] = [Support TO-Progress].[Iso Clean]) ON [ISO HOLD LIST].[ISO #] = [Support TO-Progress].[Iso Clean]) ON [Selected Previously (Support)].[Record ID] = [Support TO-Progress].[Rec ID]) ON [Support System].[Record ID] = [Support TO-Progress].[Rec ID]) LEFT JOIN [Priority By System] ON [Support System].[System #] = [Priority By System].[System #]) ON [Released - Pipe Supports].WBS = [Support TO-Progress].WBS) LEFT JOIN [STATUS - Pipe Support] ON [Support TO-Progress].[Rec ID] = [STATUS - Pipe Support].[Rec ID]) ON [P6 Pipe Spools - Fixed Table].System = [Support System].[System #]
ORDER BY [Support TO-Progress].[Iso Clean], [Support TO-Progress].[Support number];