Problem with OutputTo data

[Yves via AccessMonster.com]

Hi,

I am having problem when I transfer data from a Report to Excel. Here is the
code that I am using:

strWhere = "[Date] Between " & _
Format(Me.txtStart, "\#yyyy\/m\/d\#") & " And " & _
Format(Me.txtend, "\#yyyy\/m\/d\#")

stDocName = "S1_Temps_Mois_P1_P2"

DoCmd.OpenReport stDocName, acViewPreview, , strWhere
DoCmd.OutputTo acReport, stDocName, acFormatXLS, , True

The fields in the Report that are directly related to a Table does get
transfered to Excel but the fields that I am calculating with VB code does
not show up in Excel.

Any suggestion??

 

[strive4peace]

Hi Yves,

Instead of using [field] Between #this# and #that#

try

[field] >= #this# AND [field] <= #that#

also, you must SAVE the filter with the report before you Output
To...here is a generic procedure you can put into a general module to do
this:

'~~~~~~~~~~~~~~~
Sub SetReportFilter( _
ByVal pReportName As String, _
ByVal pFilter As String)

' written by Crystal
' Strive4peace2007 at yahoo dot com

' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string

' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments", _
"City='Denver' AND dt_appt=#9/18/05#"

On Error Goto Proc_Err

'---------- declare variables
Dim rpt As Report

'---------- open design view of report
' --- and set the report object variable

'use the hidden parameter to open if you don't want to see it
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)

'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)

'---------- save and close the changed report
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName

'---------- Release object variable
Set rpt = Nothing

Proc_Exit:
Exit Sub

Proc_Err:
msgbox err.description,, _
"ERROR " & err.number & " SetReportFilter"

'press F8 to step thru code and fix problem
'comment next line after debugged
Stop: Resume
'next line will be the one with the error

resume Proc_Exit:

End Sub
'~~~~~~~~~~~~~~~


OpenReport is not necessary unless you also want to see it on the screen


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Yves via AccessMonster.com]

Hi Crystal!

Thanks in helping me!

I did tried to save the reports before I my OutputTo but I still missing the
data of the fields that I calculated with VB code inside the report.

Any ideas?

Hi Yves,

Instead of using [field] Between #this# and #that#

try

[field] >= #this# AND [field] <= #that#

also, you must SAVE the filter with the report before you Output
To...here is a generic procedure you can put into a general module to do
this:

'~~~~~~~~~~~~~~~
Sub SetReportFilter( _
ByVal pReportName As String, _
ByVal pFilter As String)

' written by Crystal
' Strive4peace2007 at yahoo dot com

' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string

' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments", _
"City='Denver' AND dt_appt=#9/18/05#"

On Error Goto Proc_Err

'---------- declare variables
Dim rpt As Report

'---------- open design view of report
' --- and set the report object variable

'use the hidden parameter to open if you don't want to see it
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)

'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)

'---------- save and close the changed report
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName

'---------- Release object variable
Set rpt = Nothing

Proc_Exit:
Exit Sub

Proc_Err:
msgbox err.description,, _
"ERROR " & err.number & " SetReportFilter"

'press F8 to step thru code and fix problem
'comment next line after debugged
Stop: Resume
'next line will be the one with the error

resume Proc_Exit:

End Sub
'~~~~~~~~~~~~~~~

OpenReport is not necessary unless you also want to see it on the screen

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi,

[quoted text clipped - 15 lines]

Any suggestion??

 

[strive4peace]

Hi Yves ,

what section of your report is that calculated data in? and how are you
calculating it? where is the code? what is the code?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal!

Thanks in helping me!

I did tried to save the reports before I my OutputTo but I still missing the
data of the fields that I calculated with VB code inside the report.

Any ideas?

Hi Yves,

Instead of using [field] Between #this# and #that#

try

[field] >= #this# AND [field] <= #that#

also, you must SAVE the filter with the report before you Output
To...here is a generic procedure you can put into a general module to do
this:

'~~~~~~~~~~~~~~~
Sub SetReportFilter( _
ByVal pReportName As String, _
ByVal pFilter As String)

' written by Crystal
' Strive4peace2007 at yahoo dot com

' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string

' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments", _
"City='Denver' AND dt_appt=#9/18/05#"

On Error Goto Proc_Err

'---------- declare variables
Dim rpt As Report

'---------- open design view of report
' --- and set the report object variable

'use the hidden parameter to open if you don't want to see it
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)

'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)

'---------- save and close the changed report
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName

'---------- Release object variable
Set rpt = Nothing

Proc_Exit:
Exit Sub

Proc_Err:
msgbox err.description,, _
"ERROR " & err.number & " SetReportFilter"

'press F8 to step thru code and fix problem
'comment next line after debugged
Stop: Resume
'next line will be the one with the error

resume Proc_Exit:

End Sub
'~~~~~~~~~~~~~~~

OpenReport is not necessary unless you also want to see it on the screen

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi,

[quoted text clipped - 15 lines]

Any suggestion??

 

[Yves via AccessMonster.com]

Hy Crystal,

I am calculating value in the Detail section of the report. In the Footer
group, I have Fields that contains the value of the variables used in the
Detail section of the report.

Code in the Detail Event of the Report:

Public Temp As Varaint

Temp = [A] * 2.5


In the Footer part of my Report, I have a Field that is =[Temp]. It does
work in the report but not in Excel.

The fields that are not displayed in Excel are the ones in the Footer section
of my report.

Is there a way to display those values in Excel?

Thanks in advance!

Hi Yves ,

what section of your report is that calculated data in? and how are you
calculating it? where is the code? what is the code?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal!

[quoted text clipped - 90 lines]

[quoted text clipped - 15 lines]
Any suggestion??

 

[strive4peace]

Is there some way to put an equation in the control? What does the
equation do?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hy Crystal,

I am calculating value in the Detail section of the report. In the Footer
group, I have Fields that contains the value of the variables used in the
Detail section of the report.

Code in the Detail Event of the Report:

Public Temp As Varaint

Temp = [A] * 2.5


In the Footer part of my Report, I have a Field that is =[Temp]. It does
work in the report but not in Excel.

The fields that are not displayed in Excel are the ones in the Footer section
of my report.

Is there a way to display those values in Excel?

Thanks in advance!

Hi Yves ,

what section of your report is that calculated data in? and how are you
calculating it? where is the code? what is the code?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal!

[quoted text clipped - 90 lines]

[quoted text clipped - 15 lines]
Any suggestion??

 

[Yves via AccessMonster.com]

Crystal,

Thanks for your help!
I found my problem, with OutputTo, the fields in the Footer does not get
transfered. Only the fields in the Header and the Details part get
transfered.
So I put my Fields in the Header and it did work.

Thanks again!

Is there some way to put an equation in the control? What does the
equation do?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hy Crystal,

[quoted text clipped - 38 lines]

[quoted text clipped - 15 lines]
Any suggestion??

 

[strive4peace]

you're welcome, Yves happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Crystal,

Thanks for your help!
I found my problem, with OutputTo, the fields in the Footer does not get
transfered. Only the fields in the Header and the Details part get
transfered.
So I put my Fields in the Header and it did work.

Thanks again!

Is there some way to put an equation in the control? What does the
equation do?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hy Crystal,

[quoted text clipped - 38 lines]

[quoted text clipped - 15 lines]
Any suggestion??