Question Exporting a table to excel, a certain calculated field returns nulls if negative.

[wmphoto]

Hi all,

I'm exporting some data to excel as a table containing external data, linked to the original, which is a query. One of the fields, which is a calculated field comes out in excel as blank unless the value stored in it is zero or positive and I can't work out why. I've tried creating a new query so that the query excel links to isn't the one with the original calculation but it still comes out blank. Any ideas?

The code for the calculated field is

Cash-flow Input: [Regular Input]+[Sundry Input]+[Sales Forecast Input]

 

[DavidAtWork]

Are you exporting this data as a table or a query. If it's a table, does the value show in this table when it's a negative number, just prior to export?
David

 

[wmphoto]

The item being exported is a query, as far as I'm aware it is just exported as a table. Well through the external data feature in excel, and then selecting the query from the list of tables and queries.

Other calculated fields appear normal. It's just this one field, which is frustratingly the one I want to use.

 

[DavidAtWork]

Ok, I'd try making the export query a make table query, run it and then check the value in the created table to see if any negative numbers have come through, if they don't and there should be, then it's the query not the export process
David

 

[wmphoto]

Thanks I'll report back momentarily

 

[wmphoto]

The negative values appear exactly as they should in the resultant table.

 

[DavidAtWork]

Ok, now try exporting the table (in Excel format) you just created and see if the negative numbers survive
David

 

[wmphoto]

Thanks again. I tried it, both with and without formatting as xlsx both times the negative values appeared as they should do.

 

[DavidAtWork]

Ok, so we need to look at your original export method, could you post the code
David

 

[wmphoto]

Well I don't know what the code is for the export, or how I could view it, but my method is as follows:

Excel 2007, xlsx file.

Data > From Access > select accdb file in 'select data source' dialogue box,

Select table dialogue box lists both tables and queries, select the query I want, click OK

Select view as table, and location, a cell reference in a worksheet (or I also tried new worksheet) OK.

Et voila, the table appears, but with this one column containing blanks where the value is negative, other calculated fields appear fine.

 

[wmphoto]

Just having a closer look at which values appear as null in the problem field, I've worked out, it isn't as simple as it just being negative values which aren't displayed. The calculated field which contains values to be input into my cash flow forecast, is the sum of three other fields (calculated in separate queries) containing values calculated in different ways for different types of item. For any particular record, only one of the other three fields contains a value, the other two aren't used and contain zero (as opposed to nulls)

It seems that only those records which are sales forecasts work in excel, the other two, regular and sundry items return nulls. The odd thing is if I export a query containing the three fields and not the total field they all appear alright, so I can add a column to combine them in excel, this works for now but I would like to find out what was up with the combined field.

 

[DavidAtWork]

Tou could always change the export query to a MAKE_TABLE query, run that first then export the new table
David

 

[wmphoto]

Thanks, I think I'm gonna stick with doing the calculation done in excel. Fewer steps between adding a new item to be purchased and seeing how it affects my cash flow.