Unexplained loss of data while using a query.

[cssamerican]

I have included the file with some data. The password that pops up when the file opens is "word". There is no password needed to view the Visual Basic code.

Now here is my problem:
When I pull the reports it is dropping data, but it doesn't seem to have any kind of patern to explain why.
On Dept01 it pulls all the paper charges for each record; however it does not pull any of the copy totals for those records.
On Dept03 and Dept06 it pulls all the paper charges for each record; however, it does not pull any of the network totals for those records.
On Dept07 it pulls all the paper charges and the xerox charge; however, it does not pull the copy charge.
On Dept12 it pulls all of the paper charges for all the records that have data. It pulls all the Local and Network charges for 3 of the records that have data, but it doesn't pull the copy data for these records. Yet there is one record that it pulls the paper charge but not the Network or copy charges.

I originally had more data and at times it will pick up copy charges so it does pick them up every now and then. I resorted the data on the User_Data sheet thinking that if I resorted the data I would get different results when I pulled the report, but this doesn't happen. It drops the same data on each record no matter where the record is located in the listing.

This is super wierd and to be honest I have no idea where to even begin to find a solution for this. I would appreciate any help I can get for this.

[acw]

Hi

Don't have a reason yet, but if you fill in all the blank cells in User_Data columns F:AC with a 0, it seems to be producing the proper output. I basically only tested on the summary report, but it seemed to be bringing in the totals.

Tony

[cssamerican]

Thanks for the help Tony. I got it working. What I did was filled in all the empty cells in the range with a value of 0.
[vba]Sub Data_Fill()
Dim wbBook As Workbook
Dim wsudTarget As Worksheet
Set wbBook = ActiveWorkbook
Set wsudTarget = wbBook.Worksheets("User_Data")
Application.ScreenUpdating = False
wsudTarget.Cells.SpecialCells(xlCellTypeBlanks).Value = 0
Application.ScreenUpdating = True
End Sub[/vba]This keeps it from dropping data. I am not sure why though, I am assuming it has to do with the SUM function in the SQL query but I am not sure. Anyway it works and that is all that really matters. After I key in and import my data I just have to run this subroutine once to fill in the empty cells then save the file. It was a pretty simple fix, but this is something to keep in mind if anyone is running a query against an Excel worksheet.

[geekgirlau]

It's probably evaluating those empty cells as Null. In Access you would use the NZ function to cope with this (for example, SUM(nz([MyField],0)) which tells the query to treat all blank values as 0) but I'm pretty sure you can't use NZ in Excel.