Stopping report

Where you put it depends on what you want to do with it.

Typically, you would use the Open event procedure of the report to parse the
string into your variables. From there you can perform math on the dates, or
concatenate the strings, or assign values to text boxes (typically in
Report_Header) to show them if you want.

It's your call how you use it. I was simply showing you how to pass multiple
values of different types via the OpenArgs string, and then parse them.

--
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
...
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)


"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.

Where you put it depends on what you want to do with it.

Typically, you would use the Open event procedure of the report to parse the
string into your variables. From there you can perform math on the dates, or
concatenate the strings, or assign values to text boxes (typically in
Report_Header) to show them if you want.

It's your call how you use it. I was simply showing you how to pass multiple
values of different types via the OpenArgs string, and then parse them.

--
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
...
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)


"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.

Great answer... that helped tremendously! One last question (hopefully):

I have a report with a subreport in it that are based on the same query.
From my unbound form, I click a button to OpenReport, passing the Where
statement with the user's filter criteria. How do I insure the same criteria
are passed to the subreport?

"Allen Browne" wrote:

Where you put it depends on what you want to do with it.

Typically, you would use the Open event procedure of the report to parse the
string into your variables. From there you can perform math on the dates, or
concatenate the strings, or assign values to text boxes (typically in
Report_Header) to show them if you want.

It's your call how you use it. I was simply showing you how to pass multiple
values of different types via the OpenArgs string, and then parse them.

--
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
...
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)


"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.

The simplest way to do that is to base the subreport on a query that reads
the values directly from the form, i.e. the Criteria row of the query
contains things like:
[Forms].[Form1].[Text1]

If you want other alternatives, the question has been asked lots of times.
Go to groups.google.com, choose the Advanced Search, and search the group
microsoft.public.access.reports

--
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
...
Great answer... that helped tremendously! One last question (hopefully):

I have a report with a subreport in it that are based on the same query.
From my unbound form, I click a button to OpenReport, passing the Where
statement with the user's filter criteria. How do I insure the same
criteria
are passed to the subreport?