Date Command Button (More than 6 month's)

[Ron S]

Good Morning,

I am finally creating that last part of app...what I am trying to do
is create a command button that will print a report for any record
that is older than six months. I am sure that this is an easy
expression. I am also thinking about going a little bit further and
adding a combo box with the option of selecting either 3m, 6m, 9m, or
1 year.

Thanks for your help,

Ron

 

[Guest]

Use the Where argument of the OpenReport method. Assuming you create this
combo, it would be something like:

Dim dtmHowOld As Date

dtmHowOld = DateAdd("m", - Me.cboOldDate, Date)
DoCmd.OpenReport "MyReportName", , , "[SomeDateField] < #" & dtmHowOld &
"#"

Note - Untested "air" code

 

[Ron S]

Use the Where argument of the OpenReport method. Assuming you create this
combo, it would be something like:

Dim dtmHowOld As Date

dtmHowOld = DateAdd("m", - Me.cboOldDate, Date)
DoCmd.OpenReport "MyReportName", , , "[SomeDateField] < #" & dtmHowOld &
"#"

Note - Untested "air" code

--
Dave Hargis, Microsoft Access MVP

Good Morning,

I am finally creating that last part of app...what I am trying to do
is create a command button that will print a report for any record
that is older than six months. I am sure that this is an easy
expression. I am also thinking about going a little bit further and
adding a combo box with the option of selecting either 3m, 6m, 9m, or
1 year.

Thanks for your help,

Ron- Hide quoted text -

- Show quoted text -

Dave,

I like your idea. I have played around with this and thus far all I am
able to do is print out the form with no info on it...

Any Ideas?

 

[Guest]

The code is expecting your combo to return a number of months. (3,6,9,12) as
you posted.

You may want to try running a test using:

DoCmd.OpenReport "MyReportName", , , "[SomeDateField] < #7/31/2007#"
That is six months.

SomeDateField, of course, should be replaced with the date you want to
compare against.

I am assuming SomeDateField is a Date data type field.

Also, be sure I haven't dummied up and have the compare on the dates
backwards.
--
Dave Hargis, Microsoft Access MVP

Use the Where argument of the OpenReport method. Assuming you create this
combo, it would be something like:

Dim dtmHowOld As Date

dtmHowOld = DateAdd("m", - Me.cboOldDate, Date)
DoCmd.OpenReport "MyReportName", , , "[SomeDateField] < #" & dtmHowOld &
"#"

Note - Untested "air" code

--
Dave Hargis, Microsoft Access MVP

Good Morning,

I am finally creating that last part of app...what I am trying to do
is create a command button that will print a report for any record
that is older than six months. I am sure that this is an easy
expression. I am also thinking about going a little bit further and
adding a combo box with the option of selecting either 3m, 6m, 9m, or
1 year.

Thanks for your help,

Ron- Hide quoted text -

- Show quoted text -

Dave,

I like your idea. I have played around with this and thus far all I am
able to do is print out the form with no info on it...

Any Ideas?

 

[Ron S]

The code is expecting your combo to return a number of months. (3,6,9,12) as
you posted.

You may want to try running a test using:

DoCmd.OpenReport "MyReportName", , , "[SomeDateField] < #7/31/2007#"
That is six months.

SomeDateField, of course, should be replaced with the date you want to
compare against.

I am assuming SomeDateField is a Date data type field.

Also, be sure I haven't dummied up and have the compare on the dates
backwards.
--
Dave Hargis, Microsoft Access MVP

Use the Where argument of the OpenReport method. Assuming you create this
combo, it would be something like:
Dim dtmHowOld As Date
dtmHowOld = DateAdd("m", - Me.cboOldDate, Date)
DoCmd.OpenReport "MyReportName", , , "[SomeDateField] < #" & dtmHowOld &
"#"
Note - Untested "air" code
--
Dave Hargis, Microsoft Access MVP
:
Good Morning,
I am finally creating that last part of app...what I am trying to do
is create a command button that will print a report for any record
that is older than six months. I am sure that this is an easy
expression. I am also thinking about going a little bit further and
adding a combo box with the option of selecting either 3m, 6m, 9m, or
1 year.
Thanks for your help,
Ron- Hide quoted text -
- Show quoted text -

Dave,

I like your idea. I have played around with this and thus far all I am
able to do is print out the form with no info on it...

Any Ideas?- Hide quoted text -

- Show quoted text -

Dave,

This is interesting, I went ahead and placed in Debug.Print dtmHowOld
to see what the code was doing, it is returning the calculated date,
i.e. 2/1/2006 (when 12 month's is selected)

While I am new to VBA, it am thinking there is something missing...but
I am not really sure

For the purposes of testing I changed part of your code to acPreview,
I have noticed the report is opening but a # Error is showing up in my
address field of the report, am I correct in saying that this is
because there is a null value when the report displayed; I have
already checked the report and everything there is ok there, it
display's everything outside of this code.

 

[Ron S]

After taking a deeper look into this I have the results that I want.
The only problem that I need help on is the is a null value, I am
finding out the error message is created when there are no records to
print.

 

[Guest]

Reports have a Not Data event. You can display a message box there. Here is
an example from one of my reports:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo Report_NoData_Error

MsgBox "No Data Matching Selections", vbInformation + vbOKOnly, "Product
Offering Report"
Cancel = True

Report_NoData_Exit:

On Error Resume Next
Exit Sub

Report_NoData_Error:

MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & _
") in procedure Report_NoData of VBA Document
Report_rptUPOProductOffering"
GoTo Report_NoData_Exit
End Sub

Now, even though at this point you think the problem is resolved, the Form
thinks the report was canceled. Well, it was, but the form doesn't know why,
so it wants to throw an Action Canceled error (2501), so you have to code for
that in the error handler of the procedure in which you open the report:

PrintReport_Error:

If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & _
") in procedure PrintReport of VBA Document Form_frmUPOReports"
End If
GoTo PrintReport_Exit

End Sub