Solved Export button on a report (1 Viewer)

ChrisC

Registered User.
Local time
Today, 16:07
Joined
Aug 13, 2019
Messages
84
Hi guys,

Part of another DB I am making gives the user a report. Although they can obviously export this to Excel using the functions in the External Data tab, I want to make it as easy as a one click operation (less chance of confusion then!).

I have added a button to the report but when it exports to Excel, it is doing something very odd with some of the numbers...

Here is a screenshot of the original report. Note the "Stock Difference" figures.
originalreport.JPG


However, when this is exported in to Excel we get this:
excelexport.JPG


The first 3 rows of the 6 have exported perfectly (as has ALL other info on the report); 84, 22 and 85 respectively. However when we get to line 4 it appears to give up and just puts "85" for everything. These last 3 should be showing -48, -22 and 0.

Here is the code for the button:


Private Sub btnExportReport_Click()

DoCmd.RunSavedImportExport ("Export-rptAllStockDifferences")

End Sub



Could the two negative numbers have something to do with it? If so, any advice on how to remedy this would be appreciated.

Thanks as always,
Chris
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:07
Joined
Sep 21, 2011
Messages
7,176
I think you have other problems as well?
That report is not correct.? First line diff should be 84?, line 5 should be -62?
 

ChrisC

Registered User.
Local time
Today, 16:07
Joined
Aug 13, 2019
Messages
84
HA! VERY good spot... I shall see what has gone wrong!
 

arnelgp

error reading drive A:
Local time
Tomorrow, 00:07
Joined
May 7, 2009
Messages
10,877
where does "Stock Difference" comes from.
you should have it in a Query as Calculated Column.
revisit your ImportExport spec or yet re-create one.
 

ChrisC

Registered User.
Local time
Today, 16:07
Joined
Aug 13, 2019
Messages
84
Re ran the report and its all good now - I took the screen shot from the wrong report (previous was a scrap report that I was using to practise on!)
Thanks for the spot though :)

originalreport2.JPG
 

ChrisC

Registered User.
Local time
Today, 16:07
Joined
Aug 13, 2019
Messages
84
Hi ArnelGP, Stock Difference is a calculated field on the report itself
calculation.JPG


I will look at creating a new query to make this work :)
 

arnelgp

error reading drive A:
Local time
Tomorrow, 00:07
Joined
May 7, 2009
Messages
10,877
you should make a Query and use the Query as Recordsource of
your form.
You also use this Query to create new Import/Export spec.
 

ChrisC

Registered User.
Local time
Today, 16:07
Joined
Aug 13, 2019
Messages
84
Sorted! :) thanks very much; I made the query and it works perfectly now.

thank you!

Out of interest - why would the calculated field I originally used work on SOME lines, but not the others?
 

Users who are viewing this thread

Top Bottom