Stopping report

Thanks for all of the help and support...just a couple more questions...

I've created an unbound form that has unbound controls for everything that a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound controls to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records. The
query must limit the records to info bewteen those dates and then I need to
display the dates the user requested on the report.

"Shaun Beane" wrote:

Yes, that's exactly the way I've done that in the main database that I
support. I don't use a table to store the macro/module names but I can
definitely see the benefit in that. My prompt form is pretty big, but like
you said, I enable/disable the controls based on which report the user
clicks on. I actually pass the criteria using VBA, but you could certainly
go right to the query.

--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"albertsong" wrote in message
...
No, you're not argumentative at all...I'm just trying to understand.

I know that I can open the form from the switchboard and then open the
report from the form, but I have a group of ~10 forms that feed ~42
reports
and ~12 macros (for those users that want the numbers and not a pretty
report).

If I understand you all correctly, I would have a single form that would:
1. Allow the users to select a report or macro
2. Then update the Visible value of the appropriate filter criteria
(based
on the selection in step 1)
3. Run the report or macro

Would you recommend having a table behind the form with report/macro
names,
the criteria that should be visible for each, and the values the user
selects
for the criteria for the queries to pull from? Or should the form be
unbound, coded to make the appropriate criteria selection visible and then
pass criteria selection to the query right from the form?

"Shaun Beane" wrote:

True, but even with the switchboard manager you can open the form instead
of
the report and then trigger the report from the form. Please don't think
I'm trying to be argumentative, just trying to see think outloud about
when
to use the open event. Thank you for the feedback!

On using one form for all reports with multiple users, one user will not
affect the criteria of another if they both happen to be running the
report
at the same time. Even though the database is on the network, the
"instance" of the form is still unique.

--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"albertsong" wrote in message
...
I guess I should have mentioned that this a single databse on a network
where
several users may be using it at the same time. So far, I've resisted
loading the front-end on everyone's PC because of how widely everyone
is
distributed (the back end is a centralized SQL database). To this
point,
I've just dealt with the small number of overlaps on the pop-up forms.
If
I
create a central form, would a user change the query criteria while
another
user is running a report with a differnt set of reports?

I guess a lot of semi-literate Access users like myself open the form
from
the report because we just use the Switchboard Manager for the user
interface
for the report selection.


"Allen Browne" wrote:

How about working the other way around?

Create a form that acts as a front-end to your reports. It will
contain
some
way the user can select the report to open (e.g. option group or
combo).
In
the AfterUpdate event of that control, display the various boxes that
the
user can use to filter that particular report, keeping irrelevant ones
hidden. Below all that is a Preview button. When button is clicked,
build
up
a WhereCondition string from the controls where the user entered
something,
and the OpenReport the one specified in the option group/combo.

This way:
- the user has already entered any filtering before the report is
opened;
- there is only one form to open many reports (simple interface);
- nothing has to be modal;
- you don't have any jammed events in the queue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
I have several reports that open one of several forms (depending on
the
report) to allow users to select a date
range/department/machine/shift
for
their particular report. Each specific form is initiated by the
OnOpen
function of the reports and is set to Modal so that the report will
not
open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the
form
that
pops up, will decide that they've tried to run the wrong report.
Closing
the form or entering blanks for dates still runs the query (time
consuming)
for the report. Is there a way to give the user a "Cancel" button
that
will
close the form and stop the report/query from running? I've tried
End,
but
that closes Access altogether. I can't specify which Report to
cancel
because each form is used by several reports.

You will have a command button on your form that actually opens the report.
In its Click event you need to build two strings: one as the WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden label on
your form, and use the Format event of the Report Header section to read it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field types,
using the different delimiters. Note that the date range example copes with
the fact that the field may have a time component by asking for "less than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """) AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND "
strDescrip = strDescrip & "Amount at least " & Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] = " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
Thanks for all of the help and support...just a couple more questions...

I've created an unbound form that has unbound controls for everything that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records. The
query must limit the records to info bewteen those dates and then I need
to
display the dates the user requested on the report.

OK, now I understand how to use the WhereCondition and how to use OpenArgs to
send strings through. In my example, I can send a string through OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have a
function JustMinutes that calculates the difference in minutes so that's not
the issue. I'm dividing the run time of a machine during the user entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from the
unbound controls in another unbound control on the forms and pass it to the
report

Either way, how do I use OpenArgs to pass this value (to do calculations) in
addition to the string?

"Allen Browne" wrote:

You will have a command button on your form that actually opens the report.
In its Click event you need to build two strings: one as the WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden label on
your form, and use the Format event of the Report Header section to read it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field types,
using the different delimiters. Note that the date range example copes with
the fact that the field may have a time component by asking for "less than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """) AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND "
strDescrip = strDescrip & "Amount at least " & Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] = " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
Thanks for all of the help and support...just a couple more questions...

I've created an unbound form that has unbound controls for everything that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records. The
query must limit the records to info bewteen those dates and then I need
to
display the dates the user requested on the report.

You can pass as many values as you like in the OpenArgs string, delimited by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
news
OK, now I understand how to use the WhereCondition and how to use OpenArgs
to
send strings through. In my example, I can send a string through OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have a
function JustMinutes that calculates the difference in minutes so that's
not
the issue. I'm dividing the run time of a machine during the user entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from the
unbound controls in another unbound control on the forms and pass it to
the
report

Either way, how do I use OpenArgs to pass this value (to do calculations)
in
addition to the string?

"Allen Browne" wrote:

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden label
on
your form, and use the Format event of the Report Header section to read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field types,
using the different delimiters. Note that the date range example copes
with
the fact that the field may have a time component by asking for "less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """)
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND "
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] = " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



"albertsong" wrote in message
...
Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for everything
that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records. The
query must limit the records to info bewteen those dates and then I
need
to
display the dates the user requested on the report.

OK, now you lost me...

I added the results of my JustMinutes function to the OpenArgs so I now have
a unbound text box on my report that says "between 10/01/04 07:00AM and
10/01/04 08:00AM;60" with the ;60 being the difference.

Where do I put the Split()? The help file on the Split function in VBA is
not very good.

"Allen Browne" wrote:

You can pass as many values as you like in the OpenArgs string, delimited by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
news
OK, now I understand how to use the WhereCondition and how to use OpenArgs
to
send strings through. In my example, I can send a string through OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have a
function JustMinutes that calculates the difference in minutes so that's
not
the issue. I'm dividing the run time of a machine during the user entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from the
unbound controls in another unbound control on the forms and pass it to
the
report

Either way, how do I use OpenArgs to pass this value (to do calculations)
in
addition to the string?

"Allen Browne" wrote:

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden label
on
your form, and use the Format event of the Report Header section to read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field types,
using the different delimiters. Note that the date range example copes
with
the fact that the field may have a time component by asking for "less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """)
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND "
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] = " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



"albertsong" wrote in message
...
Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for everything
that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records. The
query must limit the records to info bewteen those dates and then I
need
to
display the dates the user requested on the report.

OK, now you lost me...

I added the results of my JustMinutes function to the OpenArgs so I now have
a unbound text box on my report that says "between 10/01/04 07:00AM and
10/01/04 08:00AM;60" with the ;60 being the difference.

Where do I put the Split()? The help file on the Split function in VBA is
not very good.

"Allen Browne" wrote:

You can pass as many values as you like in the OpenArgs string, delimited by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
news
OK, now I understand how to use the WhereCondition and how to use OpenArgs
to
send strings through. In my example, I can send a string through OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have a
function JustMinutes that calculates the difference in minutes so that's
not
the issue. I'm dividing the run time of a machine during the user entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from the
unbound controls in another unbound control on the forms and pass it to
the
report

Either way, how do I use OpenArgs to pass this value (to do calculations)
in
addition to the string?

"Allen Browne" wrote:

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden label
on
your form, and use the Format event of the Report Header section to read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field types,
using the different delimiters. Note that the date range example copes
with
the fact that the field may have a time component by asking for "less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """)
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND "
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] = " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



"albertsong" wrote in message
...
Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for everything
that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records. The
query must limit the records to info bewteen those dates and then I
need
to
display the dates the user requested on the report.

If you pass in a string such as:
"#1/1/2004#; #12/31/2004#; 365"
you can parse out the parts of the string as:
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(1)
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
OK, now you lost me...

I added the results of my JustMinutes function to the OpenArgs so I now
have
a unbound text box on my report that says "between 10/01/04 07:00AM and
10/01/04 08:00AM;60" with the ;60 being the difference.

Where do I put the Split()? The help file on the Split function in VBA is
not very good.

"Allen Browne" wrote:

You can pass as many values as you like in the OpenArgs string, delimited
by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
news
OK, now I understand how to use the WhereCondition and how to use
OpenArgs
to
send strings through. In my example, I can send a string through
OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have
a
function JustMinutes that calculates the difference in minutes so
that's
not
the issue. I'm dividing the run time of a machine during the user
entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls
and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from
the
unbound controls in another unbound control on the forms and pass it to
the
report

Either way, how do I use OpenArgs to pass this value (to do
calculations)
in
addition to the string?

"Allen Browne" wrote:

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden
label
on
your form, and use the Format event of the Report Header section to
read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field
types,
using the different delimiters. Note that the date range example copes
with
the fact that the field may have a time component by asking for "less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """)
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND
"
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] = " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



"albertsong" wrote in message
...
Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for
everything
that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records.
The
query must limit the records to info bewteen those dates and then I
need
to
display the dates the user requested on the report.

If you pass in a string such as:
"#1/1/2004#; #12/31/2004#; 365"
you can parse out the parts of the string as:
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(1)
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
OK, now you lost me...

I added the results of my JustMinutes function to the OpenArgs so I now
have
a unbound text box on my report that says "between 10/01/04 07:00AM and
10/01/04 08:00AM;60" with the ;60 being the difference.

Where do I put the Split()? The help file on the Split function in VBA is
not very good.

"Allen Browne" wrote:

You can pass as many values as you like in the OpenArgs string, delimited
by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
news
OK, now I understand how to use the WhereCondition and how to use
OpenArgs
to
send strings through. In my example, I can send a string through
OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have
a
function JustMinutes that calculates the difference in minutes so
that's
not
the issue. I'm dividing the run time of a machine during the user
entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls
and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from
the
unbound controls in another unbound control on the forms and pass it to
the
report

Either way, how do I use OpenArgs to pass this value (to do
calculations)
in
addition to the string?

"Allen Browne" wrote:

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden
label
on
your form, and use the Format event of the Report Header section to
read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field
types,
using the different delimiters. Note that the date range example copes
with
the fact that the field may have a time component by asking for "less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """)
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND
"
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] = " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



"albertsong" wrote in message
...
Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for
everything
that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records.
The
query must limit the records to info bewteen those dates and then I
need
to
display the dates the user requested on the report.

Like this?

? Split([Report].[OpenArgs])(1)

Where do I put this? In the Control Source of an unbound text box? In the
Event Procedure of the OpenEvent of the report?

"Allen Browne" wrote:

If you pass in a string such as:
"#1/1/2004#; #12/31/2004#; 365"
you can parse out the parts of the string as:
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(1)
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
OK, now you lost me...

I added the results of my JustMinutes function to the OpenArgs so I now
have
a unbound text box on my report that says "between 10/01/04 07:00AM and
10/01/04 08:00AM;60" with the ;60 being the difference.

Where do I put the Split()? The help file on the Split function in VBA is
not very good.

"Allen Browne" wrote:

You can pass as many values as you like in the OpenArgs string, delimited
by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
news OK, now I understand how to use the WhereCondition and how to use
OpenArgs
to
send strings through. In my example, I can send a string through
OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have
a
function JustMinutes that calculates the difference in minutes so
that's
not
the issue. I'm dividing the run time of a machine during the user
entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls
and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from
the
unbound controls in another unbound control on the forms and pass it to
the
report

Either way, how do I use OpenArgs to pass this value (to do
calculations)
in
addition to the string?

"Allen Browne" wrote:

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden
label
on
your form, and use the Format event of the Report Header section to
read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field
types,
using the different delimiters. Note that the date range example copes
with
the fact that the field may have a time component by asking for "less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """)
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND
"
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] = " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



"albertsong" wrote in message
...
Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for
everything
that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records.
The
query must limit the records to info bewteen those dates and then I
need
to
display the dates the user requested on the report.

Like this?

? Split([Report].[OpenArgs])(1)

Where do I put this? In the Control Source of an unbound text box? In the
Event Procedure of the OpenEvent of the report?

"Allen Browne" wrote:

If you pass in a string such as:
"#1/1/2004#; #12/31/2004#; 365"
you can parse out the parts of the string as:
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(1)
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
OK, now you lost me...

I added the results of my JustMinutes function to the OpenArgs so I now
have
a unbound text box on my report that says "between 10/01/04 07:00AM and
10/01/04 08:00AM;60" with the ;60 being the difference.

Where do I put the Split()? The help file on the Split function in VBA is
not very good.

"Allen Browne" wrote:

You can pass as many values as you like in the OpenArgs string, delimited
by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
news OK, now I understand how to use the WhereCondition and how to use
OpenArgs
to
send strings through. In my example, I can send a string through
OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have
a
function JustMinutes that calculates the difference in minutes so
that's
not
the issue. I'm dividing the run time of a machine during the user
entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls
and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from
the
unbound controls in another unbound control on the forms and pass it to
the
report

Either way, how do I use OpenArgs to pass this value (to do
calculations)
in
addition to the string?

"Allen Browne" wrote:

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden
label
on
your form, and use the Format event of the Report Header section to
read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field
types,
using the different delimiters. Note that the date range example copes
with
the fact that the field may have a time component by asking for "less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """)
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND
"
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] = " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



"albertsong" wrote in message
...
Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for
everything
that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records.
The
query must limit the records to info bewteen those dates and then I
need
to
display the dates the user requested on the report.