Preparing a Report by a Date Selection

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Good day folks,

It has been a while since I've used Access and have
prepared a new database that's working great. I am having
trouble figuring out how to print a report where the user
specifies a >= date or a date range.

Details:
The database holds data collected that includes the date
received, the amount, type of service, etc. I am trying
to link this up so that a deposit report can be run on a
weekly basis. I have the summary, count and totals coming
up fine but just can not seem to specify a date anywhere
(or I'm using the incorrect syntax).

Thank you for any help you can provide.

Paul
 
I asume you have a print buttonfor the report. If you
check the on event properties of the report icon, you will
see something like this (except for the Lines with dbl
asterisks)
Private Sub Command234_Click()
On Error GoTo Err_Command234_Click
Dim DocName As String
**Dim LinkCriteria As String
**DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, ,
A_MENU_VER20
**LinkCriteria = "[date] >= [Enter start date]"
stDocName = "Report Name"
**DoCmd.OpenReport stDocName, A_PREVIEW, , LinkCriteria
Exit_Command234_Click:
Exit Sub
Err_Command234_Click:
MsgBox Error$
Resume Exit_Command234_Click
End Sub

Enter/modify the lines with the dbl asterisks and make
sure to substitute the names of your report and variables
as necessary.
I hope this helps.
Fons
 
Good day Fons,

Thank you for your speedy reply. I don't think I am
getting it though... this is pretty foreign stuff for me.

I am not using a print button for the report. The user
just selects Reports from the Objects Menu and launches
the report they want (in this case Deposit Report).

When I go into the Visual Basic editor I see:


Private Sub Report_Open(Cancel As Integer)

End Sub

So I took that out and put your stuff in and modified what
I thought I could (see below proceeded by $$) but that
didn't work. So I think I'm on the right path but syntax
is everything! :)

Paul
-----Original Message-----
I asume you have a print buttonfor the report. If you
check the on event properties of the report icon, you will
see something like this (except for the Lines with dbl
asterisks)
Private Sub Command234_Click()
On Error GoTo Err_Command234_Click
Dim DocName As String
Dim LinkCriteria As String
DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, ,
A_MENU_VER20
LinkCriteria = "[$$Date_IN] >= [Enter start date]"
stDocName = "$$Deposit Report"
DoCmd.OpenReport stDocName, A_PREVIEW, , LinkCriteria
Exit_Command234_Click:
Exit Sub
Err_Command234_Click:
MsgBox Error$
Resume Exit_Command234_Click
End Sub

Enter/modify the lines with the dbl asterisks and make
sure to substitute the names of your report and variables
as necessary.
I hope this helps.
Fons
-----Original Message-----
Good day folks,

It has been a while since I've used Access and have
prepared a new database that's working great. I am having
trouble figuring out how to print a report where the user
specifies a >= date or a date range.

Details:
The database holds data collected that includes the date
received, the amount, type of service, etc. I am trying
to link this up so that a deposit report can be run on a
weekly basis. I have the summary, count and totals coming
up fine but just can not seem to specify a date anywhere
(or I'm using the incorrect syntax).

Thank you for any help you can provide.

Paul
.
Click to expand...
.
Click to expand...
 
Is your report based on a query? If it is, you can add a parameter to your
query by right mouse clicking on the query and choosing parameter. Add a
StartDate of Date/Time data type. Make sure you add to your query on the
Date field the criteria >=StartDate.

Now when you open your report, the query will ask you for a date and then
will query your report by that date.

Evy

Good day Fons,

Thank you for your speedy reply. I don't think I am
getting it though... this is pretty foreign stuff for me.

I am not using a print button for the report. The user
just selects Reports from the Objects Menu and launches
the report they want (in this case Deposit Report).

When I go into the Visual Basic editor I see:


Private Sub Report_Open(Cancel As Integer)

End Sub

So I took that out and put your stuff in and modified what
I thought I could (see below proceeded by $$) but that
didn't work. So I think I'm on the right path but syntax
is everything! :)

Paul
-----Original Message-----
I asume you have a print buttonfor the report. If you
check the on event properties of the report icon, you will
see something like this (except for the Lines with dbl
asterisks)
Private Sub Command234_Click()
On Error GoTo Err_Command234_Click
Dim DocName As String
Dim LinkCriteria As String
DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, ,
A_MENU_VER20
LinkCriteria = "[$$Date_IN] >= [Enter start date]"
stDocName = "$$Deposit Report"
DoCmd.OpenReport stDocName, A_PREVIEW, , LinkCriteria
Exit_Command234_Click:
Exit Sub
Err_Command234_Click:
MsgBox Error$
Resume Exit_Command234_Click
End Sub

Enter/modify the lines with the dbl asterisks and make
sure to substitute the names of your report and variables
as necessary.
I hope this helps.
Fons
-----Original Message-----
Good day folks,

It has been a while since I've used Access and have
prepared a new database that's working great. I am having
trouble figuring out how to print a report where the user
specifies a >= date or a date range.

Details:
The database holds data collected that includes the date
received, the amount, type of service, etc. I am trying
to link this up so that a deposit report can be run on a
weekly basis. I have the summary, count and totals coming
up fine but just can not seem to specify a date anywhere
(or I'm using the incorrect syntax).

Thank you for any help you can provide.

Paul
.
Click to expand...
.
Click to expand...
Click to expand...
 
Good day Fons,

Ah that is it! Thank you so much. I can't believe all
the things I tried. Thank you!!

Paul
-----Original Message-----
In that case, the way I see to create the selection as you
indicate, limiting the displayed data to all records
greater than a certain data,is as follows.
I assume your report currently is based on a single table.
You need to have the report based on a query. If needed
change the source to a query and select all applicable
fields. In the criteria for the $$Date_IN enter ">= [Enter
start date]" without the dbl quotes.
Now when you run the report with the print button on the
tool bar or just by dbl clicking it in the reports tab
selection, a message box will popup asking for the >= data
parameter.
Hope this helps.
Fons
-----Original Message-----
Good day Fons,

Thank you for your speedy reply. I don't think I am
getting it though... this is pretty foreign stuff for me.

I am not using a print button for the report. The user
just selects Reports from the Objects Menu and launches
the report they want (in this case Deposit Report).

When I go into the Visual Basic editor I see:


Private Sub Report_Open(Cancel As Integer)

End Sub

So I took that out and put your stuff in and modified what
I thought I could (see below proceeded by $$) but that
didn't work. So I think I'm on the right path but syntax
is everything! :)

Paul
-----Original Message-----
I asume you have a print buttonfor the report. If you
check the on event properties of the report icon, you will
see something like this (except for the Lines with dbl
asterisks)
Private Sub Command234_Click()
On Error GoTo Err_Command234_Click
Dim DocName As String
Dim LinkCriteria As String
DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, ,
A_MENU_VER20
LinkCriteria = "[$$Date_IN] >= [Enter start date]"
stDocName = "$$Deposit Report"
DoCmd.OpenReport stDocName, A_PREVIEW, , LinkCriteria
Exit_Command234_Click:
Exit Sub
Err_Command234_Click:
MsgBox Error$
Resume Exit_Command234_Click
End Sub

Enter/modify the lines with the dbl asterisks and make
sure to substitute the names of your report and variables
as necessary.
I hope this helps.
Fons
-----Original Message-----
Good day folks,

It has been a while since I've used Access and have
prepared a new database that's working great. I am
having
trouble figuring out how to print a report where the user
specifies a >= date or a date range.

Details:
The database holds data collected that includes the date
received, the amount, type of service, etc. I am trying
to link this up so that a deposit report can be run on a
weekly basis. I have the summary, count and totals
coming
up fine but just can not seem to specify a date anywhere
(or I'm using the incorrect syntax).

Thank you for any help you can provide.

Paul
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
That's fine Fons, except that it uses Access Version 2
stuff. Unless you know Paul is using that version, it would
be a better example to use the more modern methods and
constants:

Private Sub Command234_Click()
On Error GoTo Err_Command234_Click
Dim DocName As String
Dim LinkCriteria As String

If Me.Dirty Then Me.Dirty = False
LinkCriteria = "[datefield] >= " _
& Format(txtStartDate, "\#m\/d\/yyyy\#")
stDocName = "Report Name"
DoCmd.OpenReport stDocName, acViewPreview, , LinkCriteria
Exit_Command234_Click:
Exit Sub
Err_Command234_Click:
MsgBox Error$
Resume Exit_Command234_Click
End Sub

Fons said:
I asume you have a print buttonfor the report. If you
check the on event properties of the report icon, you will
see something like this (except for the Lines with dbl
asterisks)
Private Sub Command234_Click()
On Error GoTo Err_Command234_Click
Dim DocName As String
**Dim LinkCriteria As String
**DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, ,
A_MENU_VER20
**LinkCriteria = "[date] >= [Enter start date]"
stDocName = "Report Name"
**DoCmd.OpenReport stDocName, A_PREVIEW, , LinkCriteria
Exit_Command234_Click:
Exit Sub
Err_Command234_Click:
MsgBox Error$
Resume Exit_Command234_Click
End Sub

Enter/modify the lines with the dbl asterisks and make
sure to substitute the names of your report and variables
as necessary.

-----Original Message-----
It has been a while since I've used Access and have
prepared a new database that's working great. I am having
trouble figuring out how to print a report where the user
specifies a >= date or a date range.

Details:
The database holds data collected that includes the date
received, the amount, type of service, etc. I am trying
to link this up so that a deposit report can be run on a
weekly basis. I have the summary, count and totals coming
up fine but just can not seem to specify a date anywhere
(or I'm using the incorrect syntax).
Click to expand...
Click to expand...
 
Back
Top